Post reply

gaps in the identity column

  • February 13, 2014 at 7:00 am

    #288139

    The identity int column in my sql2012 db is not incrementing properly.

    I have a table which uses an int auto identity as a primary key it is sporadically skipping increments, for example:

    1, 2, 3, 4, 5, 1004, 1005

    This is happening on a random number of tables at very random times

    any suggestions on how to stop this

    Thanks

  • February 13, 2014 at 7:14 am

    #1688722

    Hi,

    There's a connect item about this somewhere (I'll try and find it). Basically, it's by design. There's a trace flag in the connect item I've linked to (just seen it)

  • February 13, 2014 at 7:19 am

    #1688724

    here:

    http://connect.microsoft.com/SQLServer/feedback/details/743300/identity-column-jumps-by-seed-value

    There's another connect item with a better explanation of why it is by design - I recollect it's to do with performance... If I find it, i'll post that link too

    Edit - new link https://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity

    Posted by Microsoft on 5/6/2013 at 3:53 PM

    Hello all,

    I am the dev owning the identity feature.

    To boost the preformance for high end machines, we introduce preallocation for identity value in 2012.

    And this feature can be disabled by using TF 272 (then you will get the behaviour from 2008R2).

    The identity properties are stored separately in metadata.

    If a value is used in identity and increment is called, then the new seed value will be set.

    No operation, including Rollback, Failover, ..... can change the seed value except DBCC reseed.

    Failover applies for the table object, but no the identity object.

    So for failover, you can call checkpoint before manual failover, but you may see gap for unplanned cases.

    If gap is a concern, then I suggest you to use TF 272.

    For control manager shutdown, we have a fix for next verion (with another TF). This fix will take care of most control manager shutdown cases.

    Thanks,

    Bryan

  • February 13, 2014 at 7:50 am

    #1688745

    Remember too that there is no guarantee that you won't have gaps in identity values for other reasons, such as transaction rollbacks. If you need no gaps, you need another solution (which is very complex to manage and deal with).

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • February 13, 2014 at 9:11 am

    #1688809

    TheSQLGuru (2/13/2014)

    such as transaction rollbacks.

    Beat me to it.

    If a transaction fails and rolls back, the identity is "lost". It isn't de-incremented as part of the roll back

  • February 17, 2014 at 4:08 am

    #1689438

    thnx everyone for your help...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply