How can you remove all data from a table efficiently? Using the delete command can take a very long time if you have lots of data.
If you want to delete all rows from the table – the simplest way to do this is to use the TRUNCATE command.
TRUNCATE TABLE table_name
Remember that the table cannot have foreign keys constraints, but there is a workaround. You can delete foreign keys temporarily and recreate them afterward.
- Drop the FK constraints
- Execute TRUNCATE command
- Recreate the FK constraints
Once you removed all rows – consider resetting the identity value as follows:
DBCC CHECKIDENT ('table_name', RESEED, 0)
Shout out to Piotr Bach for the article that can be found here