Can I reorganize a Primary Key after deleting some rows? 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.
Both options delete all records from your table and restarts IDENTITY values.

TRUNCATE TABLE

TRUNCATE TABLE &lt;schema&gt;.&lt;table_name&gt;;



  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 &lt;schema&gt;.&lt;table_name&gt;;
DBCC CHECKIDENT ('&lt;schema&gt;.&lt;table_name&gt;', 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.