What causes jump in identity column after restart?
The SQL Product team makes a continuous change in the product which sometimes causes unpredictable behaviors in the SQL Server engine. One of such change was done in SQL Server 2012 which caused the behavior which I just said in the subject line Identity Column. Here are steps to simulate the behavior.
What does SQL Server 2012 auto identity column value jump?
Now you see that after restarting the SQL Server 2012 instance, then identity value starts with 1002. It means it jumped 1000. Previously, I said that we also see if the data type of that identity column is bigint, then it will jump 10000.
How to avoid identity Jump issue in SQL Server?
This SEQUENCE solution resolves your identity jump issue with the identity columns in the SQL Server. However, you can approach this when the user is designing the database for the new product.
What is the jump value in SQL Server 2012?
From SQL Server 2012 version, when SQL Server instance is restarted, then table’s Identity value is jumped and the actual jumped value depends on identity column data type. If it is integer ( int) data type, then jump value is 1000 and if big integer ( bigint ), then jump value is 10000.
What happens to identity value when restarting SQL Server?
Whenever I restart SQL Server, the seed value for each identity column is increased by 1000 (for int identity columns it is 1000 and for bigint it is 10,000). For example, if the next int identity value for a table was 3, after restarting SQL Server it will be 1003. If I again restart SQL Server, it will be 2003 and so on.
What happens when I restart SQL Server 2012?
We just switched from SQL Server 2008 R2 to SQL Server 2012. I am facing a problem with identity columns: Whenever I restart SQL Server, the seed value for each identity column is increased by 1000 (for int identity columns it is 1000 and for bigint it is 10,000).