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).
CREATE TABLE MainTable ( 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)) AS ( 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) SET @newId=SCOPE_IDENTITY() 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)