by Ahmed
29. June 2010 18:17
SQL Server provides auto identity to generate sequential IDs in a table which is handy. But there is no feature (that I know of in 2005 and before) to generate sequential IDs across multiple tables, similar to the Sequence Generator in Oracle. Recently I came across a requirement where I had to bulk insert parent and child records in 2 separate tables and I did not want to go the Sequential GUID route.
While from the surface it may look simple, the challenge with generating unique sequences is reliability in a multi-user environment. This is why I always prefer the database system providing such functionality. Here is something that I came up with that is simple and worked quite well for my needs. It has 2 main components – a table & a stored proc.
CREATE TABLE [dbo].[EntityIdSequence](
[SeqNo] [bigint] IDENTITY(1,1) NOT NULL,
[Used] [bit] NULL
) ON [PRIMARY]
GO
CREATE PROCEDURE [dbo].[GenerateEntityIdSequences]
@count int
AS
SET NOCOUNT ON;
declare @sequences as table (seqno bigint)
while (@count > 0)
begin
insert into [entityidsequence] values (1);
insert into @sequences values (scope_identity()); -- get the last generated id
set @count = @count - 1;
end
select seqno from @sequences;
GO
Notice that I didn’t have to specify any special transaction isolation level. And I am able to generate a bunch of unique sequences at a time which can cut down on database roundtrips. The real key here is making use of the scope_identity() system function. Another important thing to remember is that concurrent requests to GenerateEntityIdSequences for multiple sequences could result in skipped sequences e.g. 1,2,4,6,7. But they should always be unique.
We can also reseed if need be. This can be useful if you wanted to turn off auto ID on an existing table although that would require recreating & repopulating the table. Here is the command for reseeding to start generating sequence from 1000:
DBCC checkident(‘EntityIdSequence’, RESEED, 999)
Subscribe