http://www.sqlservercentral.com/blogs/james-sql-footprint/2013/07/23/gap-issue-in-sql-server-2012-identity-column/

Printed 2014/10/25 12:29PM

Gap issue in SQL Server 2012 IDENTITY column

By jamesxu98918, 2013/07/23

Found a gap issue when using IDENTITY column in SQL Server 2012, every time I restarted sql server service, the current identity number will jump to 1000. here is the repro script

CREATE TABLE MYTEST (ID INT IDENTITY(1,1),
CREATE_DATE datetime);

GO

INSERT INTO MYTEST(CREATE_DATE)
VALUES (GETDATE())
GO 10

DBCC CHECKIDENT ('MYTEST')
GO

the output will be like
Checking identity information: current identity value '10', current column value '10'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

then restart sql service, and run

DBCC CHECKIDENT ('MYTEST')
GO

this time we got
Checking identity information: current identity value '1001', current column value '10'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


by searching Google, there is a product connection about this issue
https://connect.microsoft.com/SQLServer/feedback/details/739013/alwayson-failover-results-in-reseed-of-identity#details

There are 2 workaround provided.
1. Use trace flag 272






















in my testing, the trace flag 272 works good.

2. Use a sequence generator with the NO CACHE setting(http://msdn.microsoft.com/en-us/library/ff878091.aspx).This will cause a log record to be generated for each generated sequence value. Note that the performance of sequence value generation may be impacted by using NO CACHE.

Example:
CREATE SEQUENCE s1 AS INT START WITH 1 NO CACHE;
CREATE TABLE t1 (Id INT PRIMARY KEY DEFAULT NEXT VALUE FOR s1, col INT NOT NULL);


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.