CODEWITHSUNDEEP
sqlsql-serveruniqueidentifierunique-id
Ilya Chernomordik
Ilya Chernomordik

Reputation: 30175

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

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?

Upvotes: 2

Views: 9015

Answers (3)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131189

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

Upvotes: 3

marcothesane
marcothesane

Reputation: 6721

If you want a behaviour that matches the one of an IDENTITY column, try:

CREATE SEQUENCE mydb.dbo.mysequence;

And then, repeatedly:

SELECT NEXT VALUE FOR mysequence;

And , if you want to play some more, see here:

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-ver15

happy playing ...

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520918

One option here would be to use a UUID to represent each unique record. Should you want to generate the UUID within SQL Server, you could use the NEWID() function (see the documentation for more information). If this value would be generated from your application code, you could convert it to uniqueidentifier type within SQL Server using CONVERT.

For reference, a UUID is a 16 byte unique identifier. It is extremely unlikely that your application or SQL Server would ever generate the same UUID more than once. They look like this:

773c1570-1076-4e19-b728-6d7b0b20895a

Upvotes: 0

Related Questions