What is the difference between DELETE and TRUNCATE operators?

DELETE removes rows one by one, recording each deletion in the transaction log. It supports WHERE clauses for selective deletion and returns the number of rows deleted. It can be rolled back (ROLLBACK).

TRUNCATE removes all rows from a table, freeing the used space, without recording individual deletions in the transaction log (only the operation itself is logged). It does not support WHERE clauses. Usually, it is faster than DELETE without a WHERE condition. TRUNCATE does not trigger ON DELETE triggers. It cannot be fully rolled back in most cases, as the operation is considered DDL (Data Definition Language), although in some DBMSs (e.g., PostgreSQL), TRUNCATE can participate in transactions and be rolled back.

Key differences:

Attribute	DELETE	TRUNCATE
Operation type	DML (Data Manipulation Language)	DDL (Data Definition Language)
Logging	Each deleted row	The fact of the operation
WHERE clause support	Supported	Not supported
Rollback	Possible	Usually not possible (except in some implementations)
ON DELETE triggers	Fired	Not fired
Returns number of rows	Yes	No
Performance	Slower (when deleting all rows), faster (when deleting a subset)	Faster (when deleting all rows)
Identity reinitialization	No	Yes (for most DBMSs)