Difference between Truncate and Delete

DELETE and TRUNCATE are two terms used in Structured Query Language (SQL) to delete record or data from a table. Although the end result of both commands is the same, there are also some differences between these two that are important to understand. The main difference between DELETE and TRUNCATE is that DELTE is a Data Manipulation Language (DML) command while TRUNCATE is a Data Definition Language (DDL) command.

What is TRUNCATE?

TRUNCATE is also a logged operation. The TRUNCATE TABLE statement is a DDL command in SQL that marks the extents of a table for deallocation. As a result of this operation, all data in the table is instantly deleted, usually bypassing a series of integrity enforcement mechanisms. It was introduced in the SQL standard: 2008. The ‘TRUNCATE TABLE mytable’ statement is logically equivalent to the ‘DELETE FROM mytable’ statement without a ‘WHERE’ clause. ‘TRUNCATE TABLE’ instantly removes all data from a table by deallocating the data pages used by the table. This reduces the resource overhead of logging deletes, as well as the number of locks required. We cannot specify the ‘WHERE’ clause in a ‘TRUNCATE TABLE’ statement as it is all or nothing.

What is DELETE?

In SQL database, DELETE means a statement that deletes one or more records or data from a table. A subset can be defined for deletion/deletion using a condition to delete all records. Some database management (DBM) systems like MySQL allow rows to be deleted from multiple tables with a DELETE statement which is also called a multi-table DELETE. The DELETE statement syntax is DELETE FROM table_name[WHERE condition]. Now any row that matches the ‘WHERE’ condition will be removed from the table. If the ‘WHERE’ clause is omitted from the declaration, all rows in the table are deleted. Therefore, the ‘DELETE’ statement should be used with caution. Also, the ‘DELETE’ statement does not return any rows; that is, it will not generate a result set.

1. DELETE is a data manipulation language (DML) command while TRUNCATE is a data definition language (DDL) command.
2. TRUNCATE is much faster compared to DELETE as it instantly deletes all data.
3. The ‘WHERE’ condition is required on DELETE. . If the ‘WHERE’ clause is omitted from the declaration, all rows in the table are deleted. But we cannot specify the ‘WHERE’ clause in a ‘TRUNCATE TABLE’ statement as it is all or nothing.
4. DELETE deletes some or all rows from a table, while TRUNCATE deletes all rows.
5. DELETE causes all DELETE triggers on the table to fire while no trigger fires during the TRUNCATE operation because it is not applicable on individual rows.
6. Delete can be used with indexed views, while TRUNCATE cannot be used with indexed views.
7. DELETE is executed using a row lock and each row of the table is locked for deletion, while TRUNCATE is executed using a table lock and the entire table is locked to delete all records.