Aug 15 2014 :: by Alex Yumashev

TL:DR version: when you restart an SQL Server instance, the "Identity" columns jump by 1000 for every int column and by 10000 for every bigint column. Microsoft claims that's a "feature". Please upvote this bug in MS's bugtracker.
(note to *nix folks: "Identity" is the SQL Server's analogue of "auto_increment")

Full version: Today one of our customers has reported that the ticket-numbers in his helpdesk-app installation have suddenly jumped from 1,2,3,.. to 1001, 1002,.... Turns out this happened after he restarted the server. Here's what I mean:

And after you restart your SQL server:

"Feature" not a bug

After a bit of investigating and googling, we have discovered we're not the only ones. That's actually a known issue with SQL Server 2012 and higher, that many developers have faced. It has even been reported to Microsoft. And - guess what - Microsoft responded that's a "feature", and "closed" the bug-report. Someone named Bryan who is obviously a team member, responded in the bug-tracker thread:

I am the dev owning the identity feature. To boost the performance for high end machines, we introduce preallocation for identity value in 2012. And this feature can be disabled by using TF 272 (then you will get the behaviour from 2008R2).


So. If any of you has faced this "feature" and it breaks your business logic (or you're afraid to run out of ints) - just start the SQL Server with the -T272 parameter. Open "SQL Server Configuration Manager" - "SQL Server Services" - right-click on the instance - "Properties" - "Startup Parameters" - "specify a startup parameter" - "-T272" - "Add"

Please upvote this bug on the MS site if you have a spare minute. Thanks!

'SQL Server bug MS calls a "feature"' was written by Alex Yumashev
Alex Yumashev
Alex has founded Jitbit in 2005 and is a software engineer passionate about customer support.

Subscribe comments