SQL Server “Denali” among other introduces Sequence Objects. I am sure that sequence objects are widely known but let’s provide their definition in plain words:
A sequence object allows you to set a global counter of values within the scope of a DBMS instance.
Whenever you call the sequence object it returns a unique value based on its configuration (i.e. the numbers 1,2,3,4, etc.).
Even though you can use this feature in SQL Server “Denali”, what about if you need to use it in an earlier version of SQL Server?
It is a fact that sequence objects are not available in earlier versions of SQL Server but guess what; there are many workarounds that can be used instead! 🙂
Further below I’m describing one such workaround. The main idea is the following:
1. Create a table with two columns:
- An identity column of the type bigint.
- A value column of the type bigint.
2. Create a stored procedure that will be doing the following upon its execution:
- Insert a value in the respective value column (this will be causing the identity column to be automatically increased).
- Delete the previously inserted record and always keep the last one.
3. Whenever you need a unique value, you simply call the stored procedure and then you retrieve the identity column’s value from the respective table.
4. That’s it! You are now using a sequence-like object!
Here’s the T-SQL for the above logic:
–Select the proper database
–Step 1: Create the table for storing the data (once-off)
CREATE TABLE [tblSeq](
[SeqID] [int] IDENTITY(1,1) NOT NULL,
[SeqVal] [bigint] NULL
–Step 2: Create the stored procedure for manipulating the data (once-off)
CREATE PROCEDURE [GetNewSeqVal_tblSeq]
insert into tblSeq (SeqVal) values (1)
if ((select max(seqid) from tblSeq))!=1
delete from tblSeq where seqid < (select max(seqid) from tblSeq)
— Whenever a new, unique number is required, you just run the following two T-SQL statements:
— Step 1: Execute the stored procedure for increasing the identity by 1
— Step 2: Get the new, unique value
SELECT MAX(seqid) FROM [tblSeq]
I hope you found the article useful!
Artemakis Artemiou is a Senior SQL Server Architect, Author, a 9 Times Microsoft Data Platform MVP (2009-2018) and a Udemy Instructor. He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Moreover, Artemakis teaches on Udemy, you can check his courses here.