0

In my project there is a table in which I add testing data for smoke tests, proceed with some tests, then delete the data I added. The Primary Key on that table is and not null int and has an auto incrementing identity, so after the deletion the highest number is left as it is.

My question is: is there a way to reorganize this number so it can return to the value before my smoke test inserts?

I tried to do Rebuild and Reorganize on the primary key hoping for a miracle but it didn't work.

Remark: I can't drop the Id column and create it again, as the existing data must have the same Ids after my tests.

  • Is there a functional reason you need to reset the ID? Unless you are inserting a ton of rows (in which case you maybe need to re-think this approach...) I would wager that having higher IDs / some gaps in the IDs isn't an actual problem. – LowlyDBA Jun 30 '17 at 14:48
  • Well, there are quite a few rows. The main reason was the team lead's point that after the smoke tests we should leave the place clean. – Maurice Klimek Jun 30 '17 at 21:17
  • 1
    Unless you're in danger of causing your table to max out the values for an INT/BIGINT by this practice, I'd not worry too much about it as the more unnecessary cleanup you do the more likely you are to adversely impact the system and open yourself up to potential bugs in your smoke screen cleanup process. – LowlyDBA Jul 1 '17 at 15:14
4

Both options delete all records from your table and restarts IDENTITY values.

TRUNCATE TABLE

TRUNCATE TABLE <schema>.<table_name>;

Removes all rows from a table or specified partitions of a table, without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

or

DBCC CHECKIDENT

DELETE FROM <schema>.<table_name>;
DBCC CHECKIDENT ('<schema>.<table_name>', RESEED, 0);

Checks the current identity value for the specified table in SQL Server 2017 and, if it is needed, changes the identity value. You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.