Deleting All Rows from a Table

Considerations for deleting all rows from a table.

  • If you do not use a where clause, all rows in the table named after delete [from] are removed. The table, though empty of data, continues to exist until you issue a drop table command.

  • truncate table and delete without a row specification are functionally equivalent, but truncate table is faster. delete removes rows one at a time and logs these transactions. truncate table removes entire data pages, and the rows are not logged.

    Both delete and truncate table reclaim the space occupied by the data and its associated indexes.

  • You cannot use the truncate table command on a partitioned table. To remove all rows from a partitioned table, either use the delete command without a where clause, or unpartition the table before issuing truncate table.