Reuse identity value after deleting rows

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.

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.

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.

identity fields do not reuse old values by default. You can reseed them with dbcc checkident, but this isn't suggested as you will get key violations if you reseed below a value that still exists in the table. In general, you shouldn't care what the PK values are. The fact that they're not contiguous doesn't hurt anything.