SQL Server Sequences – Guaranteed Different Values for Concurrent Transactions?

concurrencyisolation-levelsequencesql server

In documentation it is told that:

Duplicate validation only occurs once a record is fully populated. This can result in some cases where the same number is used for more than one record during creation, but then gets identified as a duplicate

I do NOT want to use that number in an insert query like this:

INSERT Test.Orders (OrderID, Name, Qty)  
    VALUES (NEXT VALUE FOR Test.CountBy1, 'Tire', 2) ;

I want to obtrain that number in my web application and use it to construct a ciphered text. and store the ciphered code in database. Hence, in my case, there is no duplicate checking while inserting.

The thing I need to know is that whether it is probable for two clients calling NEXT VALUE FOR function nearly at the same time, and get the same values? or not? (I need this not to happen)

Best Answer

Hence, in my case, there is no duplicate checking while inserting.

If you really do not intend to allow duplicate values, you should create a UNIQUE constraint on the column where the ciphered text is to be stored. Your application will have to be coded to retry on inserts that fail with a unique constraint error.

The thing I need to know is that whether it is probable for two clients calling NEXT VALUE FOR function nearly at the same time, and get the same values?

While it's unlikely, under normal circumstances, to get the same value on two separate calls to NEXT VALUE FOR from the same sequence, there are things that can cause it to happen:

  • the sequence was specified with the CYCLE option, and the maximum value has been reached (causing it to roll over)
  • the sequence was reset (ALTER SEQUENCE RESTART)
  • as documented, accessing the same sequence object multiple times in the same statement will produce the same value each time, so make sure not to do something like this expecting two different numbers:
SELECT 
    NEXT VALUE FOR dbo.TestSequence,
    NEXT VALUE FOR dbo.TestSequence;

You may also be interested in reading Sequence Tables, by Paul White.

Related Question