Truncate vs. Delete

Main Difference

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.

Truncate vs. Delete — Is There a Difference?
ADVERTISEMENT

Difference Between Truncate and Delete

Truncate vs. Delete

DELETE is a Data Manipulation Language (DML) command while TRUNCATE is a Data Definition Language (DDL) command.

Truncate vs. Delete

TRUNCATE is much faster as compare to DELETE as it instantly erase the all data.

Truncate vs. Delete

‘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.

Truncate vs. Delete

DELETE removes some or all rows from a table while TRUNCATE remove all rows.

Truncate vs. Delete

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.

Truncate vs. Delete

Delete can be used with indexed views while TRUNCATE can’t be uses with indexed views.

ADVERTISEMENT

Truncate vs. Delete

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.

Truncateverb

(transitive) To shorten (something) by, or as if by, cutting part of it off.

Deleteverb

To remove, get rid of or erase, especially written or printed material, or data on a computer or other device.

Truncateverb

To shorten (a decimal number) by removing trailing (or leading) digits.

Deletenoun

(computing) A deletion.

I lost the file when I accidentally hit delete.

Truncateverb

(geometry) To replace a corner by a plane (or to make a similar change to a crystal).

ADVERTISEMENT

Deletenoun

(recorded entertainment industry) A remainder of a music or video release.

Truncateadjective

Truncated.

Deletenoun

(uncountable) Delete

Truncateadjective

Having an abrupt termination.

Deleteverb

remove or make invisible;

Please delete my name from your list

Truncateverb

replace a corner by a plane

Deleteverb

wipe out magnetically recorded information

Truncateverb

approximate by ignoring all terms beyond a chosen one;

truncate a series

Deleteverb

cut or eliminate;

she edited the juiciest scenes

Truncateverb

make shorter as if by cutting off;

truncate a wordErosion has truncated the ridges of the mountains

Truncateadjective

terminating abruptly by having or as if having an end or point cut off;

a truncate leaftruncated volcanic mountainsa truncated pyramid

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.