SQL Server Tutorial

SQL Server is a relational database management system (RDBMS) from Microsoft that’s designed for the enterprise environment. SQL Server runs on T-SQL (Transact -SQL), a set of programming extensions from Sybase and Microsoft that add several features to standard SQL, including transaction control, exception and error handling, row processing, and declared variables.

SQL Server 2012 Auto Identity Column Value Jump Issue

In this article I will tell you how to Auto Identity Column Value Jump Issue in SQL Server 2012. Microsoft changed the Identity behavior to jump in increment of 1000(if INT) or 10000(if BIGINT) whenever the SQL Server 2012 restart, Service Restart or Failover.

You need to setup SQL Server 2012 and create a test database. Then create a table with auto identity column:

create table MyTestTable(Id int Identity(1,1), Name varchar(255));

Now insert 2 rows there:

insert into MyTestTable(Name) values ('Mr.Tom');
insert into MyTestTable(Name) values ('Mr.Jackson');

You see the result:

SELECT Id, Name FROM MyTestTable;

The result is as expected. Now just restart your SQL Server service. There are various ways in which you can do it. We did it from SQL Server management studio.

Now, insert another 2 rows to the same table again:

insert into MyTestTable(Name) values ('Mr.Tom2');
insert into MyTestTable(Name) values ('Mr.Jackson2');

Now see the result:

SELECT Id, Name FROM MyTestTable;

Now you see that after restarting the SQL Server 2012 instance, then identity value starts with 1002. It means it jumped 1000.

Is it really a bug?

Microsoft declares it is a feature rather than a bug and in many scenarios it would be helpful. But in our case, it would not be acceptable because that number is shown to the client and the client will be surprised to see that new number after jump and the new number depends on how many times SQL Server is restarted. If it is not visible to the client, then it might be acceptable so that the number is used internally.

Solutions

If we are not interested in this so called feature, then we can do two things to stop that jump.

Using Sequence
Register -t272 to SQL Server Startup Parameter

Using Sequence

First, we need to remove Identity column from tables. Then create a sequence without cache feature and insert number from that sequence. The following is the code sample:

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

Open SQLServer configuration manager from your server. Select SQL Server 2012 instance there right client and select Properties menu. You will find a tabbed dialog window. You select start up parameters tab from there and register -t272. Then restart SQL Server 2012 instance again and see the difference:

Summary

If too many tables contain identity column to your database and all contain existing values, then it is better to go for solution 2. 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 there. If you want to create a new database and you need auto generated number field, then you can use solution 1, that means use sequence value to a column instead of auto Identity value. There are so many articles you can find online about when you will use auto identity column when using sequence and advantages/disadvantages of each other.

Our Recommended SQL 2008/2012/2014/2016 Hosting

ASPHostPortal.com provides its customers with Plesk Panel, one of the most popular and stable control panels for Windows hosting, as free. You could also see the latest .NET framework, a crazy amount of functionality as well as Large disk space, bandwidth, MSSQL databases and more. All those give people the convenience to build up a powerful site in Windows server. ASPHostPortal.com offers SQL 2016 hosting starts from $5. ASPHostPortal also guarantees 30 days money back and guarantee 99.9% uptime. If you need a reliable affordable SQL 2014 Hosting, ASPHostPortal should be your best choice.