SQL: Difference between DELETE and TRUNCATE

One of the questions I got asked today is what the difference is between the SQL commands DELETE and TRUNCATE.

They both are used to empty out a table, which is different from DROP (which deletes the table)

 

DELETE

DELETE FROM <table_name>
DELETE FROM <table_name> <where_clause>

Delete is used to remove rows from a table, it can remove all rows or you can specify a where clause to delete only certain rows. A COMMIT needs to be done after a delete and all delete triggers in the table will be fired.

DELETE FROM table1 WHERE column1=’value1′;
10 rows delete.

COMMIT;
commit complete.

TRUNCATE

TRUNCATE TABLE <table_name>

Truncate is used to delete all rows from a table. It does not fire any triggers and there is no rollback. A truncate is therefore much faster than a delete

TRUNCATE TABLE table1;
table truncated.