Difference between Truncate vs Delete Statement

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.

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)

• Truncate vs Delete
• What is a Truncate statement?
• What is a Delete statement?
• Key difference between Truncate vs Delete statement

You can also explore these articles:

Truncate vs Delete 

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

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:

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

TRUNCATE TABLE Table A;

The output will be something like this:

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:

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:

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!