Just Code‎ > ‎

TSQL - Transaction Safe Sequence generation in SQL Server 2008 and older

posted Jan 21, 2013, 2:35 AM by Peter Henell   [ updated Jan 21, 2013, 3:30 AM ]
Scenario: I want to generate a sequence of unique number that I can use as Primary keys in my application. For example, sometimes it can be good for performance to supply the PrimaryKey from the application instead of using Identity. Such scenarios would be when the primary key need to be used in other tables. If you generate that value in the application then you can avoid doing Insert then Update on the same table in the same transaction.

In SQL Server 2012 we would just use the new Sequence feature. In SQL Server 2008 and older we need to use something else.

One common way of doing this is to have a table with one row per 'sequence' and have an API with stored procedures to request numbers from the sequence. Doing this can be a risk if done inside a transaction. If you rollback that transaction then the values might be reused in the next transaction. You can prevent this by not allowing any active transactions in the stored procedure but that might not always be possible to prevent.

This method I'm presenting here is thread- and transaction safe. It will however require one table per Sequence.

IF OBJECT_ID('dbo.Sequence') IS NOT NULL
    DROP TABLE dbo.Sequence;
    
CREATE TABLE dbo.Sequence(
    SequenceFrom AS SequenceTo - 9999,
    SequenceTo BIGINT IDENTITY(10000, 10000) NOT NULL PRIMARY KEY CLUSTERED,
    DateRequested DATETIME2(7) NOT NULL DEFAULT(GETDATE())
);

GO

IF OBJECT_ID('dbo.RequestSequence') IS NOT NULL 
    DROP PROCEDURE dbo.RequestSequence
GO
CREATE PROC    dbo.RequestSequence
AS
BEGIN
    INSERT dbo.Sequence
    OUTPUT inserted.SequenceFrom, inserted.SequenceTo, inserted.DateRequested
    DEFAULT VALUES
END;

GO

-- Example, each transaction will always get new values from the sequence even if the transaction is rolled back
IF OBJECT_ID('tempdb..#MySequence') IS NOT NULL
    DROP TABLE #MySequence;
    
CREATE TABLE #MySequence(SequenceTo BIGINT, SequenceFrom  BIGINT, dateRequested DATETIME2(7));

BEGIN TRAN

    INSERT     #MySequence
    EXEC dbo.RequestSequence
    
    SELECT *,( SELECT transaction_id FROM sys.dm_tran_current_transaction) AS TransactionID FROM #MySequence
    
ROLLBACK

BEGIN TRANSACTION
    
    INSERT     #MySequence
    EXEC dbo.RequestSequence
    
    SELECT *,( SELECT transaction_id FROM sys.dm_tran_current_transaction) AS TransactionID FROM #MySequence
    
ROLLBACK
Comments