SQL with Manoj

In my previous post I discussed about an issue with IDENTITY property. Here today while working on a similar new feature “SEQUENCE”, I found a similar kind of behavior with it.

Here also when you restart SQL Server or restart the service the last sequence number jumps to a higher random number.

Here is a simple code to reproduce this issue:

-- CREATE a simple Sequence
CREATE SEQUENCE CountBy1 
	START WITH 1 
	INCREMENT BY 1 
	MINVALUE 0 
	NO MAXVALUE ;
GO


-- CREATE a test table:
CREATE TABLE TEST_SEQ (ID INT, NAME VARCHAR(200));

-- INSERT some records:
INSERT INTO TEST_SEQ
SELECT NEXT VALUE FOR CountBy1, 'Manoj Pandey'

INSERT INTO TEST_SEQ
SELECT NEXT VALUE FOR CountBy1, 'Gaurav Pandey'
GO

-- Check the INSERTed records before server restart:
SELECT * FROM TEST_SEQ
GO


-- RESTART SQL Server & INSERT a new record:
INSERT INTO TEST_SEQ
SELECT NEXT VALUE FOR CountBy1, 'Garvit Pandey'
GO

-- Check the INSERTed records after server restart:
SELECT * FROM TEST_SEQ
GO


--// Final cleanup
DROP TABLE TEST_SEQ
DROP SEQUENCE CountBy1

Finally I got the following output:

As you can see by running the above test before I restarted SQL Server the SEQUENCE value assigned to the last record was 2, but when I restarted the new SEQUENCE value generated is 51.

Reason: Actually while creating SEQUENCE object SQL Server engine caches the new SEQUENCE values to Increase performance. By default the cache size is 50, so it caches values upto 50 values, and when SQL Server restarts it starts after 50, that’s a bug.

Workaround: To avoid this situation you can put an “NO CACHE” option while declaring the SEQUENCE object, like:

-- CREATE a simple Sequence
CREATE SEQUENCE CountBy1 
	START WITH 1 
	INCREMENT BY 1 
	MINVALUE 0 
	NO MAXVALUE 
	NO CACHE ; -- here
GO

This will not cache the future values and you wont get this issue of jumping values and gaps.

To know more about SEQUENCES check my previous blog post, [link].