/ Sql

How to deploy a SQL Sequence using SSDT without it always resetting

SQL Sequences have been a great addition to SQL Server. However when using them with SQL Server Data Tools (SSDT), I hit a bit of snag.

My sequence was to be used for incrementing invoice numbers. Previously, the invoice number had used the row's IDENTITY column. I know, not ideal, but it served the immediate purpose of creating incrementing invoice numbers with little overhead.

The better way, is to use a SQL Sequence, such as:

CREATE SEQUENCE [finances].[InvoiceNumberSequence] AS INT
START WITH 1
INCREMENT BY 1;

Sometimes the databases I were deploying to were new, clean and fresh. No problems there.

However some databases I was updating, already had invoices in it, so I needed the sequence to start at the next appropriate value (something other than 1 anyway).

It's clear that I need a Post-Deployment script to alter the sequence depending on the target database. My instinct was to use RESTART WITH:

ALTER [finances].[InvoiceNumberSequence] RESTART WITH @nextInvoiceNumber

On first deployment to a database, this all worked great. Job done, or so I thought.

When Deploying to the database again, SSDT reset [finances].[InvoiceNumberSequence] back to start at 1.

So why did SSDT reset the Sequence?

In SSDT, my sequence has to be a CREATE script. The deployment process checks for differences between the sequence object it would generate with the CREATE script and what is already there in the target database.

The use of RESTART WITH actually changes the definition of the Sequence, which SSDT sees as a difference saying, "Hey, the InvoiceNumberSequence is different, I should update it to match the definition I have".

And so the sequence is summarily reset to 1. Not ideal to say the least.

So, what can be done?

There are a few options:

  1. Ignore sequence objects when publishing (not ideal),

Option 3 was the winner for me.

Using sp_sequence_get_range to fix the Sequence

So what sp_sequence_get_range does for us is actually 'consume' the sequence - i.e. set the next value of the Sequence to a value we want, without changing its definition, by 'using' some values from it:

EXEC sp_sequence_get_range @sequence_name = 'InvoiceNumberSequence', @range_size = {{numberOfValuesToConsume}}

So putting this into practice, gives a script like the following: