SQL truncate command to remove table data

The truncate statement of SQL

The truncate table statement removes whole data from the given table. You should use the Truncate statement only when you are sure to remove whole table data while you want to keep table structure in place.

The difference between Truncate and delete statement is that in the delete statement you can remove individual or specific rows with Where clause. You can also use the delete statement without the where clause to remove whole data, however, the Truncate in SQL is faster in execution and use fewer resources. The Delete statement removes the rows one by one from a table.

Syntax of using truncate statement

The general syntax of using truncate command in SQL:

TRUNCATE TABLE table_name;

Example of using truncate table statement of SQL

The example below uses the SQL Truncate statement to remove whole data from tbl_employee table. The table before and after the Truncate statement is shown in the graphic below:

Demo Table before and after truncate

The Truncate query is:

As you can see, whole data is removed in single Truncate command while the structure/columns still exist. Also, table constraints like Primary key, not null etc remains intact.

Where Truncate statement cannot be used

The Truncate in SQL would not work in a table if it has a foreign key to the other table or a table participates in an indexed view.

Was this article helpful?

Related Articles

Leave A Comment?