Friday, September 30, 2011

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:

  • 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
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:

Easily secure your SQL Server instances! Try out DBA Security Advisor!
DBA Security Advisor for SQL Server by SQLArtBits
DBA Security Advisor was developed by SQLArtBits. The tool was being carefully developed for an entire year and it contains comprehensive security checks and recommendations based on proven SQL Server security best practices. Feel free to download and use the Community Edition which is free and if you find it useful, you can consider upgrading to the Enterprise Edition!

Artemakis Artemiou [MVP]

Author & Editor

Artemakis Artemiou is a Senior SQL Server Architect, Software Developer and Microsoft Data Platform MVP. He is also an author, regular blogger, president of Cyprus.NET User Group and CY Country leader of INETA-EU. He is also the creator of DBA Security Advisor and In-Memory OLTP Simulator. Artemakis is a frequent guest author of worldwide well-respected online journals where he writes articles focusing on many SQL Server topics.

Reference: The SQL Server and .NET Hub (