SQL Server - Azure - DotNet - Programming Courses and More - SQLNetHub

Sequence Objects in SQL Server

by

SQL Server 2012, among other introduced 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! 🙂

 

How to Implement Sequence-like Functionality in SQL Server 2008R2 and Earlier

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]
GO

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

--Step 2: Create the stored procedure for manipulating the data (once-off)
CREATE PROCEDURE [GetNewSeqVal_tblSeq]
AS
BEGIN
      insert into tblSeq (SeqVal) values (1)
      if ((select max(seqid) from tblSeq))!=1
        delete from tblSeq where seqid < (select max(seqid) from tblSeq)   
END
GO




--
-- 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]
GO

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

 

I hope you found the article useful!

 

 

Learn More Tips like this – Enroll to the Course!

Check our online course titled “Essential SQL Server Development Tips for SQL Developers” (special limited-time discount included in link).

Sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!

Essential SQL Server Development Tips for SQL Developers - Online Course
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Learn More

 

Upgrade your Tech Skills – Learn all about Azure SQL Database

Enroll to our online course on Udemy titled “Introduction to Azure SQL Database for Beginners” and get lifetime access to high-quality lessons and hands-on guides about all aspects of Azure SQL Database.

Introduction to Azure SQL Database (Online Course - Lifetime Access)
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Learn More

 

 

Featured Online Courses:

 

Read Also:

 

Subscribe to our newsletter and stay up to date!

Subscribe to our YouTube channel (SQLNetHubTV)!

Like our Facebook Page!

Check our SQL Server Administration articles.

Check out our latest software releases!

Check our eBooks!

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub

Views: 2,360