problem with auto increment jump 1000

Jose Arturo Elizondo 1 Reputation point

Hello, I see a problem with auto increment jump 1000.

I add this on MSSQL -T272 but not working.

I use the version 13.0.4 and SSMS is 2019

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,584 questions
{count} votes

7 answers

Sort by: Most helpful
  1. Viorel 111.5K Reputation points

    Did you consider an alternative that does not require major changes: setting IDENTITY_CACHE to OFF according to various suggestions: I.e.:


    0 comments No comments

  2. Erland Sommarskog 100.7K Reputation points MVP

    Why would that be problem? If it is a problem, that is, you need consecutive numbers, you should not use the IDENTITY property at all. IDENTITY is designed for when you need to number something but you don't care about the numbers being consecutive. IDENTITY is designed to produce gaps to permits inserts with high concurrency, so that one process can insert a new row without having to wait for the previous guy to commit. Which means that if this previous guy rolls back, there will be a gap.

    Also, to increase speed further, SQL Server does not write to disk every time an IDENTITY value is taken, but only for every 1000 rows (for an int column). This means that you can get larger gaps if SQL Server crashes and restarted.

    But as I said, if you have used IDENTITY for the right thing, this is not a problem. If you are using for the wrong thing, well, you should reconsider your redesign.

    All that said, you can do


    But beware that this is a go-slower button.

    0 comments No comments

  3. Jose Arturo Elizondo 1 Reputation point

    I running the script and show me this error

    Msg 102, Level 15, State 1, Line 5
    Incorrect syntax near 'IDENTITY_CACHE'.

    Completion time: 2021-07-12T16:34:26.1927991-05:00

  4. Cris Zhan-MSFT 6,601 Reputation points


    You may already know that this is a change introduced since SQL Server 2012, the identity caching is used to improve INSERT performance on tables with identity columns.. If this is really a problem for you, but because your SQL Server version is 2016 (cannot set IDENTITY_CACHE = OFF), You may need to use sequence or add -t272 to SQL Server startup parameters.

    Can you see the T272 trace flag in the startup parameters in SQL Server error log or registry?

    0 comments No comments

  5. Jose Arturo Elizondo 1 Reputation point

    Hello, Thanks all for the request. About your question Criszhan. I add the -T272 3 days ago, but is not working.