delete vs truncate in sql

I was fiddling with some test databases recently when a colleague suggested I could wipe the entire database (without dropping the table) using a TRUNCATE command.

This got me wondering though - what’s the difference between TRUNCATE and DELETE?

The major differences seem to be:

1. Performance: TRUNCATE is typically faster than DELETE
2. Visibility: TRUNCATE does not log the records deleted, whereas DELETE does.
3. Locking: TRUNCATE locks the entire table. DELETE locks the rows before deleting them. DELETE then is a better choice for a table with many concurrent users (since they’ll still be able to transact with rows that are not being actively deleted)
4. Conditions: TRUNCATE doesn’t take any conditions, whereas DELETE can accept a WHERE clause. The consequence - TRUNCATE can only “reset” a table to its empty state. DELETE can be used for more fine-tooth’d removal of rows.

TRUNCATE employees;

Is approximately the same as (though the mechanisms differ):

DELETE employees;

Whereas there’s no TRUNCATE equivalent to:

DELETE employees e
  WHERE e.firstName = 'John';

Sources:

• Difference between Delete and truncate in sql query
• SQL Truncate and Delete - Essential SQL