Truncate Table Usage

TRUNCATE TABLE

Deletes all rows in the table without logging a single row delete operation.

Grammar

TRUNCATE TABLE name

Parameters

Name

is the name of the table to truncate or the table whose entire row you want to delete.

Comments

TRUNCATE table is functionally the same as a DELETE statement without a WHERE clause: Both delete all rows in the table. However, TRUNCATE TABLE is faster than DELETE and uses less system and transaction log resources.

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

TRUNCATE table deletes all rows in the table, but the table structure and its columns, constraints, indexes, and so on, remain unchanged. The count value used for the new row identity is reset to the seed of the column. If you want to preserve the identity count value, use DELETE instead. If you want to delete the table definition and its data, use the DROP table statement.

For tables referenced by the FOREIGN KEY constraint, you cannot use TRUNCATE table and you should use a DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not recorded in the log, it cannot activate the trigger.

TRUNCATE table cannot be used for tables that participate in an indexed view.

Example

The following example deletes all data from the authors table.

TRUNCATE TABLE authors

Permissions

The TRUNCATE table permissions are granted by default to table owners, members of thesysadmin fixed server role,db_owner and db_ddladmin fixed database role members, and are not transferable.

Truncate Table Usage