Reuse identity value after deleting rows

Question

Is it possible to reuse an identity field value after deleting rows in SQL Server 2008 Express? 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?

Do I have to delete data from another table in order to accomplish this? Thanks for your help.

Answer 1

You can use the following to set the IDENTITY value:

DBCC CHECKIDENT (orders, RESEED, 999)

That means you'll have to run the statement based on every DELETE. That should start to highlight why this is a bad idea...

The database doesn't care about sequential values - that's for presentation only.

Answer 2

If you want to reset the identity after deleting all rows then do one of these

--instead of delete, resets identity value
TRUNCATE TABLE orders

--or if TRUNCATE fails because of FKs, use this after DELETE
DBCC CHECKIDENT (orders, RESEED, 1)

Otherwise, the internal value should not matter whether gaps or not.