Gap issue in SQL Server 2012 IDENTITY column

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);