Sequence Objects in SQL Server

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:

2. Create a stored procedure that will be doing the following upon its execution:

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
USE [DB_Name]

--Step 1: Create the table for storing the data (once-off)
    [SeqID] [int] IDENTITY(1,1) NOT NULL,
    [SeqVal] [bigint] NULL

--Step 2: Create the stored procedure for manipulating the data (once-off)
      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
EXEC [GetNewSeqVal_tblSeq]

-- Step 2: Get the new, unique value
SELECT MAX(seqid) FROM [tblSeq]

I hope you found the article useful!

My Latest Projects:

Labels: , , ,