locked
Identity column not getting sequential values RRS feed

  • Question

  • hi, I created an identity column on a table. There is only one process that always inserts into this table - delete not allowed on this table. I noticed that the identity values gets skipped for some records . For E.g

     

    identity col1 col2 col 3
    1 XXXXXXXXXX 3445467B-3038-3742-362D-313839452D34
    2 XXXXXXXXXX 3445467B-3038-3742-362D-313839452D34
    3 YYYYYYYYYYY 3445467B-3038-3742-362D-313839452D34
    4 YYYYYYYYYYY 3445467B-3038-3742-362D-313839452D34
    5 YYYYYYYYYYY 3445467B-3038-3742-362D-313839452D34
    6 YYYYYYYYYYY 3445467B-3038-3742-362D-313839452D34
    7 YYYYYYYYYYY 3445467B-3038-3742-362D-313839452D34
    8 YYYYYYYYYYY 4434367B-4544-3835-462D-373442312D34
    9 YYYYYYYYYYY 3445467B-3038-3742-362D-313839452D34
    12 YYYYYYYYYYY 3445467B-3038-3742-362D-313839452D34
    15 YYYYYYYYYYY 3445467B-3038-3742-362D-313839452D34
    17 YYYYYYYYYYY 3445467B-3038-3742-362D-313839452D34
    21 YYYYYYYYYYY 3445467B-3038-3742-362D-313839452D34
    24 YYYYYYYYYYY 3445467B-3038-3742-362D-313839452D34
    27 YYYYYYYYYYY 3445467B-3038-3742-362D-313839452D34
    29 YYYYYYYYYYY 3445467B-3038-3742-362D-313839452D34
    32 YYYYYYYYYYY 3445467B-3038-3742-362D-313839452D34
    35 YYYYYYYYYYY 3445467B-3038-3742-362D-313839452D34
    39 YYYYYYYYYYY 3445467B-3038-3742-362D-313839452D34
    42 YYYYYYYYYYY 4434367B-4544-3835-462D-373442312D34
    43 YYYYYYYYYYY 3445467B-3038-3742-362D-313839452D34

     

    I have set the IdentityIncrement=1. Any reason why ?. Appreciate your inputs. Thanks

    Thursday, November 13, 2008 12:05 AM

Answers

  • Identity values are not guaranteed to be unique. Even if you do not delete rows, you can end up with missing identity values. If a transaction inserts a new record to the table and ROLLS BACK, you can end up with a missing identity value.

     

    Thursday, November 13, 2008 12:58 AM

All replies

  • Identity values are not guaranteed to be unique. Even if you do not delete rows, you can end up with missing identity values. If a transaction inserts a new record to the table and ROLLS BACK, you can end up with a missing identity value.

     

    Thursday, November 13, 2008 12:58 AM
  •  

    "Identity values are not guaranteed to be unique."

     

    Just to double check do you mean sequential? or is there a chance that the same number can appear more then once?

     

    Because it just keeps incrementing, I wouldn't think it could be possible.

     

     

     

    Thursday, November 13, 2008 8:46 PM
  • He meant sequential.

    But where ever you have an identity field, there's normally a good cause for a unique index too

     

    Friday, November 14, 2008 8:31 AM
    Answerer
  • Yes, Identity values need not to be unique.

     

    If the process inserts and then rollbacks, then identity values are escaped

     

    An explicit values for identity column can be inserted by setting identity insert to on.

    for example:

    Code Snippet
     set identity_insert <table_name> on

     

     

     

     

     

     

    Friday, November 14, 2008 8:42 AM
  • identity insert - another reason to enforce uniqueness using a constraint.

    Friday, November 14, 2008 8:59 AM
    Answerer