The SEQUENCE object was added to T-SQL in SQL Server 2012. It’s reasonably well known to DBAs, but less so to developers or those new to SQL, so I thought I’d produce a quick post to demonstrate its use.

In basic terms, a SEQUENCE is a way of generating a sequence of numerical values. The following are examples of sequences you could generate:

1, 2, 3, 4, 5 6, 7, 8, 9…

1, 6 , 11, 16, 21, 26, 31…

1000, 1001, 1002, 1003, 1004…

You can pick a starting number (and an ending number if you want), a data type (which might produce a natural maximum to the size of the sequence) and an increment (i.e. how much you want to be added to produce the next number in the sequence). There are other options, but I’m going to focus on the simplest use case. You can find the full documentation here:

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-2017

So, let’s define my use case. I have a table to hold customer orders. For each record want to define an Order Reference Number of the format ORN0000000001.

Now you could implement something using an IDENTITY column to manage this – but there may be times when that is not ideal, for instance your table may not already have a suitable identity to use (you might have a unique identifier as the primary key) and if you want to store the actual reference then you’d need to add an IDENTITY column in addition to the reference column. Or you might need a reference that is unique across multiple tables.

The SEQUENCE object is also designed to be faster than IDENTITY, creating less blocking when you have a lot of concurrent inserts.

First of all, creating the sequence to generate the numeric part of my reference is easy. Let’s say that a bunch of reference numbers have already been used so I want to start with ORN0000100001

Let’s look at the SQL…

CREATE SEQUENCE dbo.OrderRefSequence 
   AS bigint
   START WITH 100001
   INCREMENT BY 1;

Then I can request numbers from the sequence using NEXT VALUE FOR e.g.

SELECT NEXT VALUE FOR dbo.OrderRefSequence; 

The first time I run that I get the starting number 100,001.

Another nice addition to SQL Server 2012 was the FORMAT function which we can use to format the number into a string whilst padding it with leading zeroes and adding the text prefix:

SELECT FORMAT(NEXT VALUE FOR dbo.OrderRefSequence, 'ORN0000000000#');

That returns me ORN00000100002.

If I keep executing it then the reference increases:

ORN00000100003

ORN00000100004

ORN00000100005

ORN00000100006…

So, now I can just use that when inserting values to my table to get a new reference number each time.

But, what’s even nicer is that you can do it all by defining a default for your column and referencing the sequence in the default.

I’ll create the following table:

CREATE TABLE dbo.Orders (
   Id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
   CustomerId UNIQUEIDENTIFIER NOT NULL,
   OrderReference VARCHAR(20) 
     DEFAULT(FORMAT(NEXT VALUE FOR dbo.OrderRefSequence, 'ORN0000000000#')),
   OrderDate DATETIME DEFAULT(GETUTCDATE()));

You can see that the OrderReference is defined with a default using our sequence object.

I insert a bunch of rows into the table. For the sake of this rather contrived example, I only need to specify the CustomerId. I do that by generating a bunch of random unique identifiers – one for each row in the sys.objects table.

INSERT INTO dbo.Orders (CustomerId)
SELECT NEWID() FROM sys.objects;

Let’s have a look at an extract from the table:

You can see I’ve got a nice series of ascending, non-duplicating reference numbers.

One thing to note is that, while the sequence will generally produce unique number, it is still worth enforcing that in your table definition with a unique constraint i.e.

ALTER TABLE dbo.Orders ADD CONSTRAINT UQ_Orders_OrderReference UNIQUE(OrderReference);

This prevents someone from issuing an UPDATE command that might create a duplicate reference. Also, once the sequence runs out of numbers it will repeat back at the beginning unless you specify NO CYCLE in the defintion of the sequence – obviously in most applications this is unlikely to be an issue if you’re using a bigint for the sequence.

There was also a bug in some versions of SQL 2012 and 2014 that meant a duplicate could get created when your server was under memory pressure:

https://support.microsoft.com/en-gb/help/3011465/fix-sequence-object-generates-duplicate-sequence-values-when-sql-serve

This was fixed with SQL Server 2012 SP2 CU4 and SQL Server 2014 CU6 – but it’s better to be safe than sorry.

As a final note, it’s worth remembering that with the GDPR, these sorts of references are defined as personal data.That’s one good reason not to ever consider using these sorts of references as the primary key of your table (there are many others) – but also a reason why – where you already have an identity based primary keys that you could use to generate the references –  it may be worth decoupling them from the primary key and basing them on a separate sequence instead.