Difference between DELETE and TRUNCATE

When managing large datasets in SQL, it's essential to understand the differences between various commands used for removing data. Two commonly used SQL commands for data removal are DELETE and TRUNCATE. While both serve the purpose of removing rows from a table, they have distinct features and use cases that can significantly impact the performance of our database operations.

In this article, we will explain the differences between DELETE and TRUNCATE, including their syntax, advantages, and the best scenarios for their use. Understanding when to use DELETE, TRUNCATE, or even DROP TABLE can help us maintain optimal database performance.

What is the DELETE Command in SQL?

The DELETE command in SQL is part of the DML (Data Manipulation Language) category and is used to remove specific rows from a table based on a condition. We can delete all rows or filter which rows to delete by using a WHERE clause.

Syntax

DELETE FROM TableName 
WHERE condition; 

Example of DELETE Command

Let's understand it with taking one simple example in which we will create one dummy table and then do the delete operation.

Employee Table

CREATE table Employee (
Emp_id int, 
name VARCHAR(20), 
country VARCHAR(20),
 Salary INT);

--insert the data in the Employee Table
INSERT INTO Employee (Emp_id, name, country, Salary)
values (101, 'Mohit', 'India', 60000),
 (103, 'Anish', 'England', 70000),
 (104, 'Shubham', 'France', 100000),
 (102, 'Danish', 'Sweden', 40000),
 (105, 'Vivek', 'Wales', 50000),
 (106, 'Rohan', 'Scotland', 30000);
 Select * from Employee ;                                                                                           

Output

We must now create a query to remove the last entry with the value 106 for the Emp_id.

Query:

Delete from Employee where Emp_id = 106;

Output

What is the TRUNCATE Command in SQL?

TRUNCATE is a DDL(Data Definition Language) command and is used to delete all the rows or tuples from a table. Unlike the DELETE command, the TRUNCATE command does not contain a WHERE clause. In the TRUNCATE command, the transaction log for each deleted data page is not recorded. We cannot roll back the data after using the TRUNCATE command.  Unlike the DELETE command, the TRUNCATE command is fast.

Syntax

TRUNCATE TABLE  TableName;

Example for TRUNCATE Command

Let’s see an example of using TRUNCATE to delete all rows from the Employee table. Unlike DELETE, TRUNCATE is faster because it does not log each row deletion and does not fire triggers.

Query:

TRUNCATE TABLE Employee;

Rollback Example

We can use the ROLLBACK command to undo a TRUNCATE operation if no COMMIT has been issued. However, once executed, the data cannot be rolled back unless it is part of an active transaction.

Query:

BEGIN TRANSACTION;

TRUNCATE TABLE Employee;

SELECT * FROM Employee;

-- To undo the TRUNCATE operation
ROLLBACK TRANSACTION;

SELECT * FROM Employee;

Note that this rollback is only possible if a COMMIT has not been executed after the TRUNCATE command. Once a COMMIT is made, the changes cannot be undone.

Differences between DELETE and TRUNCATE

Conclusion

In summary, DELETE, TRUNCATE, and DROP TABLE are all essential SQL commands for managing data, but they serve different purposes and have unique characteristics. Understanding the differences between them is important for database performance optimization. Use DELETE when we need to selectively remove rows, TRUNCATE when you want to quickly clear all rows from a table, and DROP TABLE when you need to remove a table entirely.