locked
Identity column Auto increment not work properly RRS feed

  • Question

  • I have noted since then that many tables that have auto increment primary key bigint field increases by 1 like it should, then for some reason it jumps up by 1000 or even 10000?  i have seed adn auto increment set to 1 but doesnt effect it.  Is there anything that could be causing the next value to jump that much?
    Friday, December 8, 2017 12:54 PM

All replies

  • it jumps up by 1000 or even 10000? 

    Hello,

    It's by design, for performance reason SQL Server caches 1,000 numbers and on SQL Server restart the cache is gone.

    And the behaviour is well documented see CREATE TABLE (Transact-SQL) IDENTITY (Property) => Remarks: "Consecutive values after server restart or other failures "


    Olaf Helper

    [ Blog] [ Xing] [ MVP]



    • Edited by Olaf HelperMVP Friday, December 8, 2017 1:14 PM
    • Proposed as answer by disssss Wednesday, December 13, 2017 8:18 PM
    Friday, December 8, 2017 1:13 PM
  • IDENTITY fields are NOT sequential, they are unique.  There are many reasons for missing numbers.


    Friday, December 8, 2017 1:14 PM
  • I have noted since then that many tables that have auto increment primary key bigint field increases by 1 like it should, then for some reason it jumps up by 1000 or even 10000?  i have seed adn auto increment set to 1 but doesnt effect it.  Is there anything that could be causing the next value to jump that much?

    Its due to caching of values and is by design

    You can avoid it by setting trace flag in earlier versions of SQLServer

    In SQL 2017 you have a SCOPED CONFIGURATION setting for this

    https://visakhm.blogspot.in/2017/06/sql-2017-tips-avoiding-gaps-in-identity.html


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, December 8, 2017 1:29 PM
  • Thanks Olaf, we faced this issue several times. 

    @Datta - If you are using SQL Server 2017 or Azure then you can disable identity cache, that will help to avoid this problem


    Mark as ANSWER if I helped you today :-) www.sql-articles.com

    Friday, December 8, 2017 1:30 PM
  • @Datta - If you are using SQL Server 2017 or Azure then you can disable identity cache <https://social.technet.microsoft.com/wiki/contents/articles/40666.sql-server-2017-identity-cache-feature.aspx>, that will help to avoid this problem

    But why would you use a go-slower button?

    If you want contiguous numbers, you cannot use IDENTITY. IDENTITY, as well as sequences, are designed to produce gaps. This applies also if you apply the go-slower button. If your INSERT fails or rollback the number is till consumed and there will be a gap. If it hadn't been like that,  processes that inserts rows simultaneously would be serialised, which means that  in high-concurrency the system would be severly throttled.

    Far from all systems have that challenge but then you need to use IDENTITY. You can easily roll your own with MAX if you want contiguous numbers.

    If you need contiguous numbers and high concurrency you have a big challenge!

    • Proposed as answer by Tom Phillips Friday, December 8, 2017 5:50 PM
    Friday, December 8, 2017 5:40 PM
  • IDENTITY fields are NOT sequential, they are unique. 

    Not even that. The IDENTITY property is not a guarantee for uniqueness. The numbers are generated in incremental order, but you can reset the current value, and thus generate the same value a second time.

    Friday, December 8, 2017 5:41 PM
  • With all that has been said. 

    You didn't tell us the version of SQL Server you are using.  There was a SQL Server bug which would increment the value significantly every restart of the service.  This has been fixed. Please make sure you have the latest SP installed.

    Friday, December 8, 2017 5:52 PM
  • You need to get a good book on basic RDBMS and SQL. The IDENTITY is a proprietary construct in the Sybase family. The "auto increment" is a term borrowed from several other languages, I think ACCESS uses it. IDENTITY is not a column; is a table property, based on a sequential storage used in the original UNIX file system over 25 years ago by Sybase. 

    By definition, it is not possible for to be a key. It's a count of physical insertion attempts on a physical disk a particular piece of hardware, which replaces the original Sybase/UNIX record number on Mac tapes. You might also want to actually read the SQL standards and learn what the term "field" means in SQL; fields are absolutely nothing whatsoeverlike columns. 

    However, ANSI/ISO Standard SQL does have a construct you can use for something like this. It is the CREATE SEQUENCE statement. Here's an article on how to use it:

    www.red-gate.com/simple-talk/sql/learn-sql-server/sql-server-sequence-basics/

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, December 8, 2017 6:02 PM
  • You didn't tell us the version of SQL Server you are using.  There was a SQL Server bug which would increment the value significantly every restart of the service.  This has been fixed. Please make sure you have the latest SP installed.

    That was not a bug. As others have pointed out, that was by design. By caching IDENTITY values and only writing to disk for every thousand values, they win quite a bit of performance in high-frequency scenarios. As a side effect it can lead to huge gaps. Which you should not care about.

    • Proposed as answer by Visakh16MVP Saturday, December 9, 2017 5:29 PM
    • Unproposed as answer by Visakh16MVP Saturday, December 9, 2017 5:29 PM
    Friday, December 8, 2017 11:02 PM
  • Currently i am using this version Microsoft SQL Server 2014 - 12.0.4213.0 (X64)

    Saturday, December 9, 2017 5:30 AM
  • There was a "change" early in the release of identity caching.  Initially, the identity would jump every restart of the SQL Server service.  MS "changed" that functionality in a patch, even without the trace flag, not always jump due to the identity cache.  There are still cases where it does jump, but it happens less often. 

    Wednesday, December 13, 2017 1:39 PM
  • Microsoft saying it's a FEATURE . . . while everyone else saying it's a BUG.

    If we are not interested in this "feature":

    Earlier versions than SQL Server 2017:

    * Register -t272 to SQL Server Startup Parameter

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

    SQL Server 2017:

    It's a "feature" BUG from SQL Server Cache. They did not fix it yet, but at least they gave us the option to disable the cache and it's BUG in SQL Server 2017. And now you can have legacy database and new databases at the same server.

    IDENTITY_CACHE = { ON | OFF }

    Enables or disables identity cache at the database level. The default is ON. Identity caching is used to improve INSERT performance on tables with Identity columns. To avoid gaps in the values of the Identity column in cases where the server restarts unexpectedly or fails over to a secondary server, disable the IDENTITY_CACHE option. This option is similar to the existing SQL Server Trace Flag 272, except that it can be set at the database level rather than only at the server level.

    (...)

    G. Set IDENTITY_CACHE

    This example disables the identity cache.

    ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF ;

    Friday, February 2, 2018 11:31 PM