Removes all rows from a table without logging the individual row deletes. This allows TRUNCATE TABLE to be efficient and quick, but unrecoverable.
TRUNCATE TABLE [[database.]owner.]table_name
TRUNCATE TABLE removes all rows from a table, but the table structure and all indexes (although empty of data) continue to exist until you issue a DROP TABLE statement. Defined constraints, rules and defaults bound to the columns, and triggers remain in effect. Additionally, an identity column will be reset to the original seed. If you do not want to reset the identity value for a column, use the DELETE statement without a WHERE clause instead of the TRUNCATE TABLE statement.
The TRUNCATE TABLE works similarly to the DELETE statement but is faster than DELETE. The DELETE statement removes rows one at a time and logs each deleted row as a transaction. TRUNCATE TABLE deallocates whole pages of data and makes fewer log entries. TRUNCATE TABLE guarantees that all rows are removed from the table and cannot be rolled back. DELETE can be rolled back. Both the DELETE and TRUNCATE TABLE statements reclaim the space occupied by the data and its associated indexes.
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use the DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
Note Because TRUNCATE TABLE deallocates the distribution pages for all indexes, run UPDATE STATISTICS after adding new rows to the table.
This example removes all data from the authors table.
TRUNCATE TABLE authors
TRUNCATE TABLE permission defaults to the table owner and is not transferable.
| DELETE | DROP TABLE |