Delete all rows in the table without recording the delete operation of a single row. The TRUNCATETABLE function is the same as the DELETE statement without a WHERE clause. However, the TRUNCATETABLE is faster and less system resources and transaction log resources are used.
Delete all rows in the table without recording the delete operation of a single row. The truncate table function is the same as the DELETE statement without a WHERE clause. However, the truncate table is faster and uses fewer system resources and transaction log resources.
Remarks
Compared with the DELETE statement, truncate table has the following advantages:
The transaction log space is small.
The DELETE statement deletes a row at a time and records an entry for each row in the transaction log. Truncate table deletes data by releasing the data page used to store TABLE data, and only records the page release in transaction logs.
The number of locks used is usually small.
When the DELETE statement is executed using the row lock, the rows in the table are locked for deletion. Truncate table always locks tables and pages, rather than locking rows.
The table will leave no page without exception.
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 release empty tables in the heap. If the table lock is not used during the delete operation, the table (HEAP) contains many blank pages. For indexes, the delete operation leaves some blank pages, although these pages are quickly released through the background clearing process.
Truncate table deletes all rows in the TABLE, but the TABLE structure, its columns, constraints, and indexes remain unchanged. To delete TABLE definitions and data, use the drop table statement.
If the table contains an ID column, the counter of this column is reset to the seed value defined in this column. If no seed is defined, the default value 1 is used. To retain the ID counter, use DELETE.
Restrictions
You cannot use truncate table for the following tables:
The table referenced by the foreign key constraint.
The table that participates in the index view.
Tables published by using transaction replication or merge replication.
For tables with one or more features, use the DELETE statement.
Truncate table cannot activate the trigger because this operation does not record the deletion of each row. For more information, see create trigger (Transact-SQL ).
