Description

The SQL TRUNCATE TABLE statement can be used to delete all records from a database table, quicker than a DELETE statement without a WHERE clause.

  • Deletes all the records from a table, similar to a DELETE statement without a WHERE clause.
  • But the table is not deleted and its structure, attributes, and indexes will be intact and untouched.

In the previous chapter, we discussed how to insert, select, and update table data.

Now, let's see how to delete all the records at once using the TRUNCATE TABLE statement.

Syntax

Here is the basic syntax of a TRUNCATE TABLE statement.

  • The table_name represents the table name.
TRUNCATE TABLE table_name;

Table Data

Let's consider the below data in the table employees before executing any of the below queries.

employee_id employee_name gender birth_date hire_date salary
1 Robin Hood M 1990-10-10 2010-10-15 25000
2 Tony Blank M 1982-08-07 2010-01-05 89000
3 Andrew Russel M 1998-05-04 2012-02-20 28000
4 James Cooper M 2000-10-20 2015-05-10 45000
5 Rose Cutler F 1985-08-08 2015-06-21 65000

TRUNCATE TABLE

The below SQL statement deletes all the records from a table at once.

Run this on IDE

TRUNCATE TABLE employees;

After successful execution, use the SELECT statement to fetch the records from the table, which shows no records.

employee_id employee_name gender birth_date hire_date salary

TRUNCATE TABLE Vs DELETE

When we look at the functionality of the TRUNCATE TABLE and DELETE statement, they seem to have the same effect on table data, but they work differently.

Here is a list of some of the differences between them.

DELETE TRUNCATE TABLE

The DELETE statement supports the WHERE clause to filter and delete the specific records from a table.

The TRUNCATE TABLE statement doesn't support the WHERE clause and simply drops and re-creates the table, which resets the auto-increment column value (in case of any) to its starting value, generally 1.

The DELETE statement scans the table to generate a count of rows that needs to be deleted, then deletes the records one by one, and records an entry into the database logs for each deleted record. This whole process requires more system resources.

The TRUNCATE TABLE statement simply drops the table and re-creates it without adding any information to database logs. This process requires fewer system resources compared to the DELETE statement, which makes it faster than DELETE.

Tips & Suggestions

Never use the TRUNCATE TABLE statement unless you want to recreate the table with no records.

Use the DELETE statement if you want to delete a specific set of records based on a condition or don't want to reset the auto-increment value on the table.

Overall

We now know how to delete records on a table using the DELETE statement.

Related Links