Difference between Truncate vs Delete Statement

Difference between Truncate vs Delete Statement

6 mins read565 Views Comment
Anshuman
Anshuman Singh
Senior Executive - Content
Updated on Jan 10, 2024 16:16 IST

Truncate vs Delete - The Truncate statement allows you to delete entire rows from a table. In contrast, the Delete statement allows you to remove some or specified rows from the table.

2022_11_MicrosoftTeams-image-158.jpg

In this article, we will explore Truncate vs Delete statement. But, before we dive deeper into the difference between these two statements, let’s go through the list of topics listed under the table of contents that we will cover in this article.

Table of contents (TOC)

You can also explore these articles:

Primary Key vs Unique Key
Primary Key vs Unique Key
The main difference between a primary key vs unique key is that a primary key is a key that uniquely identifies each record in a table but cannot store NULL...read more
What are Constraints in SQL?
What are Constraints in SQL?
Constraints in SQL are not a mandatory to use while creating the table but they are very helpful to maintain the accuracy, reliability, and integrity of the data during the...read more
How to CREATE TABLE in SQL
How to CREATE TABLE in SQL
When you are working with the large dataset sometime you want to segregate some data and inspect them separately or sometimes you don’t have any table or dataset in that...read more

Truncate vs Delete 

For a better understanding, let’s explore Truncate vs Delete statement in a tabular format:

Benchmark Truncate Delete
What is it? A statement that allows you to delete entire rows from a table. A statement that allows you to remove some or specified rows from the table.
Command type DDL (Data Definition Language) command DML (Data Manipulation Language) command
Seed Fast Slow
Does this command use the “WHERE” clause to filter records from the table? No Yes
Permission needed to use this command ALTER permission DELETE permission
Before deleting the data, it locks Data Page Row 
Resets the table identity No Yes
Maintains transaction logs for each deleted record No Yes
Activates trigger command No Yes
Allows you to restore deleted data No Yes
Can be used with indexed views No Yes
Transaction space occupied More. This is due to the fact that it keeps a transaction log for the entire data page instead of each row. Less. This is due to the fact that it keeps a log for each deleted row.

What is a Truncate statement?

Truncate definition: A Truncate statement allows you to delete entire rows from a table, regardless of whether or not any conditions are met.

The Truncate statement is a type of DDL (Data Definition Language) command that allows you to remove or delete entire rows from a table, regardless of whether or not any conditions are met. Once you use the Truncate statement on a table, the table structure remains intact, and all the data is deleted. The Truncate statement does not allow rollback. Simply put, it means that once all the data has been deleted, it cannot be recovered, as the Truncate statement does not maintain a log for each deleted row.

As the Truncate statement does not maintain a log for each deleted row, its operating speed is way faster in comparison to the Delete statement. The Truncate statement locks data pages before it performs the deletion process instead of rows, as in the Delete statement case. And, because the Truncate statement locks data pages instead of rows, it requires fewer locks and resources than the Delete statement.

Here’s the syntax for the Truncate statement:

TRUNCATE TABLE Table_name;

For a better understanding of the Truncate statement, let’s go through an example (Truncate example):

Table A:

Aadhar_number Name City Age
178695740935 Atul Noida 27
167498564499 Anshuman Lucknow 26
150067773489 Aquib Delhi 26
128766448830 Vikram Shillong 25

So, to run the Truncate command, you will use this:

TRUNCATE TABLE Table A;

The output will be something like this:

Field Type NULL Key Default Extra
Aadhar_number int YES   NULL  
Name varchar(20) YES   NULL  
City varchar(20) YES   NULL  
Age int YES   NULL  

Note: As you can see, all the data has been deleted, and only the table structure remains intact.

You can also explore: 100+ SQL Interview Questions and Answers for 2022

Must explore: SQL query to find second highest salary

What is a Delete statement?

Delete definition: A Delete statement enables you to delete some or all rows from a table according to the conditions specified in those rows.

The Delete statement is a DML (Data Manipulation Language) command that allows you to delete some or all rows from a table according to the conditions specified in those rows. Once you use the Delete statement on a table, the rows that meet the condition are deleted. The Delete statement allows rollback. Simply put, once all the data has been deleted, it can be recovered, as the Delete statement maintains a log for each deleted row.

As the Delete statement maintains a log for each deleted row, its operating speed is much slower than the Truncate statement. The Delete statement locks data pages before it performs the deletion process instead of rows, as in the Delete statement case. And, because the Truncate statement locks rows instead of data pages, it requires more locks and resources than the Truncate statement.

Here’s the syntax for the Delete statement:

DELETE FROM table_name WHERE condition;  

For a better understanding of the Delete statement, let’s go through an example (Delete example):

Table A:

Aadhar_number Name City Age
178695740935 Atul Noida 27
167498564499 Anshuman Lucknow 26
150067773489 Aquib Delhi 26
128766448830 Vikram Shillong 25

Now, you want to remove or delete the row where Aadhar_number = 150067773489. To do this, you can use this command:

DELETE FROM Tabel A WHERE Aadhar_number =  150067773489;

The output will be something like this:

Aadhar_number Name City Age
178695740935 Atul Noida 27
167498564499 Anshuman Lucknow 26
128766448830 Vikram Shillong 25

You can also explore: What is the Difference Between SQL and MySQL?

Key differences between Truncate vs Delete

Here are the key differences between the Truncate vs Delete statements:

  • Transaction space is occupied more by the Truncate command in comparison to the Delete command.
  • The truncate command does not use the “WHERE” clause to filter records from the table, but the Delete command does.
  • The Truncate command locks the data page’s before deletion. On the other hand, the Delete command locks the rows.
  • Truncate command is a DDL (Data Definition Language) command, but the Delete command is a DML (Data Manipulation Language) command.
  • The truncate command allows you to delete entire rows from a table, whereas the Delete command allows you to remove some or specified rows from the table.

Conclusion 

Truncate vs Delete - The main difference between these two statements is that the Truncate statement deletes entire rows from a table. In contrast, the Delete statement deletes some or specified rows from the table.

If you have queries regarding Truncate vs Delete, please feel free to drop your queries in the comment box. We will be happy to help!

FAQs

What is the main difference between Truncate vs Delete statements?

The main difference between Truncate vs Delete is that the Truncate statement allows you to delete entire rows from a table. On the other hand, the Delete statement allows you to remove some or specified rows from the table.

Regarding the difference between Truncate vs Delete, which of the two statements locks data pages before deletion?

In terms of Truncate vs Delete, the Truncate statement/command locks data pages before deletion.

Regarding the difference Truncate vs Delete, which statement is a type of Data Manipulation Language command?

In terms of difference between Truncate vs Delete command, the Delete command is a type of Data Manipulation Language command.

Regarding the difference Truncate and Delete, which command's operation is faster and why?

In terms of difefrence between Truncate and Delete, The working of Truncate command is much faster than the Delete command. It is so because the Truncate statement does not maintain a log for each deleted row.

About the Author
author-image
Anshuman Singh
Senior Executive - Content

Anshuman Singh is an accomplished content writer with over three years of experience specializing in cybersecurity, cloud computing, networking, and software testing. Known for his clear, concise, and informative wr... Read Full Bio