Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Regarding sql server 2012 identity number gap problem after restarting sql server 2012 RE: Regarding sql server 2012 identity number gap problem after restarting sql server 2012

  • pksutha (7/22/2013)


    Hi friends,

    I've a problem in sql server 2012, like i've one table, table name is "tblA".

    In that Identity field Name is ID bigint not null identity[1,1].

    Now i'm making few records entry like from 1 to 5.

    Then i've close the sql server 2012. Then i've restarting the system.

    Again i'm making another record entry. Here it should come ID value is 6.

    But it has come like

    10001.

    Then again u make entry its coming correct 10002, 10003,10004 etc...

    But i need when u restart the system it should continue with existing example

    ID value 5+1=6, should start from 6 but its coming 10001...

    Plz help why its coming like this.

    The IDENTITY property doesn't give any garantuee there will be no gaps.

    SQL Server 2012 caches some of the next identities that might be inserted, in order to speed up the performance. If you restart the server, this cache will be lost.

    Maybe you can try to take a look at sequences, if avoiding gaps is important.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP