Summary: 

Identity value jumps when SQL Server restarts

Version:

SQL Server 2012

Issue: 

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.

Solution:

You can stop this jump by two methods: Using Sequences Or Registering -t272 parameter

  • Using Sequence

        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

        AS INT

        START WITH 1

        INCREMENT BY 1

        MINVALUE 0

        NO MAXVALUE

        NO CACHE

        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.

Sql

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.

Useful Links: 

To create sequence, you can refer to this link: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql