News
Hey SQL Server experts! Experiencing ID value jumps?

From time to time we’ve received inquiries from HDMS users about PO Numbers, Vendor ID’s, or other identification values jumping by 1000 in their premise-based environment. Where did those missing values go? Was data lost? In every case, the 1000 value ID jump seemed too perfect to be a coincidence. Take a look at what one of our Data Analysts, Dean Nelson shared with us internally on the topic.

In SQL Mgmt Studio, 1000 is a default parameter received when a Select is done; and that was the clue that there was more to the story. Through community and research Dean shared with us that this is NOT unique to Universal’s development, in fact it’s something that has been disputed in the SQL Server 2012 was released.

In SQL Server 2012 Microsoft introduced “cached identity values” claiming blocks of 1,000 values for integers and 10,000 for numeric types. This “feature”, as described by Microsoft, is new to SQL SERVER 2012 and considered a bug by many in the SQL community.

Dean Nelson shares, “MS states this is not a bug and does not intend to provide a fix it. It will not be corrected in bug fixes or service packs.” Given Microsoft’s view of the issue, Dean agrees that this is something to be aware of when upgrading to SQL Server 2012 or 2016.

You may experience this jump in ID values if there was any work done in tables, especially loading or importing data where a failure occurred. Even if later the process was run successfully or corrected, it would create a gap. Dean provides us with an example:

You can get this block of missing numbers if you do something like

 

                INSERT INTO  TableXYZ (….)

                SELECT …. FROM TableABC

 

(where ….  Are the various fields)

 

But say that during this oneline execution, there is an error, like a conversion error or truncation, resulting in the insert failing, the IDENTITY field from  TableXYZ will be consumed and not given back.  Meaning that the next time you insert, the Identity will be at a higher number and not the sequential value that you may visually expect. This can also happen on a restart of the SQL Server to multiple table items at the same time.

Dean went on to share with our developers that there is a way to stop this by adding a flag in the startup Parameters of -T272, but it must be done at the server level making it hard to control for local hosts. Exploring other processes or procedures that would run on restart to reset the seed values to the max +1 on all identify fields was also an idea, but this would significantly slow the restart.

Other options include Version 2017’s option to stop this feature at the database level.

Dean Nelson continues to hold the line that this is a MS Bug that needs to be acknowledged as such and addressed. If you think you are experiencing this or care to read more, below are some of the sources Dean shared with us.

The community is talking here:

https://stackoverflow.com/questions/14146148/identity-increment-is-jumping-in-sql-server-database

https://www.codeproject.com/Tips/668042/SQL-Server-Auto-Identity-Column-Value-Jump-Is

https://www.sqlservercentral.com/Forums/Topic1651468-3412-1.aspx

https://github.com/ktaranov/sqlserver-kit/blob/master/SQL%20Server%20Trace%20Flag.md

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/ff878058%28v%3dsql.110%29

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/ms186775%28v%3dsql.110%29