Advertisements
Home > SQL Server 2012, SQL Tips > Issue with the new SEQUENCE feature in SQL Server 2012 … and its workaround

Issue with the new SEQUENCE feature in SQL Server 2012 … and its workaround


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].

Advertisements
  1. Bits and Pieces
    April 23, 2013 at 5:41 pm

    It’s not a bug, it is designed that way.
    Sequences only guarantee uniqueness (unless defined with CYCLE), not consecutiveness.
    You really should not use a sequence if you want gapless values. Not only database restarts cause gaps, but also transactions that are rolled back.

    • April 23, 2013 at 5:47 pm

      You are right “Bits and Pieces”… I learnt long back but didn’t updated it here. Thanks for pointing it out, I’ve updated the article above.

      But you can still use Sequences even if you don’t want gaps. Only you need to use the “NO CACHE” option. This option do not skip values after server restart, but still it will not guarantee no-gaps, as you mentioned transaction rollbacks.

  2. Damien Laffan
    December 17, 2012 at 3:59 am

    Sequence Numbers
    http://msdn.microsoft.com/en-us/library/ff878058.aspx

    General Remarks

    “Sequence numbers are generated outside the scope of the current transaction. They are consumed whether the transaction using the sequence number is committed or rolled back.”

    “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. This functionality may leave gaps, but guarantees that the same value will never be issued two times for a single sequence object unless it is defined as CYCLE or is manually restarted.”

    Limitations
    “The sequence object generates numbers according to its definition, but the sequence object does not control how the numbers are used. Sequence numbers inserted into a table can have gaps when a transaction is rolled back, when a sequence object is shared by multiple tables, or when sequence numbers are allocated without using them in tables. When created with the CACHE option, an unexpected shutdown, such as a power failure, can lose the sequence numbers in the cache.”

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: