Earlier, I’ve been writing about how gaps are a very normal thing with IDENTITY columns. The same applies for SQL Server SEQUENCES however.
The above might make you wonder how you would reliably get a sequential number from SQL server without any gaps, so this post is about just that.
Creating the sandbox environment
First, we’ll create our sandbox environment. We’ll create a dedicated, separate database for this:
CREATE DATABASE [Sandbox]
Then, create the test table:
CREATE TABLE [dbo].[Seq]( [val] [int] NOT NULL )
Now comes a bit of a ‘catch’. For this to work, you’ll need to feed the table with the initial value.
INSERT INTO [dbo].[Seq] VALUES (0)
This is a bit of a downside, but the benefit is that you can set your initial value to whatever you want. Just make sure you set it to 0 if you want your first value to be 1 (one).
Now here comes the ‘magic’; Whenever you want to increment the value in the table, execute the following statement:
DECLARE @i AS INT; UPDATE Seq SET @i = val = val +1; SELECT @i;
This contains a single update statement, ran within a single transaction, both selecting the value that is incremented as updating the value.
Concurrency testing our solution
A solution is only a solution when:
- We don’t have any gaps while rolling back, failing over or restarting SQL
- It reliably runs while having a lot of concurrent calls
To test this, I used a tool called SQLQueryStress. This tool is able to run the same statement any number of times on any number of threads!
Our first test was just running the statement 1000 times, spread over 10 threads:
We do not have any exceptions, and we successfully incremented our record 1000 times:
Next, we do the same test, but within a rolled back transaction:
As expected, our value within the table stays the same and no gaps/hiccups were detected.
Seems we have a solution to replace our identity columns to reliably retrieve a sequential number in SQL!