Generating On Demand Sequential Values in SQL Server

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
Read more about DBCC CHECKIDENT

About the author

Armando Prato has close to 30 years of industry experience and has been working with SQL Server since version 6.5.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2007-07-25