reuse an identity field value after deleting rows

[Sadia fatima]

Is it possible to reuse an identity field value after deleting rows in SQL Server 2005? Here is an example. Suppose I have a table with an Id field as a primary key (identity). If I add five rows, I will have these 5 Ids: 1, 2, 3, 4, 5. If I were to delete these rows, and then add five more, the new rows would have Ids: 6, 7, 8, 9, 10. Is it possible to let it start over at 1 again?

[BitBlt]

Short of dropping/recreating the table, the only way to reset the identity values is to issue a TRUNCATE statement. Then the identity value for identity column for that table will reset to the seed value specified at create time.

Keep in mind that TRUNCATE gets rid of ALL the rows. If you have a need to reuse SPECIFIC id's, you might consider researching the "SET IDENTITY_INSERT..." statement.

[adam_k]

@BitBlt: I totally agree, but I thought that you could also alter the table and have the identity seed from whatever you need it to.

@OP: Unless you clear the contents of the table I strongly suggest against reusing the same IDs - in fact I strongly suggest against it unless you are re-initiallizing your app/db. IDs have a reason for being unique.
If however this is the way you need to design this and this is something that will occur again and again perhaps you need to abandon the IDENTITY and create a function that will seed for you.

A function can be as simple as select max(id) + 1 from table and when you delete a chunk of IDs then alter it to select max(id) + 1 from table where id between 2 and 5 or whatever, or it can be as complex as you can imagine it to be (if you have IDs missing that don't form a solid block then you can use an outer join against a continuous set of numbers and fetch the min(id) where the joined table has null values).

Then when inserting you use the function as

insert into my table(id, field1) 
values 
(my_seed_function(),'abc')

[cutepinkbunnies]

I agree with the opinion of others here, Identities are typically created to be retained...regardless of logical sense. Most database devs that use identity columns use flags instead of removing data from the database. It makes life easier because you don't have to worry about deletes and updates...restoring from backup, etc.

Keep in mind these are just generalized comments, each situation is different and requires balance. There is a trade-off to nearly any database activity, you just need to find a proper solution for your environment and the specifics of your data.