CODEWITHSUNDEEP
sql-serversql-server-2012sequences
Viral
Viral

Reputation: 393

Microsoft SQL Server sequences

We are using SQL Server 2012 and using sequence for columns where auto-incrementing values of type int are required.

In rare cases we are facing problems when sequence current value is not updated to the next value.

Recently when our server restarted having SQL services, we faced similar problem for below created sequence..

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

SqID,Current Value = 12 In SQL..

In tables which I used to insert records using above sequence, have inserted records with 

SqID (13, 14, 15....)

Once server is restarted, then at time insert query with throw exception for duplicate key.

So my question is, in which case current value of SqID is not properly updated and why ? Is there any workaround to avoid restart sequences manually for above case?

Or isn't there a thing in SQL Server which can automatically restart sequences in case of problem with SQL services (start / stop)?

Thanks

Upvotes: 2

Views: 333

Answers (1)

Alexei - check Codidact
Alexei - check Codidact

Reputation: 23078

It seems that in SQL2012 there were some reports similar to your problem. Check here and here.

The only solution I have found is the following:

ALTER SEQUENCE dbo.SqIDNO CYCLE NO CACHE;

which will affect performance.

Regarding previous suggested cause, documentation states that:

If the Database Engine stops abnormally for an event such as a power failure, the sequence restarts with the number read from system tables (39). Any sequence numbers allocated to memory (but never requested by a user or application) are lost.

So, no change of getting duplicates from this cause.

Upvotes: 1

Related Questions