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
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.
http://msdn.microsoft.com/en-us/library/ff878091.aspx
Reference: Pinal Dave (https://blog.sqlauthority.com)
15 Comments. Leave new
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 :-)
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.
is there any overhead/performance impact for this traceflag
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.
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.
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
May be..
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.
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.
SQL 2014 have same issues. Is there side effects using -T272 at SQL startup parameter?
Is this problem only in sql server 2012 or in later versions also ?
Hi Georgy,
It depends on your environment and the load SQL server has. It accordingly optimize the logs and decides.
It has nothing to do with the version.
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.
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