By: Armando Prato | Last Updated: 2007-07-25 | Comments | Identities
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.
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]
create table dbo.[sequence]
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
create procedure dbo.get_next_sequence @seqno int output
-- Send back the generated handle
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
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.
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.
- 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
Last Updated: 2007-07-25
About the author
View all my tips