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 (identi

Solution 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.

Solution 2:

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

--instead of delete, resets identity valueTRUNCATETABLE 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.

Solution 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.