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.

Got a problem or embarking on a SQL Server project and want some help and advice? I’m available for consulting – please get in touch or check out my services page to find out what I can do for you.

For T-SQL Tuesday #111, Andy Leonard asks “What is your why? Why do you do what you do?”

Like Andy, I didn’t take a straight route into being a DBA. I actually trained as a teacher, and did a variety of temp jobs – largely in call centres – before I got my first “proper” job as a developer.

My first steps into development were when I was a team leader in a call centre. There was a lot of paper shuffling going on. I felt there had to be a better way of doing things, so developed processes using Access and Excel and convinced the Admin team in the benefits of using them.

Not exactly development, more Macros with a bit of VBA, but that got me spotted by another team that were trying to automate reporting in the call centre – again using MS Access. That was where I first encountered relational databases and got deeper into coding. I joined as they were in the process of taking an Excel spreadsheet that processed flat files using macros – taking the whole day/every day to chunder away, replacing it with a coded solution. Finally we had it importing all the call center stats in seconds on a schedule in the early hours.

Nowadays that seems trivial, but back then it felt like a great achievement. It’s still one of my best work memories – when I showed my boss the automated import for the first time.

Moving forward from there and trying to answer Andy’s question, I realised that what started me off is what’s kept moving me forward. That there’s always got to be a better way.

Another of my favourite early developer memories was being asked to look at whether a file import to an application could be sped up as it was taking 10 minutes. The process was to upload a file with corporate credit card statements from a flat file into a SQL database for the application that companies used to manage them. When I opened it up I was quite impressed with the code, it seemed cleverer than anything I could have written – but maybe a bit too clever. I asked myself if there was a better and simpler way of achieving the same thing. There was, and it turned out to be a lot lot quicker.

Making things run faster remained something I loved doing, but it often seemed often that optimisation wasn’t a big focus in application coding –  that was more about delivering features. I think that’s part of what led me to specialise in data where performance, particularly at scale, is so key. Looking at SQL code and going – “well, sure it works as it is, but is there not a better way of doing this.”

It strikes me that for a lot of us, this is how we ended up in software, we saw things being done a certain way and saw scope for improvement. Felt that there must be a better way.

And it’s good to remember this isn’t an attitude where we can rest on our laurels. Even when we know what we’re doing, have done something a hundred times before, it’s still good to say “There’s got to be a better way”, and to think about what that might be. Not only does that mean we’re continually improving, but it’s also what keeps working in technology fresh and interesting.

Got a problem or embarking on a SQL Server project and want some help and advice? I’m available for consulting – please get in touch or check out my services page to find out what I can do for you.