Difference between DELETE and TRUNCATE

What is delete?

DELETE is a DML or Data Manipulation Language. This command mainly specifies the row or a tuple that a person wants to eliminate or delete from the table or relation. The delete command can contain a where clause as well. If the where clause is used in combination with the delete command, the difference between deleting and truncate removes or deletes only those rows or tuples that satisfy the state. By default, it will remove all the tuples or rows from any particular table.  

What is truncate? Difference between delete and truncate

TRUNCATE is a DDL or Data Definition Language. It is the command which use to delete all the rows or tuples from any particular table. Contrasting the delete command, the truncate command does not encompass a where clause. In the truncate command, the contract or transaction log for each deleted data page will be recorded. Contrasting the delete command, the truncate command is swift as well as efficient. Like the delete, it is possible to roll back the data even after using the truncate command. 

Difference between deleting and truncate 

Let’s have a look at some of the critical differences between delete and truncate commands:

• It is possible to use the delete command when one or more rows are specified for elimination. In contrast, the truncate command is beneficial and used to delete all the rows from any particular table
• Delete is a DML or Data Manipulation Language command. While truncate is a DDL or Data Definition Language command.
• There is a presence of where clause in the delete command to filter all the records. At the same time, there may not be a where clause in the truncate command.
• In the delete command, it is possible to lock the tuple before removing it. While in the truncate command, the data page will lock before removing the table data.
• The delete statement will remove one row at a time and then records an entry in the transaction log for each deleted row. On the other hand, the truncate table will remove the data by deallocating the data pages used to store the table data, and then it will record only the page deallocations in the transaction log.
• Delete command will always be slower as compared to the truncate command. However, the truncate command possesses a breakneck speed.  
• If someone needs to use the delete, it becomes compulsory to take the permission on the table. However, for Truncate on a table, there is a condition of at least alter permission on the table.
• The individuality of the column holds the identity after using delete Statement on a particular table. However, in truncate identity, the column will arrange to its seed value if the table contains an identity column.
• It is possible that The delete can use with indexed views, but Truncate cannot use with indexed views at all. 

Summary

As it becomes clear now that a truncate command is faster and depends on fewer system resources than delete because delete keeps the potential to scan the table to create a count of rows that were affected, then delete the rows one by one and archives an entry in the database log for every deleted row. In contrast, a truncate table deletes all the rows.

Also read: Difference between center and centre