Results 1 to 5 of 5

Thread: Identity Column

  1. #1
    Join Date
    Dec 2002

    Unanswered: Identity Column

    Hi all,
    I am using a identity column in my table. When any rows are deleted in the identity column. The next value is not assigned to the previously deleted number. ie In one column iam generating sequence of number from 1 to 10. for example, the current row is 5. Now iam deleting the row 5. Next number should come only 5. Instead of that, the next value generated is 6. Can u suggest me any idea on this. Of couse, it is the default property how to over come this. Pls help me.


  2. #2
    Join Date
    Jul 2002
    Hi there
    AFAIK identities (auto assigned) are unique - that means used just once.

    I had the same usage for a identity-row, i've simply used non auto-assignment and a procedure wich searches for the lowest free key and assigns it...


  3. #3
    Join Date
    Feb 2002
    This is the nature of the IDENTITY property. The following is an excerpt from BOL:

    "If an identity column exists for a table with frequent deletions, gaps can occur between identity values. If this is a concern, do not use the IDENTITY property. However, to ensure that no gaps have been created or to fill an existing gap, evaluate the existing identity values before explicitly entering one with SET IDENTITY_INSERT ON."

    So basically if you continue to use the IDENTITY property and wish to attempt to fill the gaps you will have to use the SET IDENTITY_INSERT ON statement.

    coroner already mentioned an alternative method if this is a problem. Others just ignore the gaps. Or you can use the method mentioned above.

  4. #4
    Join Date
    Dec 2002


    Thank u rnealejr & coroner. It was so useful to me.
    Thank u so much.

  5. #5
    Join Date
    Dec 2002
    I'm wondering where SQL Server saves this information about what the "next" generated identity number should be?

    i.e How does it know it's been used and deleted.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts