SQL Clear Table (TRUNCATE TABLE statement)

In this tutorial, you will learn how to quickly delete all rows from a table using SQL.

deleting table data

The TRUNCATE TABLE statement is faster than DELETE in deleting all rows from the table. Logically, TRUNCATE TABLE is similar toDELETEnoneWHEREclauses of the statement.

The TRUNCATE TABLE statement deletes all rows from the table, but the table structure and its columns, constraints, indexes, etc., remain unchanged. To delete the table and its data, you can use theDROP TABLEStatement, but be cautious with the operation.

Syntax

Basic syntax of TRUNCATE TABLE:

TRUNCATE TABLE table_name;

Let's perform a clear operation on the database table.

Consider that we have aemployeeThe table, which contains 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 fromemployeesDelete all rows from the table:

TRUNCATE TABLE employees;

Now, after executing the above SQL statements, if you try to accessemployeein the tableSelect recordswill get an empty result set.

TRUNCATE TABLE vs DELETE

Although DELETE and TRUNCATE TABLE seem to have the same effect, their working methods are different. Here are some of the main differences between these two statements:

• The TRUNCATE TABLE statement deletes and recreates the table and resets any auto-increment values to their initial values (usually 0).1)

• DELETE allows you to filter the rows to be deleted based on an optional WHERE clause, while TRUNCATE TABLE does not support the WHERE clause and only deletes all rows.

• Compared to DELETE, TRUNCATE TABLE is faster and uses fewer system resources because DELETE scans the table to generate the number of affected rows, then deletes rows one by one, and records an entry in the database log for each deleted row, while TRUNCATE TABLE only deletes all rows without providing any other information.