CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008identity
Jeremy
Jeremy

Reputation: 3451

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.

Upvotes: 19

Views: 43300

Answers (3)

gbn
gbn

Reputation: 432431

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.

Upvotes: 11

OMG Ponies
OMG Ponies

Reputation: 332661

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.

Upvotes: 25

Donnie
Donnie

Reputation: 46933

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.

Upvotes: 7

Related Questions