CODEWITHSUNDEEP
sqlsql-servert-sqlsequence
Jonathan
Jonathan

Reputation: 651

Sequence not get current next number

I have a sequence like:

CREATE SEQUENCE [dbo].[DesignSequence] 
 AS [int]
 START WITH 1
 INCREMENT BY 1
 MINVALUE 0
 MAXVALUE 2147483647
 CACHE 
GO

So in my table I have column with this sequence in Default Value or Binding : (NEXT VALUE FOR [dbo].[DesignSequence])

Problem is for some reason my next number is 4140, but when I added new row to table it added number: 3627 . So now I have duplicate values. Why my sequence is failing? any reason for this?

Upvotes: 0

Views: 40

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89091

Sequences aren't automatically synchronized with the existing data in your tables. If you are adding a sequence to an existing table you need to set its start with appropriately.

You can reset it like this:

declare @maxVal bigint = (select max(id) from DesignTable)
declare @sql nvarchar(max) = concat('alter sequence DesignSequence restart with ', @maxVal + 1)
print (@sql)
exec (@sql)

Upvotes: 3

Related Questions