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

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


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.

Advertisement
  1. March 12, 2014 at 4:36 pm

    From MSDN:

    To boost the performance for high end machines, preallocation for identity value in 2012 is introduced.
    And this feature can be disabled by using TF 272 (then you will get the behavior from 2008R2).

    The identity properties are stored separately in metadata.
    If a value is used in identity and increment is called, then the new seed value will be set.
    No operation, including Rollback, Failover, ….. can change the seed value except DBCC reseed.
    Failover applies for the table object, but no the identity object.
    So for failover, you can call checkpoint before manual failover, but you may see gap for unplanned cases.
    If gap is a concern, then I suggest you to use TF 272.

    For control manager shutdown, we have a fix for next version (with another TF). This fix will take care of most control manager shutdown cases.

    Source: https://connect.microsoft.com/SQLServer/feedback/details/739013/alwayson-failover-results-in-reseed-of-identity#details

  2. S.E.
    April 23, 2014 at 2:28 pm

    As far as I know, the IDENTITY functionality does NOT provide guarantee for not having gaps. It only guarantees that the numbers it creates are ascending (providing you don’t reseed).

    • April 27, 2014 at 12:44 pm

      Yes you are right S.E.

      Adding to your comments you can also creating identity series in descending numbers.

  1. July 27, 2012 at 7:40 pm
  2. October 19, 2015 at 7:44 pm

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 )

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: