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)


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

    No. It should become a value > 5.

    If you need consecutive numbers, you should not use IDENTITY. The point with IDENTITY and sequences is that they are non-blocking. That is, one process can get a number although another process that just took a number has not yet committed. This also means that there can be gaps if there is a rollback. And in some cases there can be bigger gaps. Which is perfectly OK, and it is completely beyond me why Microsoft wasted time on this trace flag.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]