27 | July | 2012 | SQL with Manoj

“Identity Gap” Issue with the new SEQUENCE feature in SQL Server 2012 … and its workaround

July 27, 2012 3 comments

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

Categories: SQL Server 2012, SQL Tips Tags: Denali, SEQUENCE bug, SQL Server 2012

Jump/Gap Issue with IDENTITY property in SQL Server 2012 … and its workaround (not a bug)

July 27, 2012 5 comments

Sometime back there was discussion going on in an SQL Server forum regarding issues with IDENTITY property in the new SQL Server 2012. The issue was that, when restarting SQL Server (or service) the IDENTITY value in a particular table having IDENTITY column jumps to a higher random number and starts from there.

I tried to reproduce this issue, the SQL Server version I’m using is as follows:

Microsoft SQL Server 2012 RC0 - 11.0.1750.32 (X64) 
	Nov  4 2011 17:54:22 
	Copyright (c) Microsoft Corporation
	Business Intelligence Edition (64-bit) on Windows NT 6.1  
	(Build 7601: Service Pack 1)

I reproduced the issue with following code:

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

-- INSERT some records:
INSERT INTO TEST_IDENTITY (NAME)
SELECT 'Manoj Pandey'

INSERT INTO TEST_IDENTITY (NAME)
SELECT 'Gaurav Pandey'
GO

-- Check recently inserted records:
SELECT * FROM TEST_IDENTITY -- 2 records, with ID value 1, 2.

-- Check the current IDENTITY value:
DBCC CHECKIDENT ('TEST_IDENTITY')
--Checking identity information: current identity value '2', current column value '2'.
--DBCC execution completed. If DBCC printed error messages, contact your system administrator.
GO


-- RESTART SQL Server and check the current IDENTITY value:
DBCC CHECKIDENT ('TEST_IDENTITY')
--Checking identity information: current identity value '11', current column value '2'.
--DBCC execution completed. If DBCC printed error messages, contact your system administrator.

-- INSERT a new record:
INSERT INTO TEST_IDENTITY (NAME)
SELECT 'Garvit Pandey'
GO

-- Check recently inserted records:
SELECT * FROM TEST_IDENTITY -- 3 records, with ID value 1, 2, 12.
GO

--// Final cleanup
DROP TABLE TEST_IDENTITY

Finally I got the following output:

As you can see by running the above test before I restarted SQL Server the IDENTITY value was 2, but when I restarted the IDENTITY value changed to 11.
Thus the new records was inserted with value = 12.

The above bug/issue has been logged in the Microsoft Connect site, [here].

Workaround: Right now there is no fix for this, so you need to check all tables in you database every time your SQL Server restarts and reseed the correct IDENTITY value. Check my blog post to reseed the IDENTITY value.