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.

HenryHi 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...

greetz
c0r0n3r|||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.|||Thank u rnealejr & coroner. It was so useful to me.
Thank u so much.|||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.

thanks
js