Difference between Truncate and Delete in SQL

All SQL Databases offer the DELETE Command. The purpose as anyone would know is to delete rows from a table in a database.

So whats TRUNCATE ?

Truncate as the name suggests empties a table completely. Logically, this is equivalent to a DELETE statement that deletes all rows, but there are practical differences under some circumstances.

Notable Differences

• Truncate operations drop and re-create the table, which is much faster than deleting rows one by one.
• The number of deleted rows is not returned.
• TRUNCATE statement does not invoke ON DELETE triggers.

The above might be applicable for MySQL 5.0 upwards but overall .

Interestingly the TRUNCATE statement is not actually a part of the SQL standard, but many relational database management systems implement it.

In SQL Truncate statement doesn’t delete the content of a table which is to be truncated while it removes the address of the table from the list containing addresses to identify the table. That’s why Truncate statement is 300 times faster than Delete statement of SQL because Delete statement removes the content of table one-by-one.

Truncate will help you save a lot of time when deleting for example 1 Million Rows.