I noticed that values of a column related to newly inserted records which is an identity property enabled column in one of the tables, suddenly started from 1000+. The last values before this sudden jump were 27, 28, 29 but suddenly it started from 1000+ values. How did it happen without resetting the seed or increment of the identity property?
This is not a bug or something related to SQL Server 2016. It a result of a new implementation related to SQL Server 2012. As per my readings, since SQL Server 2012, it caches 1000 values for an identity property if the data type is int and it caches 10,000 values for big int and numeric. If an unexpected restart happens (in my case, I believe that an update caused the restart), there is a possibility to lose the cache, hence it starts from next available value. This is normal and you do not need to worry if you experience it.
Enabling Trace flag 272, you can get the old behavior or you can use Sequence instead. Read this thread for understanding these two options: http://stackoverflow.com/questions/17587094/identity-column-value-suddenly-jumps-to-1001-in-sql-server
No comments:
Post a Comment