Post date: Jan 21, 2013 10:35:34 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()));GOIF OBJECT_ID('dbo.RequestSequence') IS NOT NULL DROP PROCEDURE dbo.RequestSequence GOCREATE PROC dbo.RequestSequence ASBEGIN INSERT dbo.Sequence OUTPUT inserted.SequenceFrom, inserted.SequenceTo, inserted.DateRequested DEFAULT VALUESEND;GO-- Example, each transaction will always get new values from the sequence even if the transaction is rolled backIF 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 ROLLBACKBEGIN TRANSACTION INSERT #MySequence EXEC dbo.RequestSequence SELECT *,( SELECT transaction_id FROM sys.dm_tran_current_transaction) AS TransactionID FROM #MySequence ROLLBACK