Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Weird ID incrementation

Answered Weird ID incrementation

  • Saturday, November 17, 2012 10:10 AM
     
     
    Sign In to Vote
    0

    Hello!

    I've got a "Computers" table in my db with the ComputerID column set as PK, ID, not null.

    Up till now when I was adding a row to the table the value of "ComputerID" column was being incremented by 1, for instance: 230, 231, 232...248. When I added one more row this morning this value was incremented by 1000 (the next row has ComputerID = 2048), the second added row has the value = 2049.

    Tell me please whether it's normal behavour of auto-increment or not?

    Thank you in advance,

    Michael

     

All Replies

  • Saturday, November 17, 2012 5:31 PM
     
     
    Sign In to Vote
    0

    is an identity field? if it is, is normal when there were failed attempts at insertion.

    if you execute an insert, and this failure, as it will increase the identity

    You may have tried to do a bulk insert that failed or had a rollback

     
  • Saturday, November 17, 2012 5:40 PM
     
     Answered
    Sign In to Vote
    0

    Hallo Michael,

    this is a well known bug in SQL 2012.

    https://connect.microsoft.com/SQLServer/feedback/details/739013/alwayson-failover-results-in-reseed-of-identity#details

    I hope it's fixed with the latest update (SP1)
    http://www.microsoft.com/en-us/download/details.aspx?id=35575

    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)

    • Marked As Answer by MF47 Monday, November 19, 2012 8:07 AM
    •  
     
  • Monday, November 19, 2012 8:07 AM
     
     
    Sign In to Vote
    0

    Thank you all for your replies!

    https://connect.microsoft.com/SQLServer/feedback/details/739013/alwayson-failover-results-in-reseed-of-identity#details

    "This also occurs if you restart the server. " - I really did restart my server and tried to add one more row to a table (the first row during that day!) and this identity field has changed its value to >1000!

    I don't understand how MS can sell a product with such a bug...???

    People say they're downgrading to SQL2008R2:

    "Just I'm finished a hard work to downgree to 2008 R2....", "I can't believe SP1 didn't address this... big problem!", "but now im working hard to downgree to sql 2008 R2."

    No comments...

    Best regards,

    Michael


    • Edited by MF47 Monday, November 19, 2012 8:08 AM
    •  
     
  • Monday, November 19, 2012 1:27 PM
    Moderator
     
     
    Sign In to Vote
    1
    Although this is not the intended implementation, IDENTITY fields are guaranteed to be unique, not seqential.  Although generally they are, gaps can occur for many reasons.  If you are expecting them to be sequential without gaps, you need to rethink your implementation.
     
  • Tuesday, November 20, 2012 8:45 AM
     
     
    Sign In to Vote
    0
    Tom, thank you!