While working on the Clearshift project We have faced one issue regarding the Identity column value suddenly jumps. Say 118 to 1001 or any random values. We were pulling our heads about how this is happening? It looked interesting to dig it and we found the solution.
Microsoft has changed the way they deal with identical values in SQL Server 2012 and as a result of this you can see identity gaps between your records after rebooting your SQL server instance or your server machine. There might be some other reasons for this id gaps, it may be due to automatic server restart after installing an update.
The same bug is reported to Microsoft:
Failover or Restart Results in Reseed of Identity (SQL Server 2012 - Enterprise Edition - BugID: 739013)
https://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity
Failover or Restart Results in Reseed of Identity (SQL Server 2012 - Enterprise Edition - BugID: 739013)
https://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity
To avoid generating random identity values Microsoft added sequences in SQL Server 2012, we can change the way identity keys are generated! Otherwise, Identity jump is perfectly normal in SQL Server 2012.
No comments:
Post a Comment