Apparently this bug has been hanging around since April 2012, but I only heard about it this morning.
IF OBJECT_ID('TestTable') IS NOT NULL BEGIN DROP TABLE dbo.TestTable END GO CREATE TABLE dbo.TestTable( ID INT NOT NULL PRIMARY KEY IDENTITY(1,1), Value VARCHAR(1000) NOT NULL ) GO INSERT INTO dbo.TestTable(Value) SELECT 'Test' GO 101 checkpoint --RESTART SQL INSTANCE INSERT INTO dbo.TestTable(Value) SELECT 'Test' GO 101 SELECT * FROM dbo.TestTable
Now look at the IDENTITY values for the rows:
Skips upwards by 1000. Scary.
There is a connect item for this located here. Please vote! The connect item states that it is related to AlwaysOn. This is not true. This bug occurs on any restart of the sql service.
A work-around for this is to make sure to checkpoint the database before a restart, but obviously it’s not the best work-around as you don’t always control the failover (HA). All that being said, if you checkpoint before a restart, you’ll not see the identity bug:
Also, I’ve just installed Sql Server 2012 SP1 CTP and the bug still exists.