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.

Improve this question
3
  • 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 - John M
    Commented Jun 30, 2017 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
    Commented Jun 30, 2017 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 - John M
    Commented Jul 1, 2017 at 15:14

2 Answers 2

Reset to default
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.

Improve this answer
0
1

yes you can, just change the auto increment to non auto increment for a while then arranged the number by using update and then change it back again into auto incremmeent, it works bro

Improve this answer
1
  • 1
    This answer would have higher value with examples of changing Auto increment back & forth. You can edit it to add them.
    – Michael Green
    Commented Nov 7, 2022 at 8:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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