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.

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

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

回答3:

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.