Identity Bug in Sql Server 2012

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: