Imagine an Ecommerce system that generates a unique order number each time a customer goes to the checkout. How would you generate this unique number?
You could :
- Use the primary key from the database
- Select the MAX order number from the database and increment by one
- Write a custom piece of code that uses a table with a unique constraint to “reserve” numbers
- Use SQL Sequences to generate unique values
As you’ve probably guessed by the title of this post, I want to touch on that final point because it’s a SQL Server feature that I think has gone a bit under the radar. I’ll be the first to admit that it doesn’t solve all your problems (See limitations at the end of this post), but you should know it exists and what it’s good for. Half the battle when choosing a solution is just knowing what’s out there after all!
SQL Sequences are actually a very simple and effective way to generate unique incrementing values in a threadsafe way. That means as your application scales, you don’t have to worry about two users clicking the “order” button on your ecommerce site at exactly the same time, and being given the exact same order number.
Getting Started With SQL Sequences
Creating a Sequence in SQL Server is actually very simple.
CREATE SEQUENCE TestSequence START WITH 1 INCREMENT BY 1
Given this syntax, it’s probably obvious to you the sort of different options you can do. You can for example, always increment by the sequence by 2 :
CREATE SEQUENCE TestSequence START WITH 1 INCREMENT BY 2
Or you can even descend instead of ascend :
CREATE SEQUENCE TestSequence START WITH 0 INCREMENT BY -1
And to get the next value, we just need to run SQL like :
SELECT NEXT VALUE FOR TestSequence
It really is that simple! Not only that, you can view Sequences in SQL Management Studio as well (Including being able to create them, view the next value without actually requesting it etc). Simply look for the Sequences folder under Programmability.
Entity Framework Support
Of course you are probably using Entity Framework with .NET/SQL Server, so what about first class support there? Well.. It is supported but it’s not great.
To recreate our sequence as above, we would override the OnModelCreating of our DbContext (e.g. Where we would put all of our configuration anyway). And add the following :
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.HasSequence("TestSequence", x => x.StartsAt(1).IncrementsBy(1));
}
That creates our sequence, but how about using it? Unfortunately, there isn’t really a thing to “get” the next value (For example if you needed it in application code). Most of the documentation revolves around using it as a default value for a column such as :
modelBuilder.Entity<Order>()
.Property(o => o.OrderNo)
.HasDefaultValueSql("NEXT VALUE FOR TestSequence");
If you are looking to simply retrieve the next number in the sequence and use it somewhere else in your application, unfortunately you will be writing raw SQL to achieve that. So not ideal.
With all of that being said however, if you use Entity Framework migrations as the primary way to manage your database, then the ability to at least create sequences via the ModelBuilder is still very very valuable.
Limitations
When it comes to generating unique values for applications, my usage of SQL Sequences has actually been maybe about 50/50. 50% of the time it’s the perfect fit, but 50% of the time there are some heavy “limitations” that get in the way of it being actually useful.
Some of these limitations that I’ve run into include :
When you request a number from a sequence, no matter what happens from that point on, the sequence is incremented. Why this is important is imagine you are creating a Customer in the database, and you request a number from the sequence and get “156”. When you go to insert that Customer, a database constraint fails and the customer is not inserted. The next Customer will still be inserted as “157”, regardless of the previous insert failing. In short, sequences are not part of transactions and do not “lock and release” numbers. This is important because in some cases, you may not wish to have a “gap” in the sequence at all.
Another issue is that sequences cannot be “partitioned” in any way. A good example is a system I was building required unique numbers *per year*. And each year, the sequence would be reset. Unfortunately, orders could be backdated and therefore simply waiting until Jan 1st and resetting the sequence was not possible. What would be required is a sequence created for say the next 10 years, and for each of these be managed independently. It’s not too much of a headache, but it’s still another bit of overhead.
In a similar vein, multi tenancy can make sequences useless. If you have a single database in an ecommerce SAAS product supporting say 100 tenants. You cannot use a single sequence for all of them. You would need to create multiple sequences (One for each tenant), which again, can be a headache.
In short, sequences are good when you need a single number incrementing for your one tenant database. Anything more than that and you’re going to have to go with something custom or deal with managing several sequences at once, and selecting the right one at the right time with business logic.
