CACHE vs NO CACHE in sequence in SQL Server

 A SEQUENCE generates numeric values. CACHE and NO CACHE control how SQL Server stores upcoming sequence numbers in memory.

CACHE: Pre-allocates sequence numbers in memory for faster performance.

NO CACHE: Generates sequence numbers one-by-one from disk (slower but safer).

Let see the demo

CREATE SEQUENCE Seq_Order_Cache     START WITH 1     INCREMENT BY 1     CACHE 50;

Object created successfully. Selecting few records.

SELECT NEXT VALUE FOR Seq_Order_Cache; SELECT NEXT VALUE FOR Seq_Order_Cache;

See the value as below

What happens internally:  SQL Server reserves 50 numbers in memory (1–50) for the faster performance and If SQL Server restarts, unused cached numbers are lost.

see the current sequence value is 2.

 Now we are restarting the server. Here I have restarted my system..

Running the below query

SELECT NEXT VALUE FOR Seq_Order_Cache;

SELECT NEXT VALUE FOR Seq_Order_Cache; 

 

Here we have loosed the sequence between 3 to 50. It starts form 51.

 No cache:

We can create sequence without cache using below script.

 

CREATE SEQUENCE Seq_Order_NoCache     START WITH 1     INCREMENT BY 1     NO CACHE;

 See the value

What happens internally the Value generated directly from disk and no number loss on restart it is bit slower under high load. After restart, next value continues correctly. After restart the system now running this sequence

Here we are not losing sequence.

Using the Alter command we can Alter the sequence.

See below.

ALTER SEQUENCE Seq_Order_Cache     NO CACHE;

 Now it becomes as no cache.