Delete All Rows from a Table

Use truncate table to delete all rows in a table. truncate table is almost always faster than a delete statement with no conditions, because the delete logs each change, while truncate table logs only the deallocation of entire data pages.

truncate table immediately frees all the space occupied by the table’s data and indexes. The freed space can then be used by any object. The distribution pages for all indexes are also deallocated. Run update statistics after adding new rows to the table.

As with delete, a table emptied with truncate table remains in the database, along with its indexes and other associated objects, unless you enter a drop table command.

You cannot use truncate table if another table has rows that reference it through a referential integrity constraint. Delete the rows from the foreign table, or truncate the foreign table and then truncate the primary table.

Related concepts
General Rules for Creating Referential Integrity Constraints