problem with auto increment jump 1000

Jose Arturo Elizondo 1 Reputation point
2021-07-12T21:00:49.897+00:00

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,629 questions

7 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Viorel 111.7K Reputation points
    2021-07-12T21:26:39.433+00:00

    Did you consider an alternative that does not require major changes: setting IDENTITY_CACHE to OFF according to various suggestions: https://www.bing.com/search?q=t-sql+IDENTITY_CACHE? I.e.:

    ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF

    0 comments
  2. Erland Sommarskog 100.8K Reputation points MVP
    2021-07-12T21:30:38.157+00:00

    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 

    ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF

    But beware that this is a go-slower button.

    0 comments
  3. Jose Arturo Elizondo 1 Reputation point
    2021-07-12T21:35:57.223+00:00

    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
    2021-07-13T03:10:32.41+00:00

    Hi,

    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
  5. Jose Arturo Elizondo 1 Reputation point
    2021-07-13T04:39:47.913+00:00

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