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