2

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)

4

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.

5
  • are these two circumstances under which duplicate may happen, the only situations in which a duplicate output may occur? May 1 '20 at 14:12
  • @ShahryarSaljoughi as far as I know, and under the circumstances you described, yes. I would still use a unique constraint to protect the integrity of your data, SQL Server is prone to bugs and edge cases (like any software). May 1 '20 at 14:53
  • problem is that I don't save the output of sequence anywhere in db. I'd mark you answer as answer, if it had more certainty, or its uncertainty was backed by some reference, or suggested some solution for having some sequence generator, which is certain that won't return same number twice, unless if its because of cycling ... May 1 '20 at 17:16
  • @ShahryarSaljoughi - won't the ciphered text be stored in the database? That's what I was saying you could add a unique constraint to. Maybe I have misunderstood. Erik has added an interesting alternative to this answer, if you'd like to peruse that article. May 1 '20 at 18:35
  • 1
    Note that when used in a SQL Query a SEQUENCE will return the same value for each row, but different values per row. EG SELECT NEXT VALUE FOR dbo.TestSequence, NEXT VALUE FOR dbo.TestSequence from sys.objects May 1 '20 at 20:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.