A database on SQL Server 2005/2008/R2 is currently using an identity column in a separate table as primary key source. The reason is that the application, with NHibernate as ORM, needs to know the PK value before it is actually inserted (NHib can work with identity PK columns, but with some serious problems, like pre-INSERTs of invalid rows outside of transactions).

    ID int primary key,
    Name nvarchar(100) not null,
    -- more columns

CREATE TABLE IdSrc_MainTable -- one IdSrc_ table for each data table
    ID int identity(1,1),
    DummyCol int null

CREATE PROCEDURE GetNewId(@TableName nvarchar(100))
    DECLARE @newId int
    DECLARE @sql nvarchar(4000)

    -- check if <@TableName> table exists, prevent SQL injection

    -- create this as dynamic SQL due to @TableName variable, 
    -- with <@TableName> replaced by the actual table name.

    SET @sql = N'
    BEGIN TRANSACTION GetNewIdTran -- or savepoint in existing tran
    INSERT INTO IdSrc_<@TableName> (DummyCol) VALUES (null)
    ROLLBACK GetNewIdTran' -- with name if inner transaction

    EXEC sp_executesql @sql, N'@newId int OUTPUT', @newId OUTPUT

    RETURN @newId -- 

Usage is like:

DECLARE @newId int

EXEC @newId=GetNewId(N'MainTable')
INSERT INTO MainTable (ID, Name, otherStuff) VALUES (@newId, @name, @otherStuff)

NHibernate uses a custom ID generator which calls the stored procedure.

While this is o.k. for single row inserts, bulk operations can't have an explicit ID/primary key assignment and require a cursor which repeats the above code for each row. A function (as direct replacement of a sequence) with an ID result value fails due to the DML operation, and stored procedure output/returned int results can't be used directly in insert/update statements.

When attempting to pre-fetch many IDs in a GetNewIds procedure, duplicate usages have to be prevented: if somebody else gets a new ID from the IdSrc_ table meanwhile and only a min/max ID range is returned. Long lasting table locking shall be avoided to prevent a possible total deadlock.

It seems to be impossible to return result sets or table variables with IDs for a bulk operation - any attempt with table value functions, table value OUT parameters or INSERT INTO from stored procedure result set fails due to the DML operation in GetNewId(s).

Even if I have a "safe" ID range, using locking and DBCC CHECKIDENT with RESEED, I still can't find a way to insert incremental PK values, like

INSERT INTO MainTable (ID, Name, other) -- already existing, not select into!
    SELECT dbo.IncrementAndGetNext(@IdCounter), Name, Other 
    FROM StagingTable. -- won't work

Is there a safe and well performing way to simulate sequences, which allow both pre-fetching and set-based DML operations?

(for a data migration, I once even worked around this using dynamic DBCC CHECKIDENT calls with RESEED, assuming no applications were running then which could steal my new IDs. Luckily, it was an SQL Server 2012, which allowed me sequences to fill in IDs in set-based INSERT/UPDATE - even if the DB schema didn't use them due to downward compatibility)

  • Second solution here any good to you? stackoverflow.com/a/3462957/73226 – Martin Smith Jun 23 '15 at 18:14
  • Counter value update solutions (2nd in link) were probably omitted due to deadlock risks long ago. Good for client programs which can open a separate connection/transaction (NHibernate has this kind of ID generator implementation), but not if to be called by triggers, stored procedures and the like. T-SQL needed s.th. like pragma autonomous transaction in Oracle, which doesn't block and still commits fetched IDs, even if the main transaction is rolled back (an advantage of SQL Server Identity over counter tables). – Erik Hart Jun 23 '15 at 18:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.