Modernizing Your T-SQL: The Sequence Object
This is one of several posts on modernizing T-SQL code with new features and functionality available in SQL Server.
Last year, you finally retired the last of your SQL Server 2008R2 instances. Congratulations! But are you taking advantage of everything that your new instances have to offer? Unless you did a review of all of the T-SQL in your applications, I’m guessing not.
Let’s take a look at the SEQUENCE object, introduced with SQL Server 2012.
Can I Get Your Number?
Sometimes, you just need a unique number, generated more or less in isolation. Maybe it’s for a helpdesk system, so that you can give customers a case ID before you’ve cut a full ticket. Or maybe you have to write some data across a number of tables, with an ID that ties them all together. Prior to SQL Server 2012, you might have done one or the other of the following (I’ve seen both in the wild)
Method One
You’re doing work on a table that doesn’t get used ever again. So maybe you decide to just delete the record after you’ve gotten that number. That’s just more I/O work, isn’t it? What if someone has put a primary key and maybe even another index on that table as well? Yes, I’ve seen this. That’s even more logical I/O you’re having to do. Push those deletes off to your overnight maintenance window? You could, but you can’t implement it as a TRUNCATE because that’ll just reseed the table (unless you capture your last used ID and then run DBCC RESEED).
Method Two
Let’s say you need more than one pseudo-sequences. So you create a table with one row for each, and you fetch & update as needed.
Have you tested these under a heavy workload with lots of concurrency? Does these tables become hot spots and start causing performance bottlenecks? Are they safe to use when there may be a transaction rollback?
Make Way for Sequence
The SEQUENCE object eliminates all of this. It gives you a simple way to just grab a new number and increment for the next caller. It’s very fast as there’s no visible table I/O, and it’s unaffected by rollbacks.
And it’s so much easier to use! You just ask for the next number in the sequence!
Comparison
Setup
I’m going to create two stored procedures for an application to call, one using the table method, the other using the SEQUENCE.
Testing
I threw together a quick PowerShell 7 script to exercise both procedures. Using the new Foreach-Object -Parallel {}, I was able to easily simulate multiple processes asking for sequence numbers concurrently.
Results
The results? Fairly significant. With 100 iterations, there’s a roughly 16% improvement using the SEQUENCE object vs. the table method.
Time to get numbers from table: 86.3985773 Time to get numbers from sequence: 72.7598077
Implementation
You may be thinking “but Andy, I have so many places in my application calling the stored procedure that fetches the next number in line. I can’t replace it all with a query against the SEQUENCE.” And I’m probably going to agree with you. So let’s not do that.
Instead, let’s replace the body of the stored procedure our application calls today. This way, new/updated code can hit the SEQUENCE directly (if you want), but legacy code can still take advantage of it.
Caveats
There are two things you need to be aware of, one for all implementations and the other for replacing legacy code.
Remember up above where I said that the SEQUENCE is unaffected by rollbacks? If you fetch a number from this object, it will not go back if you rollback the transaction. What this means is that if you must have an unbroken chain with no gaps, SEQUENCE may not be for you.
If you’re replacing legacy code (for example, the table method above), be sure to specify a STARTS AT value that won’t cause duplication of values already used from the table. I suggest rounding up a bit from the current identity value to give some buffer. But if you have a requirement for an unbroken series, this will take some extra work on implementation day. You’ll need to check the identity before creating the SEQUENCE.
