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:
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!