The TRUNCATE TABLE statement deletes all rows from a table much faster than a DELETE statement. Conceptually, TRUNCATE TABLE behaves similarly to DELETE without a WHERE clause.
When you use TRUNCATE TABLE, it removes all rows from the table while preserving the table's structure, including its columns, constraints, indexes, and other attributes. To completely remove both the table's data and its definition, you would use the DROP TABLE statement.
The basic syntax of TRUNCATE TABLE is as follows:
table_name;Let's demonstrate the truncate operation on a database table.
Assume we have an employees table in our database with the following records:
+--------+--------------+------------+--------+---------+ | emp_id | emp_name | hire_date | salary | dept_id | +--------+--------------+------------+--------+---------+ | 1 | Ethan Hunt | 2001-05-01 | 5000 | 4 | | 2 | Tony Montana | 2002-07-15 | 6500 | 1 | | 3 | Sarah Connor | 2005-10-18 | 8000 | 5 | | 4 | Rick Deckard | 2007-01-03 | 7200 | 3 | | 5 | Martin Blank | 2008-06-24 | 5600 | NULL | +--------+--------------+------------+--------+---------+
The following command deletes all rows from the employees table:
TRUNCATE TABLE employees;Now, after executing the SQL statement above, if you attempt to select records from the employees table, you will receive an empty result set.
Although DELETE and TRUNCATE TABLE may appear to have the same effect, they operate differently. Here are some key differences between these two statements:
TRUNCATE TABLE drops and recreates the table, resetting any auto-increment values to their starting value, typically 1.DELETE allows you to specify which rows to delete using an optional WHERE clause. In contrast, TRUNCATE TABLE does not support a WHERE clause and removes all rows from the table.TRUNCATE TABLE is faster and consumes fewer system resources than DELETE. This is because DELETE scans the table to count the affected rows, deletes each row individually, and logs each deletion in the database log. In contrast, TRUNCATE TABLE simply removes all rows without generating additional logging for each row.Tip: Use TRUNCATE TABLE when you need to delete all rows and re-create the table. Use DELETE if you want to delete a specific number of rows based on conditions or if you want to avoid resetting auto-increment values.