TRUNCATE command in SQL

What is SQL TRUNCATE?

  • The SQL TRUNCATE command is used to remove table data completely.
TRUNCATE Table sto_employees;
  • You may also remove table data by using the DELETE statement, however, there are certain differences as you execute the two.
  • The following section shows the syntax and examples of using the TRUNCATE along with the difference between using the TRUNCATE and DELETE statements.

The example of using the 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:

SELECT * FROM sto_employees;


TRUNCATE Table sto_employees;

SELECT * FROM sto_employees;

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 from TRUNCATE which is explained below.

Main Points and the difference between TRUNCATE and DELETE statements

Truncate Statement
Type DDL (Data Definition Language)
Function Removes the table data completely
Rollback No rollback is required; some databases do not support TRUNCATE rollback
Performance Faster than the DELETE statement
Resources Usage Uses fewer system and transaction resources
Impact Only removes the table data, leaving the table structure intact
Auto-increment Reset Resets the auto-increment primary key in MySQL and SQL Server databases
Additional Note If you require removing table data along with its physical structure, use the DROP command
Restrictions Cannot use WHERE clause with TRUNCATE statement

 

Delete Statement
Type DML (Data Manipulation Language)
Function Removes table data; can be rolled back
Auto-reset Identity Key The identity key is not auto-reset
WHERE Clause Can be used with a DELETE statement for removing specific rows
Note The DELETE without WHERE clause can also remove the complete table data

The example of ROLLBACK using MS SQL Server

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

  1. First of all, we retrieve the complete data from the sto_products table.
  2. This is followed by using the ROLLBACK script.
  3. There, the DELETE FROM command is used to remove the complete data.
  4. The “SELECT *” statement is run again to see the table data; that should have been removed.
  5. Then, the ROLLBACK command is executed, and finally another “SELECT *” statement for fetching data.

Let us see if it brings back our removed data:

SELECT * FROM sto_products;



DECLARE @DelROLLBACKtst varchar(20) = 'RollbackTest'; 



BEGIN TRAN @DelROLLBACKtst 

       DELETE FROM sto_products;

                SELECT * FROM sto_products;                  

ROLLBACK TRAN @DelROLLBACKtst; 



SELECT * FROM sto_products;

The resultant table:

SQL DELETE ROLLBACK

You can see that 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 the removed data.

Using ROLLBACK with TRUNCATE example

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

The Code:

SELECT * FROM sto_products;



DECLARE @TruncROLLBACKtst varchar(20) = 'RollbackTest'; 



BEGIN TRAN @TruncROLLBACKtst 

       TRUNCATE table sto_products;

                SELECT * FROM sto_products;                  

ROLLBACK TRAN @TruncROLLBACKtst; 



SELECT * FROM sto_products;

The result-set:

SQL TRUNCATE ROLLBACK

So…it does.

Author - Atiq Zia

Atiq is the writer at jquery-az.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we solve the mysteries of coding together!