identity column

[yogesphu]

Hi

I am using SQL Server 2005.
I created a table and i defined an identity column. So whenever i enter any values to my table, the values in identity column keep on increasing like 1,2,3,4,5 on its own.
but when i delete, say, row no. 5 and insert a row again after deleting, the numbering in identity column will be like 1,2,3,4,6.
but i want it it to be 1,2,3,4,5 again. I hope i have made myself clear.

does anyone have the solution for this!!!!

Thanks!!!

[skhanal]

You can't reuse identity column values. If it is the last number you want to reuse after deleting it, then you may reseed the identity column with

DBCC CHECKIDENT (table1, RESEED, 5)

But if you have already used numbers after 5 then you will run into two rows with same values.

If you want to reuse the number then you should use your own sequence table.

[yogesphu]

thanks 4 d info buddy!!!