This is the weirdest thing. A handful of my clients have experienced this problem over the course of the last year. For some reason, a table where the index is not managed at all by any of my processes is incremented by exactly 1000. In other words, the previous recorded entry shows ID = 5333 and the next record shows ID = 6333!!! The index is the table key and is auto-incremented by SQL Server i.e. none of my processes do any re-indexations or anything. This is SQL Server 2014.
Does anyone have any idea what the heck is going on?
This is likely due to a change in behavior in SQL Server versions 2012 and later. See Kalen Delaney's article here. As explained in that article, you can set trace flag 272 to revert to the pre-2012 behavior (but that requires a server restart, if I am not mistaken).
But OMG! The very purpose of the identity is to be a hard reference, otherwise what's the point of its existence??? If this issue has not been completely fixed by MS, then I'm gasping for air!!!!!!!!!!!
Actually, the point of IDENTITY is to provide a non-repeating/always increasing value suitable for use as a surrogate key. It still satisfies that requirement. There has NEVER been any assurance (let alone guarantee), from MS, that the generated values will be sequential. The fact that it's sequential, most of time, leads people to believe something that's never been true.
If sequential values are required, a SEQUENCE should be used instead.
I understand your point but have to differ. When I set the field to be an IDENTITY and specify that the IDENTITY INCREMENT = 1, it is not acceptable for the increment to be randomly set to anything else on a whim. Basically, this renders the field unreliable, unpredictable and thus unusable. It may still meet the specifications of the requirements of SQL Server, but it ruins other user-defined processes which depended on a dependable mechanization stemming from SQL Server.
I'll disagree with you at first, and agree with you to some degree after... First and foremost, if you designed a "dependent process" around the functionality of a feature you didn't fully understand... That's on you, not Microsoft. BOL: CREATE TABLE (Transact-SQL) IDENTITY (Property)
It's not exactly hidden in the fine print... It was a design decision, between being sequential and being fast.. They chose being fast. If you're familiar with the relation model, surrogate keys exist only to uniquely identify a tuple (row of data). They aren't supposed to have ANY meany beyond that.
With that out of the way... I'll say that Microsoft has #@$% the bed on more than a few design decisions (their implementation of IDENTITY doesn't earn an honorable mention on that list)... But... I won't go down that road. I'll just say that it would have been nice had they left the fast vs sequential decision to their customers. I can't imagine that it would have been difficult to simply add an additional parameter allowing the user to specify their preference when creating a table. Simply creating a sequence object behind the scenes (without the hassle of creating one manually) would have been fine... or... given the amount of data held in memory for cached data and query plans, making room for the last identity value for every table wouldn't exactly be a burden... Meaning the "sequential" options wouldn't have to be more than a nanosecond longer than the "fast" option.
So, to sum up... I think it would be a nice option to have but it isn't a deal breaker for me. If I need an unbroken sequence, I'll use a SEQUENCE object. I'm far more concerned about the lack of inline scalar functions, the lack of GREATEST & LEAST functions, the lack of a row number on the new(ish) STRING_SPLIT and the inability to use DISTINCT in any of the windowing functions.
It isn't being "randomly set", the entire sequence is generated and available for INSERT, unless the SQL Service crashes or is shut down in one of the ways Kalen mentions.
Even under the previous behavior, a crash will cause a gap of at least one incremented value, depending on how many in-flight transactions failed. The alternative is duplicate identity values after a crash...is that preferable behavior?
Not in the slightest. There are several documented ways that identity can cause gaps and are valid behavior. If you've designed your system in such a way that those gaps never occur, congratulations, you're the first. It's generally understood that those gaps can and do happen and no system should rely on an unbroken sequence of identity values. And the behavior of the new increment is changed/cached in a predictable manner based on data type.
Also keep in mind that unless you have a primary key or unique constraint on your identity column you can still experience duplicate values.
If ANY of your INSERTS are rolled back then that IDENTITY will NEVER be assigned.
In a multi-user system Person-A starting to create a record, which for some reason takes a long time (e.g. blocking) then Person-B can have their record created first. Although created "later" Person-B will have the earlier IDENTITY value. So Chronological is not guaranteed either
Here's the bottom line: Don't ever build a system that assumes that IDENTITY is CONTIGUOUS ... as already said, use SEQUENCE if you need that (e.g. for an Invoice Number) - or manage the "next available number" yourself - e.g. in a NextInvoiceNUmber table - other benefits of that - you can allocate a block of numbers and you can tell a child-process what the Reserved Number(s) WILL be ahead of time.
I appreciate the time both of you took to make me (and hopefully many others now and in the future) the limitations of IDENTITY . I will definitely check out the SEQUENCE avenue or use another lock-controlled counter.
Be careful if you simply shift to SEQUENCE because , it has the same bloody problem as IDENTITY because, as of 2012, IDENTITY is actually created by a sequence behind the scenes. Further, you have to provide the value... it won't just auto-populate like IDENTITY does.
If you need to make the 1000 jump after reboot go away, you could try trace flag 272 but understand that there could be a huge difference in performance because MS made it so that every increment get's logged. Please see the following article for more details.