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.
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.
- 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 the
DELETEstatement 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.
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 statement differs from the
DELETE statement in the following ways:
TRUNCATE TABLEis a faster operation for removing all rows from a table than a
DELETEstatement. 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 a
DELETEstatement, you would need to use
DELETE 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.
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, because
TRUNCATE TABLEis a DDL operation, it immediately commits the transaction and cannot be rolled back. Conversely, a
DELETEstatement is a fully logged operation that can be rolled back.