Menu
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.
610
No, SQL Server won't reuse IDs.
To change the original seed value and reseed any existing rows, drop the identity column and recreate it specifying the new seed value. When the table contains data, the identity numbers are added to the existing rows with the specified seed and increment values.
If you know the value of the primary key for the row that you want to delete, you can specify the value using the VIA clause. If there is more than one primary key column, the values must be specified in order and separated by commas (,). String values must be enclosed in single quotes (').
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.
79
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.
45
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
