TRUNCATE quickly deletes all data in a table

Note

Compared to the DELETE statement, the TRUNCATE TABLE has the following advantages:

The transaction log space used is small.

The DELETE statement deletes one row at a time and records an entry for each row that is deleted in the transaction log. TRUNCATE table deletes data by releasing the data pages used to store table data, and only records page releases in the transaction log.


There are usually fewer locks used.

When a DELETE statement is executed with a row lock, the rows in the table are locked for deletion. TRUNCATE table always locks tables and pages instead of locking each row.


No pages will be left without exception in the table.

After the DELETE statement is executed, the table still contains blank pages. For example, you must use at least one exclusive (lck_m_x) table lock to free up empty tables in the heap. If a table lock is not used when a delete operation is performed, the table (heap) contains many empty pages. For indexes, deletions leave some blank pages, although these pages are quickly released through the background cleanup process.


TRUNCATE table deletes all rows in the tables, but the table structure and its columns, constraints, indexes, and so on remain unchanged. To delete a table definition and its data, use the DROP table statement.

If the table contains an identity column, the counter for that column is reset to the seed value defined by the column. If no seed is defined, the default value of 1 is used. To preserve the identity counters, use DELETE.

Limit

You cannot use TRUNCATE table for the following table:
The table referenced by the FOREIGN KEY constraint.
The table participating in the indexed view.

A published table by using transactional replication or merge replication.

For tables with one or more of the above features, use the DELETE statement.

The TRUNCATE TABLE cannot activate triggers because the operation does not record individual row deletions. For more information, see CREATE TRIGGER (Transact-SQL).