DELETE and TRUNCATE are two terms used in Structured Query Language (SQL) to removes the record or data from a table. Although the end result of both commands is the same but there are also some differences between these two that are important to be 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 from table removes instantly, typically bypassing a number of integrity enforcing mechanisms. It was introduced in the SQL: 2008 standard. The ‘TRUNCATE TABLE mytable’ statement is logically equivalent to ‘DELETE FROM mytable’ statement without a ‘WHERE’ clause. ‘TRUNCATE TABLE’ instantly removes all data in a table by deallocating the data pages used by the table. This reduces the resource overhead of logging the deletions, as well as the number of locks required. We can’t specify ‘WHERE’ clause in a ‘TRUNCATE TABLE’ statement as it is all or nothing.
What is DELETE?
In the database SQL, the DELETE means a statement that removes one or more records or data from a table. A subset may be defined for removal/deletion using a condition to remove the all records. Some Database Management Systems (DBMs) like MySQL permit to delete rows from various tables with one DELETE statement that is also called multi-table DELETE. The syntax of DELETE statement is DELETE FROM table_name [WHERE condition]. Now the any row that match the ‘WHERE’ condition will be deleted from the table. If the ‘WHERE’ clause is omitted from the statement, all rows in the table are deleted. So, the ‘DELETE’ statement should be used with caution. Moreover, the ‘DELETE’ statement doesn’t return any rows; that is it will not generate a result set.
- DELETE is a Data Manipulation Language (DML) command while TRUNCATE is a Data Definition Language (DDL) command.
- TRUNCATE is much faster as compare to DELETE as it instantly erase the all data.
- ‘WHERE’ condition is required in DELETE. . If the ‘WHERE’ clause is omitted from the statement, all rows in the table are deleted. But we can’t specify ‘WHERE’ clause in a ‘TRUNCATE TABLE’ statement as it is all or nothing.
- DELETE removes some or all rows from a table while TRUNCATE remove all rows.
- DELETE causes all DELETE triggers on the table to fire while no triggers are fired on during TRUNCATE operation because it is not applicable on individual rows.
- Delete can be used with indexed views while TRUNCATE can’t be uses with indexed views.
- DELETE is executed using a row lock and each row in the table is locked for deletion while TRUNCATE is executed using a table lock and all table is locked for remove all records.