How to generate a unique numeric ID in SQL Server (not using identity)?

Question

I need a unique number id for my table. Usually I would use Identity in Sql Server, but there is a catch to my use case. I would like to know the id before the row is created (to be able to reference it in other records in memory, before committing everything to the database).

I don't know if it's possible to achieve with Identity, but I could not figure that out.

So my next best guess is that I need a table that will store one value and keep incrementing it and returning me a new value for the id. Access would have to be locked so that no two operations can get the same value.

I am thinking of using e.g. sp_getapplock @Resource = 'MyUniqueId' to prevent same number from being returned to a caller. Perhaps I can use ordinary locking in transactions for that as well.

Is there any better approach to the problem?

Answer 1

You can create a SEQUENCE object that produces incrementing values. A SEQUENCE can be used independently or as a default value for one or more tables.

You can create a sequence with CREATE SEQUENCE :

CREATE SEQUENCE Audit.EventCounter  
    AS int  
    START WITH 1  
    INCREMENT BY 1 ; 

You can retrieve the next value atomically with NEXT VALUE FOR and use it in multiple statements eg :

DECLARE @NextID int ;  
SET @NextID = NEXT VALUE FOR Audit.EventCounter; 

Rolling back a transaction doesn't affect a SEQUENCE. From the docs:

Sequence numbers are generated outside the scope of the current transaction. They are consumed whether the transaction using the sequence number is committed or rolled back.

You can use NEXT VALUE FOR as a default in multiple tables. In the documentation example, three different types of event table use the same SEQUENCE allowing all events to get unique numbers:

CREATE TABLE Audit.ProcessEvents  
(  
    EventID int PRIMARY KEY CLUSTERED   
        DEFAULT (NEXT VALUE FOR Audit.EventCounter),  
    EventTime datetime NOT NULL DEFAULT (getdate()),  
    EventCode nvarchar(5) NOT NULL,  
    Description nvarchar(300) NULL  
) ;  
GO  
  
CREATE TABLE Audit.ErrorEvents  
(  
    EventID int PRIMARY KEY CLUSTERED  
        DEFAULT (NEXT VALUE FOR Audit.EventCounter),  
    EventTime datetime NOT NULL DEFAULT (getdate()),  
    EquipmentID int NULL,  
    ErrorNumber int NOT NULL,  
    EventDesc nvarchar(256) NULL  
) ;  
GO  
  
CREATE TABLE Audit.StartStopEvents  
(  
    EventID int PRIMARY KEY CLUSTERED  
        DEFAULT (NEXT VALUE FOR Audit.EventCounter),  
    EventTime datetime NOT NULL DEFAULT (getdate()),  
    EquipmentID int NOT NULL,  
    StartOrStop bit NOT NULL  
) ;  
GO