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:
1 |
TRUNCATE TABLE Book |
When this command completes the table is empty and the auto increment Id is set to NULL:
1 2 3 4 5 |
DBCC CHECKIDENT (Book) Checking identity information: current identity value 'NULL', current column value 'NULL'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. |
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:
1 |
DELETE FROM Book WHERE Id > 10 |
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!
1 2 3 |
DBCC CHECKIDENT ( table_name, RESEED, new_reseed_value ) DBCC CHECKIDENT (Book, RESEED, 10) |
Your Id is now set to 10 and the next row you insert will have Id 11 – the gap is gone.
Thanks! this save my life.