Skip to content

Difference between DROP and TRUNCATE in SQL

Difference between DELETE and TRUNCATE

Last Updated : 28 Nov, 2024

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

Employee Table

Employee Table

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

output

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.

Difference between DROP and TRUNCATE in SQL

M

MKS075

Improve

Article Tags :

Similar Reads

Difference between DELETE, DROP and TRUNCATE

In SQL, understanding the DELETE, DROP, and TRUNCATE commands is important for efficient data management. While these commands are all used to remove data, they differ significantly in functionality, usage, and performance. Knowing when and how to use each command can improve the efficiency and inte

Difference Between DELETE and DROP in SQL

In SQL, the DELETE and DROP commands are essential for managing data in a database, but they serve different purposes. While both are used to remove data, their functionality varies significantly. The DELETE command is designed to remove specific rows (tuples) or all rows from a table while preservi

Difference between DROP and TRUNCATE in SQL

In SQL, the DROP and TRUNCATE commands are used to remove data, but they operate differently. DROP deletes an entire table and its structure, while TRUNCATE removing only the table data. Understanding their differences is important for effective database management. In this article, We will learn ab

Difference between remove() and detach() Methods

Before looking at the differences between these two methods of jQuery, let us understand the methods first. remove(): This remove() method removes the matched elements from the DOM. When we apply the remove() method to any element, then everything inside that element and the element itself will be r

Difference between Normalization and Denormalization

Normalization and Denormalization are used to alter the structure of a database. The main difference between normalization and denormalization is that normalization is used to remove the redundancy in the table, while denormalization is used to add the redundancy which means combining multiple table

Difference between Dilation and Erosion

Dilation and Erosion are basic morphological processing operations that produce contrasting results when applied to either gray-scale or binary images. Dilation: Dilation is the reverse process with regions growing out from their boundaries.Dilation is A XOR B.Erosion: Erosion involves the removal o

Difference between Set.clear() and Set.delete() Methods in JavaScript

Both Set.clear() and Set.delete() methods modify the present Set by removing elements. But there are some fundamental differences between these two, which will be discussed in this article. Set.clear() Method: In this method, all the elements in the set get deleted. So, as a result, the set becomes

Difference Between ON DELETE CASCADE and ON DELETE SET NULL in DBMS

ON DELETE CASCADE and ON DELETE SET NULL are two important options in SQL foreign key constraints that define how the database handles related records in a child table when a record in the parent table is deleted. These options are crucial for maintaining referential integrity and ensuring a consist

Difference between Lossless and Lossy Join Decomposition

The process of breaking up a relation into smaller sub-relations is called Decomposition. Decomposition is required in DBMS to convert a relation into a specific normal form which further reduces redundancy, anomalies, and inconsistency in the relation. There are mainly two types of decompositions i

Difference Between Compaction and Defragmentation

Compaction and Defragmentation are two key techniques that are used to reduce fragmentation, they operate in different environments and serve different system needs. Compaction is used in main memory (RAM) to shift and reorganize data, creating large blocks of contiguous free memory. Defragmentation