The TRUNCATE TABLE statement is a Data Manipulation Language (DML) operation that is used to mark the contents of a table for deletion. This operation provides a faster mechanism to remove all records from a table, and unlike the DELETE statement, it resets auto increment counters to their initial values.
Syntax
The syntax for the TRUNCATE TABLE statement is:
TRUNCATE TABLE table_name;
In this syntax, table_name is the name of the table that you wish to truncate.
Please note:
- Executing a
TRUNCATE TABLEstatement requires the necessary privileges. - Exercise caution when using
TRUNCATE TABLE, as it removes all data from a table without logging individual row deletions. TRUNCATE TABLEcannot be used if the table is referenced by a FOREIGN KEY constraint. Use theDELETEstatement instead in this case.- After truncating a table, you can populate it again using INSERT statements. However, the records removed by the
TRUNCATE TABLEstatement cannot be restored.
Example
Consider the following example:
TRUNCATE TABLE employees;
In this example, the TRUNCATE TABLE statement truncates the table named employees. It removes all records from the employees table and resets all auto increment counters to their initial values.
TRUNCATE TABLE vs DELETE
The TRUNCATE TABLE statement differs from the DELETE statement in the following ways:
-
Performance:
TRUNCATE TABLEis a faster operation for removing all rows from a table than aDELETEstatement. This is due to its minimal resource requirements in terms of system and transaction log resources. -
WHERE Clause: A
DELETEstatement requires a WHERE clause to filter records. To delete all records from a table using aDELETEstatement, you would need to useDELETE FROM table_name;without a WHERE clause. On the other hand,TRUNCATE TABLEdoes not require a WHERE clause and will delete all records from the table. -
Reset Auto Increment: If the table has auto increments,
TRUNCATE TABLEwill reset these counters to their initial values.DELETEstatements do not affect auto increments. -
Transaction Log:
TRUNCATE TABLEoperations can be thought of as dropping and re-creating the table, which is much faster than deleting rows one by one, especially for large tables. However, becauseTRUNCATE TABLEis a DDL operation, it immediately commits the transaction and cannot be rolled back. Conversely, aDELETEstatement is a fully logged operation that can be rolled back.
