Identity value jumps when SQL Server restarts
SQL Server 2012
When you restart SQL Server instance then the value of table's Identity column jumps 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.
You can stop this jump by two methods: Using Sequences Or Registering -t272 parameter
For this, you need to remove Identity column from tables. Then create a sequence without cache feature and insert number from that sequence. For that, you have to write the sequence as given below:
CREATE SEQUENCE Id_Sequence
START WITH 1
INCREMENT BY 1
insert into MyTestTable values(NEXT VALUE FOR Id_Sequence, 'Mr.Tom');
insert into MyTestTable values(NEXT VALUE FOR Id_Sequence, 'Mr.Jackson');
- Register -t272 to SQL Server Startup Parameter
To register, you need to open SQL Server configuration manager from your server. Select SQL Server 2012 instance, press right click and select Properties menu. You will find a tabbed dialog window. You select start up parameters tab from there and register -t272(as shown in the below screenshot). Then restart SQL Server 2012 instance again and see the difference.
If too many tables contain identity column to your database and all contain existing values, then it is better to go for this solution. Because it is a very simple solution and its scope is server wise. This means if you add SQL Server 2012 parameter -t272 there, then it will affect all your databases.
To create sequence, you can refer to this link: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql