Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
What is a DELETE statement?
2.1.
Syntax 
2.2.
Example 
3.
What is a TRUNCATE Statement?
3.1.
Syntax 
3.2.
Example 
4.
The Key Difference Between Delete and Truncate
5.
DELETE or TRUNCATE: Which is better?
6.
Frequently Asked Question
6.1.
What is the difference between DELETE and TRUNCATE?
6.2.
What is the syntax of DELETE statement?
6.3.
What Is the difference between TRUNCATE and DELETE table?
6.4.
What is the difference between drop and TRUNCATE in SQL?
7.
Conclusion
Last Updated: Mar 27, 2024
Easy

Difference Between DELETE and TRUNCATE

Leveraging ChatGPT - GenAI as a Microsoft Data Expert
Speaker
Prerita Agarwal
Data Specialist @
23 Jul, 2024 @ 01:30 PM

Introduction

Delete and truncate statements are used to manipulate and manage relational databases. Each has its features and usage. However, one might get confused between the two. Therefore it is essential to understand their differences to use them effectively.

difference-between-delete-and-truncate

In this article, we will discuss difference between delete and truncate statements by briefly discussing their concept, syntax, and properties. We will also look into some examples to understand the same by outlining some of the critical features and by presenting a tabular format to understand the comparison.

What is a DELETE statement?

The delete statement is an SQL command used to remove one or more rows from a table based on a specific condition. It is a DML (data manipulation command). This command allows us to remove unwanted or obsolete data from a database and can be used to delete single rows.

The DELETE statement is helpful for selective deletion. The DELETE statement removes specific rows based on specific conditions. It is slower than the TRUNCATE and DROP statements.

Syntax 

DELETE FROM table_name WHERE condition;


In the above syntax, ‘table_name’ is the name of the table from which we want to delete rows, and ‘condition’ is the criteria or operational parameter, on the basis of which a row is deleted.

Example 

DELETE FROM NinjaClass WHERE number<3;


The above SQL query will delete all the rows from the “NinjaClass” table where the number is less than 3.

The “NinjaClass” table has columns named “id”,”NinjaName” and “NinjaSkills”.Below is an example of what the output would look like

Before executing the DELETE statement:

Code

-- create a table


CREATE TABLE NinjaClass (
  id INT PRIMARY KEY,
  name varchar(30) NOT NULL,
  position varchar(30) NOT NULL
);
-- insert some values
INSERT INTO NinjaClass VALUES (1, 'Ninja1', 'SDE1');
INSERT INTO NinjaClass VALUES (2, 'Ninja2', 'SDE2');
INSERT INTO NinjaClass VALUES (3, 'Ninja3', 'SDE2');
INSERT INTO NinjaClass VALUES (4, 'Ninja4', 'SDE1');
-- fetch some values
SELECT * FROM NinjaClass;


Output

output


After executing the DELETE statement



DELETE FROM NinjaClass WHERE number<3;
-- fetch some values
SELECT * FROM NinjaClass;


output

output

The delete statement permanently removes the data from the table. Once deleted, it cannot be undone. Therefore DELETE statement should be used with caution. We can also take a table backup before performing any delete operation on it. 

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

What is a TRUNCATE Statement?

This statement allows us to remove all the data from the table by maintaining the table structure. The Truncate statement can be rolled back using the ROLLBACK statement. It is much faster than the DELETE statement. But it cannot remove specific rows based on a condition.

It is partially reversible and can be undone using the ROLLBACK command within a transaction. But when a transaction is committed, the deleted data cannot be recovered.

Syntax 

TRUNCATE TABLE table_name;

Example 

Suppose we have the same table as mentioned above, and if we want to remove all the rows from the table, we can use the following TRUNCATE statement:

TRUNCATE TABLE NinjaClass;
SELECT * FROM NinjaClass;


The above statement will remove all the rows from the “NinjaClass” table.

Output

output

The Key Difference Between Delete and Truncate

Below is the comparison table to show difference between DELETE and TRUNCATE statements.

Basis

DELETE Statement

TRUNCATE statement

Purpose They are used to delete specific rows from a table. They are used to remove all rows from a table.
Data Modification Data Manipulation Language(DML) statement Data Definition Language(DDL) statement
Rollback By using a DELETE statement the operation can be rolled back using a transaction log. By using the TRUNCATE statement, the operation cannot be rolled back.
Memory Usage DELETE statement uses more memory as it logs each deletion. TRUNCATE statement uses less memory as it logs nothing.
Performance The DELETE statement is slower as it deletes rows one by one. TRUNCATE statement is faster as it removes all rows at once.
Trigger The DELETE statement can also active trigger. The TRUNCATE command cannot active trigger
Transaction space It occupies more transaction spaces as compared to TRUNCATE statement. It occupies less transaction spaces.
Syntax DELETE FROM table_name WHERE condition; TRUNCATE TABLE table_name;

Must Read, Difference Between List and Set

DELETE or TRUNCATE: Which is better?

Both DELETE and TRUNCATE are used to delete data from the database. The main difference between both is that the delete command deletes data without resetting the identity of a table, whereas the truncate command resets the identity of a particular table. The DELETE command will remove a few or all the records from the table, whereas the TRUNCATE command will delete an entire row from the table. TRUNCATE command is faster compared to the DELETE command as it deallocates the data pages rather than rows and records data pages rather than rows in transaction logs. Also, if we talk about space requirements, the TRUNCATE command occupies less transaction space compared to the DELETE command.

Frequently Asked Question

What is the difference between DELETE and TRUNCATE?

The main difference between both is that the delete command deletes data without resetting the identity of a table, whereas the truncate command resets the identity of a particular table.

What is the syntax of DELETE statement?

DELETE FROM table_name WHERE condition;

In the above syntax, ‘table_name’ is the name of the table from which we want to delete rows, and ‘condition’ is the criteria or operational parameter on the basis of which a row is deleted.

What Is the difference between TRUNCATE and DELETE table?

The DELETE command will remove a few or all the records from the table, whereas the TRUNCATE command will delete an entire row from the table.

What is the difference between drop and TRUNCATE in SQL?

The DROP statement is used to remove the entire database or table data, indexes, and more. Whereas the TRUNCATE command is used to remove all the rows from the table.

Conclusion

We hope this article helped you in understanding the difference between DELETE and TRUNCATE statements. This article briefly discussed difference between DELETE and TRUNCATE statements by discussing their concept, syntax, and properties. We also looked into some examples to understand the same. We have outlined some of the key features and presented a tabular format to understand the comparison. 

Also, Read-

You will find straightforward explanations of almost every topic on this platform. So take your coding journey to the next level using Coding Ninjas.

Happy coding!

Live masterclass