Both the commands- TRUNCATE and DELETE assist in the process of data deletion from a given table. They both serve a similar typ of function but also vary in some ways or the other. In this article, we will discuss the difference between DELETE and TRUNCATE. But let us know a bit more about them first.
TRUNCATE is a type of DDL command while DELETE is a kind of DML command. One can use TRUNCATE for deleting an entire set of data from a given table while the DELETE command only deletes specific data from the entire table. Read ahead to understand more difference between DELETE and TRUNCATE commands in detail.
What is DELETE?
The DELETE command is a type of Data Manipulation Language (DML) command deployed by a user when they need to specify any tuple (row) that they want to delete or remove from a relation or table. This command also contains a clause named WHERE. If you use the DELETE command along with the WHERE clause, it will then delete or remove only the rows or tuples that cater to the provided conditions. Otherwise, it ultimately removes all the tuples or rows from your table.
The Syntax of this Command is as follows:
DELETE FROM {Name of the Table}
WHERE {applicable condition};
What is TRUNCATE?
The TRUNCATE command is a type of Data Definition Language (DDL) command that one can use for deleting all of the tuples or rows from a given table. The only difference that it has from the DELETE command is that the TRUNCATE command doesn’t have the WHERE clause. When we talk about the TRUNCATE command, it records the transaction log with regard to every deleted data page. The TRUNCATE command, unlike the DELETE command, is very fast. Just like the DELETE command, a user can deploy the TRUNCATE command to rollback the data.
The Syntax of this Command is as follows:
TRUNCATE TABLE {Name of the Table};
Difference Between DELETE and TRUNCATE
| Parameter | DELETE | TRUNCATE |
| Type of Command | The DELETE is a Data Manipulation Language (DML) command. | The TRUNCATE is a Data Definition Language (DDL) command. |
| Use | This command assists in removing or deleting specific rows (single or more than one) in a table. | This command assists in deleting all the rows present in any given table. |
| WHERE Clause | The DELETE command may contain the WHERE clause for filtering the rows that you might want to delete from a table. | The TRUNCATE command does not contain the WHERE clause, and it deletes the table as specified. |
| Locking | In this case, the tuple (table row) stays locked before the DELETE command removes it. | In this command, the entire data page (table) stays locked before the TRUNCATE command removes the table data. |
| Process of Removal/Deletion | This statement removes/deletes one row at a time. It then records the entry for every row that it deletes in the transaction log. | This statement removes/deletes the data in a table by deallocation of the data pages that store that table data. In the transaction log, it records only the deallocation of the pages. |
| Speed | This command is slower as compared to the TRUNCATE command. | This command is faster as compared to the DELETE command. |
| Required Permission | For using this command, one needs the DELETE permission for any given table. | For using this command on any table, one at least needs the ALTER permission for that given table. |
| Indexed Views | You can use the DELETE command with the indexed views. | A user cannot deploy the TRUNCATE command along with the indexed views. |
| Identity of a Column | The user can retain the identity of any column by deploying the DELETE statement on a given table. | After using the TRUNCATE statement, the identity of a column resets to its original value there and then. |
| Rollback | You can easily rollback the changes made using the DELETE command. | You cannot rollback after deleting a table with the TRUNCATE command. |
Keep learning and stay tuned to get the latest updates on GATE Exam along with GATE Eligibility Criteria, GATE 2023, GATE Admit Card, GATE Application Form, GATE Syllabus, GATE Cut off, GATE Previous Year Question Paper, and more.
Comments