SQL Server ID Jump 1000 issue fixed in SQL Server 2019 ?

Tariq Sattarkhan Pathan 26 Reputation points
2021-08-17T14:26:54.31+00:00

hi,

In SQL Server 2012 - MS had introduced 'Identity Cache'. This feature had a bug of auto-incrementing ID column by '1000'. For example, if ID columns are 1, 2 and when an ID Jump happens the next ID column is 1003, instead of '3'. There are workarounds available to fix this issue. My question is as below pertaining to fix in the latest SQL version is available or not.

Question**: Is the ID Jump issue fixed in the latest SQL Server 2019 version? OR It is still persisting in SQL Server 2019 ?

Appreciate your inputs.

-Tariq

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,743 questions
Accepted answer
  1. Tom Phillips 17,716 Reputation points
    2021-08-17T15:30:01.517+00:00

    It RARELY happens. It can happen, but only happens if SQL Server is not shutdown properly. See https://blog.sqlauthority.com/2018/01/24/sql-server-identity-jumping-1000-identity_cache/

    IDENTITY fields are unique, not sequential. If you require sequential numbers, you need to use ROW_NUMBER.

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Dan Guzman 9,211 Reputation points
    2021-08-17T20:58:06.087+00:00

    As documented:

    SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application should use its own mechanism to generate key values. Using a sequence generator with the NOCACHE option can limit the gaps to transactions that are never committed.

    0 comments
  2. Erland Sommarskog 101.1K Reputation points MVP
    2021-08-17T21:27:37.28+00:00

    This feature had a bug of auto-incrementing ID column by '1000'.

    There is no such bug. Yes, the behaviour is there, but that's a not a bug. Rather, it is the whole point with the cache: by only writing to disk for every 1000 IDENTITY value generated, SQL Server can sustain a much higher insertion rate.

    If this is an issue for you, the bug is that you are using a mechanism which is designed to produce IDs without any meaning and where gaps are perfectly acceptable.

    There is however, a database-scoped configuration to turn of the cache: 

    ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF.
``´

It is also known as a go-slower button.
    0 comments
  3. AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor
    2021-08-18T02:46:07.51+00:00

    Hi TariqSattarkhanPathan-8841,

    Welcome to Microsoft Q&A.
    In addition, in SQL Server 2016 and earlier, we can use SEQUENCE object with NOCACHE option. Another option which we have is to use trace flag 272 by adding -T272 as SQL Server Startup Parameter in the configuration manager. Please refer to this article which might help.
    In SQL Server 2017 and 2019, we can SET IDENTITY_CACHE to OFF as Erland mentioned to avoid this issue.

    Best Regards,
    Amelia

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.