Post reply

identity gaps

  • May 30, 2012 at 3:47 am

    #269641

    In sybase we have identy_gap command to limit the identiy burn outs. What is equivalent comand for this in sql2008 R2?

  • May 30, 2012 at 4:01 am

    #1494534

    There is no equivalent in SQL Server 2008 R2. The cache size for IDENTITY is undocumented and not guaranteed. In SQL Server 2012, it is the CACHE option of CREATE SEQUENCE.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • May 30, 2012 at 4:08 am

    #1494537

    so how do we monitor/maintain identity jumps sql 2008 R2?

  • May 30, 2012 at 4:11 am

    #1494540

    shilpaprele (5/30/2012)

    so how do we monitor/maintain identity jumps sql 2008 R2?

    What do you mean exactly? Describe the scenario you are concerned about. There is nothing analogous to identity_gap, identity burning factor, or identity grab size in SQL Server 2008 R2. If you have a good reason for needing guarantees beyond those provided by IDENTITY, consider a Sequence Table.

    http://sqlblog.com/blogs/paul_white/archive/2010/10/19/sequence-tables.aspx

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • May 30, 2012 at 4:22 am

    #1494548

    in syabse we faced an issue that when server was resatrted before stopping the service there was jump in identity value. But server on its own can control this jump by identity_gap syntax.

    So if similar sutuation arises in sql 2008, if server can not control it then is there any other way to check and reseed the identity gap. ( automatically when we start the server)

  • May 30, 2012 at 4:34 am

    #1494555

    shilpaprele (5/30/2012)

    in syabse we faced an issue that when server was resatrted before stopping the service there was jump in identity value.

    This can happen with SQL Server too. Be aware that IDENTITY gaps can also occur during normal operation due to INSERTs rolling back, for whatever reason.

    So if similar sutuation arises in sql 2008, if server can not control it then is there any other way to check and reseed the identity gap. (automatically when we start the server)

    If it is important not to have any gaps, I would not use IDENTITY; consider a Sequence Table instead (see my previous post for a link).

    You could write a start-up procedure that reseeds IDENTITY if necessary, but that still would not prevent gaps due to roll backs.

    Automatic Execution of Stored Procedures

    sys.identity_columns

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • May 30, 2012 at 4:42 am

    #1494559

    thanks

  • May 31, 2012 at 7:54 am

    #1495224

    The single best way to "limit the identiy burn outs" is to stop seeding them at 1!! Always start identities at the LOWEST number available for the numeric type chosen. Outside of that, if you are like 99.7% of my clients and you did do 1,1 when you hit a limit you can do dbcc checkident and restart the number at the negative limit to pick up the other half of values you missed when you started with 1,1.

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

Viewing 8 posts - 1 through 7 (of 7 total)

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