SQL TRUNCATE TABLE Statement

The SQL TRUNCATE TABLE statement is used to remove all data from an existing table, but the table structure and its columns, constraints, indexes, etc... remain untouched.

The TRUNCATE TABLE performs the same function as a DELETE statement without a WHERE clause.

The TRUNCATE TABLE removes all the rows from a table more quickly than a DELETE.

Syntax

The syntax of the TRUNCATE TABLE statement is as follows:

TRUNCATE TABLE table_name;

Parameters:

• table_name: The name of the table that you want to truncate.

Demo Table

Let us suppose that we have a table called "Employee" with the following records:

SQL TRUNCATE TABLE Example

You might choose to truncate a table instead of dropping and recreating it. Using the TRUNCATE TABLE to remove data from a table is fast and does not touch any of the table's structure, columns, constraints, indexes, and dependencies.

If you don't worry about rolling back, truncating a table is a fast way to clear out all the records from a table.

The following command removes all the records from the "Employee" table:

TRUNCATE TABLE employee;

After executing, if you try to use the SELECT statement to fetch data from the "Employee" table, you will get the following empty result set:

+--------+-------------+-----------+------------+------------+-----------+------------------+
| emp_no | first_name  | last_name | birth_date | hire_date  | salary    | dept_name        |
+--------+-------------+-----------+------------+------------+-----------+------------------+
|        |             |           |            |            |           |                  |
+--------+-------------+-----------+------------+------------+-----------+------------------+

The above truncate example would be equivalent to the following DELETE statement:

FELETE FROM employee;

Both the TRUNCATE TABLE and the DELETE statements would result in all records from the "Employee" table being deleted. The main difference between the two is that you can roll back the DELETE statement, but you can not roll back the TRUNCATE TABLE statement.

If you need to prefix the table name with the database name, you can run the following command:

TRUNCATE TABLE demo_db.employee;

TRUNCATE TABLE vs. DELETE

Although TRUNCATE TABLE and DELETE seem to have the same effect, they do work differently. Here are some differences between these two SQL statements:

• The TRUNCATE TABLE statement is used to remove all the rows from a table, whereas the DELETE statement is used to remove records (one or more).
• The TRUNCATE TABLE statement does not support the WHERE clause, whereas you can use the WHERE clause with the DELETE statement in order to filter rows.
• The TRUNCATE TABLE statement drop and recreate the table in a way that it reset the auto-increment value at its start.
• The TRUNCATE TABLE statement is faster and uses fewer system resources than the DELETE statement.
• When you use the TRUNCATE TABLE, you can not roll back the data, whereas utilizing the DELETE allows you to roll back the data.
• The TRUNCATE TABLE is a DDL (Data Definition Language) command, whereas the DELETE is a DML (Data Manipulation Language) command

When to use TRUNCATE TABLE vs. DELETE

You can use the TRUNCATE TABLE statement when you want to remove all the records and recreate the whole table. On the other hand, you can use the DELETE statement either if you want to delete a limited number of rows based on a specific condition or you do not want to reset the auto-increment value.