Generating On Demand Sequential Values in SQL Server

By: Armando Prato | Read Comments | Print Armando has over 24 years of industry experience and has been working with SQL Server since version 6.5. Related Tips: More

Problem
I recently had a business situation where I needed to generate a unique “handle” on groups of rows in my database. This handle (in reality, a grouping identifier) was to be used to logically lock these rows from further processing and to allow them to be picked up for processing by an offline routine as a group. Exploring a number of potential solutions, I finally settled on one that required no manual maintenance on my part.  I also wanted to use an approach that would allow me to use built-in SQL Server functionality versus having to write a lot of code.

Solution
The way this was handled was to use the built-in identity value generation that SQL Server offers for a table column.  By using an identity value you can have SQL Server generate a integer value that can be used to create a unique value for each processing step.  Another possible option for this could have been to use the NEWID function but this function returns a 16 character uniqueidentifier value which is much harder to work with as well as the values are not sequentially numbered.

The following code illustrates my solution to this problem.

In this first step we create a table called "sequence".  This table only has one column that is an identity column.  The entire purpose of this table is to generate a unique sequential value for other processing.  No data will be stored in this table.

-- -- Create a table to hold the unique sequence values -- This table defines a single identity column -- if object_id('sequence') is not null drop table [sequence] go create table dbo.[sequence] ( sequenceid int identity(1,1) not null ) go

The next step in the process is to create a stored procedure that gets called to pass back the value that is created when a new row is inserted into the "sequence" table.  This procedure uses the "output" option to pass back the value to the calling process.  In addition, you will see that a rollback is issued after the insert.  This is because we do not want to store any data in this table.  This table is only used to generate a new value.  Each time a record is inserted a new identity value is generated.  Even with the rollback being issued the next time a new record is inserted the next sequential value will be returned, so there is no overlap of numbers.

-- -- This procedure is used for generating the unique value -- if object_id('get_next_sequence') is not null drop procedure dbo.get_next_sequence go create procedure dbo.get_next_sequence @seqno int output as set nocount on -- -- Insert into the dbo.[sequence] table; the identity produced -- will be passed back to the caller as a handle. -- -- Processing breakdown -- -- 1. We start a transaction -- 2. We insert into the table using the ‘default values’ option which triggers -- the identity column to increment -- 3. We now rollback the insert. Identity values are kept in SQL Server -- memory and are not subject to rollback. The rollback is in place to -- keep the table from growing too large. In effect, the table will always -- be empty. -- 4. We use the scope_identity() to grab the produced identity and we pass this -- value back to the caller. -- begin tran tran1 insert into dbo.[sequence] default values rollback -- This prevents the table from growing -- Send back the generated handle select @seqno = scope_identity() go

To get the next sequential number you would issue some code such as the following.  Each time you run this code a new value will be returned.

-- -- Sample call to the procedure to get the next handle -- declare @seqno int exec dbo.get_next_sequence @seqno output select @seqno

Summary
The returned generated value can then be used for updating the next set of rows for processing. It should be noted that a stored procedure was used in this case. The code in the procedure could also be rolled into any existing code. My preference was to isolate this logic to a single point of reference.

Though the sequence table is always empty, you can view the last id generated via the DBCC command CHECKIDENT.

DBCC CHECKIDENT([sequence]))

While the transaction with ROLLBACK is implemented to keep the table from growing, you may have some reason to eliminate it. If do you choose to eliminate the transaction, the sequence table will grow and you may want to schedule a job to clean up the entries. It should be noted that a TRUNCATE on the sequence table will reset the identity seed back to its starting seed value so it’s imperative that any clean up routine use DELETE.
 

Next Steps

• Read more on IDENTITY function options in SQL Server Books Online under IDENTITY
• Compare and contrast DELETE and TRUNCATE in SQL Server Books Online
• Read more about DBCC CHECKIDENT in SQL Server Books Online

Related Tips: More | Become a paid author

Last Update: 7/25/2007

Share:

Share

Tweet

Free SQL Server Learning Introduction to SQL Azure Join Herve Roggero on Wednesday, June 13 @ 3:00 PM EST to learn more.

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

"SQL diagnostic manager delivers response in minutes, not hours!" It takes just 5 minutes to connect your SQL Databases to source control. Got 5 minutes? Get started now. What grade do you think your SQL Servers get? Find out with a SQL Server Health Check consultant. Free Trial: Get Proactive Insight with Spotlight® for SQL Server Enterprise. Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now. Free web casts for DBAs and Developers on Performance Tuning, Development, Administration and more....