Reliable sequential numbers from SQL server

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).

The solution

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:

test1

We do not have any exceptions, and we successfully incremented our record 1000 times:

testresults

Next, we do the same test, but within a rolled back transaction:

test2

As expected, our value within the table stays the same and no gaps/hiccups were detected.

testresults

Seems we have a solution to replace our identity columns to reliably retrieve a sequential number in SQL!

Cheers,
Pieter

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s