Wednesday, July 13, 2016

SQL Server 2012 Auto Identity Column Value Jump bug!

I am working on a database using SQL Server 2012. There is a table named Order has an auto Identity Column named OrderId datatype is int .

I noticed that the sequence of the identity is just not right. It has jumped with 1000.
After research about this issue, I found that this is an issue with SQL Server 2012 version.

From SQL Server 2012 version, when SQL Server instance is restarted then its auto Identity column value is jumped based on identity column datatype.

If it is an integer data type, then jump value is 1000 and if  it is a big integer, then jump value is 10000.

So now how can we solve this issue?
We can work around by 2 ways:

1- Using Sequence

We will have to remove Identity column from the table.
Create a sequence without cache feature and insert number from that sequence.
Please find below code sample:
CREATE SEQUENCE OrderId_Sequence AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 0
NO MAXVALUE
NO CACHE
This is how you can use that Sequence:
INSERT INTO Order VALUES(NEXT VALUE FOR OrderId_Sequence, 'OrderName', 'OrderDescription');

2- Register -t272 to SQL Server Startup Parameter

Go to SQLServer configuration manager.
Select SQL Server 2012 instance then select Properties Menu.
You should find a tabbed dialog window.
Now you select start up parameters tab from there and register -t272.
Finally restart SQL Server 2012 instance.

Source: https://www.nilebits.com/blog/2014/02/sql-server-2012-auto-identity-column-value-jump-bug/