From SQL server 2012 onward, Auto incrementing column value jumps by 1000 or 10000 (depending on data type) on server restart.
This happens because SQL Server defines a cache size of 1000 or 10000 for IDENTITY and this cache is lost on server restart.
This can lead to loss in continuity if an auto incrementing column is used for any specific purpose like maintaining order no. etc.
To circumvent this, either use 'Sequence' feature of SQL Server or register 't272' as sql server startup parameter (which is server level change).
Refer to following links for details -
http://www.codeproject.com/Tips/668042/SQL-Server-2012-Auto-Identity-Column-Value-Jump-Is
http://stackoverflow.com/questions/14146148/identity-increment-is-jumping-in-sql-server-database
This happens because SQL Server defines a cache size of 1000 or 10000 for IDENTITY and this cache is lost on server restart.
This can lead to loss in continuity if an auto incrementing column is used for any specific purpose like maintaining order no. etc.
To circumvent this, either use 'Sequence' feature of SQL Server or register 't272' as sql server startup parameter (which is server level change).
Refer to following links for details -
http://www.codeproject.com/Tips/668042/SQL-Server-2012-Auto-Identity-Column-Value-Jump-Is
http://stackoverflow.com/questions/14146148/identity-increment-is-jumping-in-sql-server-database
No comments:
Post a Comment