The TRUNCATE command in SQL

The SQL TRUNCATE command is used to remove table data completely. You may also remove table data by using DELETE statement, however, there are certain differences as you execute the two.

The following section shows syntax and examples of using the TRUNCATE along with difference of using the TRUNCATE vs DELETE.

The example of using TRUNCATE command

The general syntax of using the TRUNCATE table for removing data is:

TRUNCATE TABLE name_of_table;

See the queries below before and after using the TRUNCATE statement on the sto_employees table that stores employee information in a departmental store (for the demo only).

The first query retrieves the complete data from the table. This is followed by using the TRUNCATE command and finally, the data is retrieved again and see the outcome:

Queries:

The resultant table:

SQL TRUNCATE

You can see, the second SELECT statement did not return any record.

The same result can be achieved by using the following query:

DELETE FROM sto_employees;

However, this is different to TRUNCATE which is explained below.

Main Points and the difference between TRUNCATE and DELETE statements

The Truncate Statement:

  • The TRUNCATE is a DDL (Data Definition Language) command.
  • It removes the table data completely.
  • You should only use the TRUNCATE SQL command when you are sure and no rollback is required. Some databases do not support TRUNCATE rollback.
  • Removing data by TRUNCATE is faster than DELETE statement.
  • The TRUNCATE is faster in execution and uses fewer system and transaction resources.
  • TRUNCATE only removes the table data while table structure remains in place.
  • The TRUNCATE statement resets the auto-increment primary key in MySQL and SQL Server databases etc.
  • If you require removing table data along with its physical structure then use the DROP command.
  • You cannot use WHERE clause with TRUNCATE statement.

The DELETE Statement:

  • The DELETE is a DML (Data Manipulation Language) command.
  • The DELETE without WHERE clause can also remove the complete table data.
  • The data removed after DELETE command can be rolled back.
  • The identity key is not auto-reset as using the DELETE command.
  • The WHERE clause can be used with DELETE statement for only removing specific rows.

The example of ROLLBACK using MS SQL Server

For having an idea how data can be rolled back as using the DELETE command, see the little code below.

First of all, we retrieve the complete data from the sto_products table. This is followed by using the ROLLBACK script. There, the DELETE FROM command is used for removing the complete data.

The SELECT * statement is run again to see the table data; that should have been removed.

Then, the ROLLBACK command is executed and finally another “SELECT *” statement for fetching data. Let us see if it brings back our removed data:

The resultant table:

SQL DELETE ROLLBACK

You can see, the data is back after running the ROLLBACK command while we used the DELETE statement for removing the data. Now, let us use the TRUNCATE command with ROLLBACK and see if we are able to get back removed data?

Using ROLLBACK with TRUNCATE example

Well, let us perform a test on SQL Server database whether it rollback transaction after using the TRUNCATE command on the same table as used for the DELETE statement:

The Code:

The result-set:

SQL TRUNCATE ROLLBACK

So…it does.