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.


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 TABLE statement requires the necessary privileges.
  • Exercise caution when using TRUNCATE TABLE, as it removes all data from a table without logging individual row deletions.
  • TRUNCATE TABLE cannot be used if the table is referenced by a FOREIGN KEY constraint. Use the DELETE statement instead in this case.
  • After truncating a table, you can populate it again using INSERT statements. However, the records removed by the TRUNCATE TABLE statement cannot be restored.


Consider the following example:


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.


The TRUNCATE TABLE statement differs from the DELETE statement in the following ways:

  1. Performance: TRUNCATE TABLE is a faster operation for removing all rows from a table than a DELETE statement. This is due to its minimal resource requirements in terms of system and transaction log resources.

  2. WHERE Clause: A DELETE statement requires a WHERE clause to filter records. To delete all records from a table using a DELETE statement, you would need to use DELETE FROM table_name; without a WHERE clause. On the other hand, TRUNCATE TABLE does not require a WHERE clause and will delete all records from the table.

  3. Reset Auto Increment: If the table has auto increments, TRUNCATE TABLE will reset these counters to their initial values. DELETE statements do not affect auto increments.

  4. Transaction Log: TRUNCATE TABLE operations 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 TABLE is a DDL operation, it immediately commits the transaction and cannot be rolled back. Conversely, a DELETE statement is a fully logged operation that can be rolled back.

© Polypheny GmbH. All Rights Reserved.