What caused SQL Server's Sequence number to skip?

user_4877272 0
Viewed 432

I am using SQL Server's Sequence to generate running number for my document number on my "doc_no" column. A quick check revealed my table's Identity column number to skip is due to server restart and one way to prevent that is to disable the cache (on my local machine). What about sequence? Is it the same way for Sequence? Is there any way to see the current sequence number in the Sequence itself?

enter image description here

CREATE SEQUENCE [dbo].[PB_SEQ_DOCNO] 
 AS [int]
 START WITH 1
 INCREMENT BY 1
 MINVALUE -2147483648
 MAXVALUE 2147483647
 CACHE 
GO
sql-server sql
1 Answers

The CACHE keyword (without an explicit cache size) uses the default server cache size - which is probably 1000 (just like for the IDENTITY columns). So SQL Server in the background caches the next 1000 sequence numbers. If your server crashes or unexpectedly restarts, those cached sequence numbers are "lost" (as in your case here, obviously).

You can use a smaller cache size (by specifying e.g. CACHE 50) or you can even turn this off altogether (NO CACHE) - benefit is not/less gaps, drawbacks is slower performance when dishing out the sequence numbers.

user_133020
Related