What is SQL TRUNCATE?
- The SQL TRUNCATE command is used to remove table data completely.
- 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:
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:
You can see, the second SELECT statement did not return any record.
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.
- 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 to remove 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:
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:
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:
So…it does.
