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]
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!
--
My Latest Projects: