SQL Server World

(Applies to SQL Server 2012 Enterprise Edition only)

Problem:
A feature or bug in SQL Server 2012 results in an unexpected behavior for identity columns. Technically, the specification for SQL does not dictate that identity columns be numerically contiguous, only that they are steadily increasing. In previous versions of SQL Server identity columns had been implemented by Microsoft to be contiguous, but in 2012, Microsoft changed the behavior so that identity columns cache values differently and may no longer be contiguous.

In practice what tends to happen is that when SQL Server service is restarted, failed over, hits a log checkpoint or possibly under other circumstances, you may see a number jump in identity column numbers, for example:

1, 2, 3, 4, (restart occurs), 1001, 1002, 1003, 1004, 1005, (failover occurs), 2000, 2001, 2002. . .

If an application is expecting contiguous numbering, this may result in unexpected issues. This can cause serious data integrity issue.

Solution:
There is a trace flag 272 that can be enabled to force the contiguous numbering behavior from previous versions of SQL Server. The downside is slightly slower identity column values upon insert, which should merely result in the same identity column performance from previous versions. You can read the comments SQL Server community leaders has sent to Microsoft on this issue.
http://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity

Test:
I reproduced the error using sql server 2012 on my laptop. Anybody can use it on test server to see the issue.

— Example is using AdventureWorks2012.Production.WorkOrder table
— Check if table has identity column using sp_help [production.workorder]

— First Check the latest value of identity column
select max(workorderID) from production.workorder — 72591

— Now insert a row
INSERT INTO [Production].[WorkOrder]
([ProductID]
,[OrderQty]
,[ScrappedQty]
,[StartDate]
,[EndDate]
,[DueDate]
,[ScrapReasonID]
,[ModifiedDate])
VALUES
(1
,1
,1
,getdate()
,Sysdatetime()
,getdate() +1
,1
,Sysdatetime())
GO

— Check the value of identity column
select max(workorderID) from production.workorder — 72592

——–*************** Restart sql server service *************——————
— Insert another row
INSERT INTO [Production].[WorkOrder]
([ProductID]
,[OrderQty]
,[ScrappedQty]
,[StartDate]
,[EndDate]
,[DueDate]
,[ScrapReasonID]
,[ModifiedDate])
VALUES
(2
,2
,2
,getdate()
,Sysdatetime()
,getdate() +1
,2
,Sysdatetime())
GO

— Finally check the identity colum value again. This time it jumped to 73592, an increase of 1000.
select max(workorderID) from production.workorder — 73592

Result:

After enabling traceflag -T272, I repeated the previous steps and there was identity column increase of 1. I repeated this test on Developer edition SQL server 2012 and got increase of 2 after enabling traceflag. So at this point this traceflag only helps in Enterprise Edition only.

WorkOrderID ProductID OrderQty StockedQty ScrappedQty StartDate
72591 531 26 26 0 7/3/08 0:00
72592 1 1 0 1 6/16/14 14:36
73592 2 2 0 2 6/16/14 14:37
73593 3 3 0 3 6/16/14 14:37
74592 4 4 0 4 6/16/14 14:38
75593 4 5 0 5 6/16/14 14:42
75594 4 6 0 6 6/16/14 14:43
75595 4 7 0 7 6/16/14 14:44
75596 4 8 0 8 6/16/14 14:46