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
Emp_id int,
name VARCHAR(20),
country VARCHAR(20),
Salary INT);
--insert the data in the Employee Table
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
|
Delete |
Truncate |
|
The DELETE command is used to delete
specified rows(one or more). |
While this command is used to delete all the
rows from a table. |
|
It is a DML(Data Manipulation
Language) command. |
While it is a DDL(Data Definition Language) command. |
|
There may be a WHERE clause in the DELETE
command in order to filter the records. |
While there may not be WHERE clause in the
TRUNCATE command. |
|
In the DELETE command, a tuple is locked
before removing it. |
While in this command, the data page is
locked before removing the table data. |
|
The DELETE statement removes rows one at a
time and records an entry in the transaction log for each deleted row. |
TRUNCATE TABLE removes the data by
deallocating the data pages used to store the table data and records only the
page deallocations in the transaction log. |
|
DELETE command is slower than TRUNCATE
command. |
While the TRUNCATE command is faster than
the DELETE command. |
|
To use Delete you need DELETE permission on
the table. |
To use Truncate on a table we need at
least ALTER permission on the
table. |
|
The identity of the fewer column retains the
identity after using DELETE Statement on the table. |
Identity the column is reset to its seed
value if the table contains an identity column. |
|
The delete can be used with indexed views. |
Truncate cannot be used with indexed views. |
|
This command can also active trigger. |
This command does not active trigger. |
|
DELETE statement occupies more transaction
spaces than Truncate. |
Truncate statement occupies less transaction
spaces than DELETE. |
|
Delete operations can be ROLLED
back. |
TRUNCATE cannot be Rolled
back as it causes an implicit commit. |
|
Delete doesn’t DROP the whole
table. It acquires a lock on table and starts deleting the rows. |
TRUNCATE first drops the
table & then re-create it, which is faster than deleting individual rows. |
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.
FAQs
1. Is TRUNCATE table better than DELETE?
TRUNCATE is faster than DELETE as it doesn’t log individual row deletions and doesn’t fire triggers. It removes all rows from a table and cannot be rolled back unless in a transaction, whereas DELETE allows for selective removal and can be rolled back.
2. Should I DROP or TRUNCATE table in SQL?
Use TRUNCATE to remove all rows from a table but keep its structure for future use. Use DROP if you want to completely remove the table, including its structure, from the database.
3. What is the difference between TRUNCATE, DELETE, and DROP TABLE?
TRUNCATE removes all rows but keeps the table structure. DELETE removes rows based on conditions and can be rolled back. DROP deletes the entire table, including its structure and data, permanently.