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