Little SQL Server Tricks: Empty Table and Reset Auto Increment Id

From time to time I get my development database in a state where I need to get rid of all rows in a table. SQL Server offers two ways to do that: DELETE and TRUNCATE. When you need to remove all rows use TRUNCATE, it is the faster option:

When this command completes the table is empty and the auto increment Id is set to NULL:

If I insert a row it will get the Id 1 and everything works as expected.

 

Remove only the newest rows

If you only need to remove a few rows you should use DELETE and specify in the WHERE clause which rows you want to delete:

This command will delete all books with an Id bigger than 10, but it will not change the value of the auto increment Id. If you had 20 rows in the Book table before you deleted the rows, the next row you insert will have Id 21 and not 11. In most cases this isn’t a problem.

For the special case that this gap is a problem, use the DBCC CHECKIDENT function to reset the value. The Id for the next row will be current value + 1, therefore set the new_reseed_value on the current Id.
Attention: different SQL Server versions handle this a bit different, you better don’t try this the first time on your production server!

Your Id is now set to 10 and the next row you insert will have Id 11 – the gap is gone.

1 thought on “Little SQL Server Tricks: Empty Table and Reset Auto Increment Id”

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.