Menu
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.
To change the original seed value and reseed any existing rows, drop the identity column and recreate it specifying the new seed value. When the table contains data, the identity numbers are added to the existing rows with the specified seed and increment values.
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.
DBCC CHECKIDENT can reseed (reset) the identity value of the table. For example, YourTable has 25 rows with 25 as last identity. If we want next record to have identity as 35 we need to run following T SQL script in Query Analyzer.
After inserting the data via sql script that had
SET IDENTITY_INSERT [dbo].[table] ON
...
SET IDENTITY_INSERT [dbo].[table] OFF
the identity seed has increased by 10,000
I have tried running reseed
dbcc CHECKIDENT ('vendors', 'reseed', 57439)
but I get the error saying the DBCC command 'CHECKIDENT' is not supported in this version of SQL Server.
How to stop in the future this problem?
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