SQL SERVER – 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. I did it on SQL 2012.

--Create a test database. 
CREATE DATABASE TestIdentityDB

--Create a test table in above database with auto identity column 
USE TestIdentityDB
GO
CREATE TABLE MyTable (
	 id INT identity
	,name VARCHAR(10)
	)
GO

--Insert a row in the table
INSERT INTO MyTable (name)
VALUES ('Pinal')

SELECT *
FROM MyTable

--Restart SQL Server service and once completed, insert another record.
INSERT INTO MyTable (name)
VALUES ('Dave')

--Let's select data from table now.
SELECT * FROM MyTable

SQL SERVER - Jump in Identity Column After Restart identity-01

You would expect the value to be in order, but as you can see there is a jump in the identity value.

I searched on the Web and as per Microsoft documentation, identity is not guaranteed to give sequential value. In SQL Server 2012 the implementation of the identity property has been changed to accommodate investments into other features. This is what they have mentioned in a connect item, “In previous versions of SQL Server the tracking of identity generation relied on transaction log records for each identity value generated. In SQL Server 2012 we generate identity values in batches and log only the max value of the batch. This reduces the amount and frequency of information written to the transaction log improving insert scalability.”

Above means that this is expected behavior because identity values are cached and destroyed on restart/failover.

WORKAROUND/SOLUTION

As per Microsoft documentation we should use SEQUENCE object with NOCACHE option. You can refer below blog for more details.

Another option which we have is to use trace flag 272 by adding -T272 as SQL Server Startup Parameter in the configuration manager.

Here are the reference articles.

https://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity

http://msdn.microsoft.com/en-us/library/ff878091.aspx

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Error Messages, SQL Identity, SQL Scripts, SQL Server, SQL Server Configuration, TraceFlags
Previous Post
SQL SERVER – AlwaysOn Listener Error – The WSFC Cluster Could Not Bring the Network Name Resource With DNS Name ‘DNS name’ Online
Next Post
SQL SERVER – Could Not Use View or Function ‘msdb.dbo.sysdac_instances’ Because of Binding Errors

Related Posts

15 Comments. Leave new

  • Georgy Boyadjiev
    March 24, 2017 2:18 pm

    Hi Pinal,
    I decided to reproduce “identity column”-problem on my SQL 2012 instance and I experience no problem. I’m definitely not using trace flag 272 or something else (SEQUENCE , nocache, …).
    What could be the reason for the normal behavior ? I mean after restart of the the service ID of the next inserted record was in sequence (first record ID = 1, second inserted record ID = 2)
    Does anyone else experience a normal behavior of the sample above ?

    Thanx in advance :-)

    Reply
    • Well, it was experienced by many not all. There might to be certain condition which your server is not meeting and I don’t know what it is.

      Reply
  • Doug Reynolds
    March 25, 2017 3:56 am

    I am not sure why this is an issue. Oracle does the same thing with sequences. This improves performance, especially in a RAC configuration. Sequences or identities should not be used to determine the order of record creation; they only provide a rough estimate of insert order.

    Reply
    • Well, one of the “myth” of identity in SQL is ever increasing number, which is not true. Its documented the same way which you mentioned for SQL Server also.

      Reply
  • Adriana Milcov
    March 27, 2017 2:38 pm

    It didn’t reproduce on my server either.
    It could be only on some versions? I’ve tested on SSMS version 11.0.2100.60

    Reply
  • Pinal, I am getting this using the Linux version on Ubuntu in a docker container. Pretty shocked by this behavior. A few years back I had a situation where order rows were physically deleted (where the order # was identity column) and the auditors went ballistic. I not only had to prove each deleted order row was a cancelled order but had to fix the existing problem and wait until the auditors signed off on the changes.

    Reply
  • philip koprowski jr
    November 30, 2017 9:18 pm

    it is just plain stupid to change something that Microsoft KNOWS everyone is using and is expecting to work as before. Things like this should be options that can be turned on if you want to improve performance. They should not be turned on by default, for obvious reasons.

    Reply
  • SQL 2014 have same issues. Is there side effects using -T272 at SQL startup parameter?

    Reply
  • Anil Kumar Dubey
    October 30, 2019 5:27 pm

    Is this problem only in sql server 2012 or in later versions also ?

    Reply
  • Hi Georgy,

    It depends on your environment and the load SQL server has. It accordingly optimize the logs and decides.

    Reply
  • It has nothing to do with the version.

    Reply
  • i tried this on a sql 2012 database and the identity just went up by 1 on the next insert, so no difference.
    I do know that if you rollback a transaction that has inserted a row then the identity won’t be rolled back. So it will jump in that situation.

    Reply
  • TARIQ SATTARKHAN PATHAN
    August 18, 2021 10:19 am

    Is the Identity Jump issue fixed in latest 2019 SQL Server or it is still persisting in 2019 ?

    While we know, how to fix it. Just need a clarity on whether fixed in 2019 or still there is a ID jump of 1000 in 2019 ?

    Thanks,
    Tariq

    Reply

Leave a Reply