Menu
And they seem to be fairly large skips, in the thousands even. Here's an example:
The last Ids of this table go above the 30.000 mark even though there are less than a thousand rows in the table.
EDIT: This is happening in an old, small desktop application of mine and I have not ever deleted rows from this table nor have I used any transactions and rollbacks.
470
Usually, it occurs when the SQL Server instance is being forced to restart. This skipped gap is particularly depending on the data type of the column, and most of the time, it is possible that column type can be INT, BIGINT, or Numeric.
In SQL Server 2012 - MS had introduced 'Identity Cache'. This feature had a bug of auto-incrementing ID column by '1000'. For example, if ID columns are 1, 2 and when an ID Jump happens the next ID column is 1003, instead of '3'. There are workarounds available to fix this issue.
The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature. In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record. Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change it to IDENTITY(10,5) .
What is causing this?
A couple of potential causes come to mind:
id?There could be more potential causes I'm not thinking of.
How can I prevent this in the future?
Depends on the cause. But it's kind of a moot point, really. Why would you need to "prevent this"? What exactly is wrong with this situation? Identifiers don't need to be consecutive, they just need to be unique. (And preferably sequential for an index, otherwise the database will have to re-build the index.)
How can I stop my current table from continuing on this dark road?
The dark road of... generating unique identifiers? Well, I guess you could manually supply unique identifiers. GUIDs are good for that sort of thing. There are pros and cons, though. (I don't know if recent implementations have improved this, but historically GUIDs don't make for a good clustered index.)
187
This is by design in SQL server. SQL Server guarantees that the next number generated will be unique and incremental, it does not guarantee it will be in sequence.
This started in SQL 2012. Simply restart the service to repro the issue. It is well know change in behavior, it has always been documented this way, and is marked by design. Its just an unexpected change in behavior
31
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
