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?

You can use SEQUENCE to retrieve a new unique value from the server. You don't need explicit locks — Panagiotis Kanavos, Nov 12 '20 at 08:57
yes, looks like it's exactly what I need, does it guarantee uniqueness? — Ilya Chernomordik, Nov 12 '20 at 09:00
It does - even rolling back a transaction won't reset a SEQUENCE. On the other hand, if you explicitly reset it it will start producing the same values again. That's no different than reseeding an IDENTITY — Panagiotis Kanavos, Nov 12 '20 at 09:09

score 3 · Accepted Answer · answered Nov 12 '20 at 09:05

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

Tim Biegeleisen · Answer 2 · 2020-11-12T09:00:51.793

0

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

edited Nov 12 '20 at 09:00

answered Nov 12 '20 at 08:58

Tim Biegeleisen

502,043
27
286
360

That would result in index fragmentation quite quickly. At the very least one should use `NEWSEQUENTIALID` or the equivalent on the client – Panagiotis Kanavos Nov 12 '20 at 09:00
Yes, I have thought about using UUID, but as far as I know using UUID as primary key would make all the future joins/searches slower compared to a number – Ilya Chernomordik Nov 12 '20 at 09:01

score 0 · Answer 3 · answered Nov 12 '20 at 09:02

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 ...

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

3 Answers3