Answer

DELETE and TRUNCATE are both tools for clearing tables but work differently:

• DELETE removes rows based on a condition or all rows, supports WHERE, can be rolled back, activates triggers. It can be slow with a large number of rows (one row at a time).
• TRUNCATE instantly clears the entire table, cannot use WHERE, often does not log row-by-row, does not always activate triggers. In most cases, the action is irreversible, cannot be rolled back in certain DBMS.

Nuances:

• DELETE supports transaction rollback (ROLLBACK), TRUNCATE does not or depends on the DBMS.
• TRUNCATE usually resets the identity/sequence counter.
• TRUNCATE cannot be applied if there are foreign keys or references to the table.

Example:

-- Delete orders older than 3 years
DELETE FROM orders WHERE created_at < CURRENT_DATE - INTERVAL '3 years';

-- Completely clear the table (fast)
TRUNCATE TABLE logs;

Trick Question

Can data be restored after TRUNCATE?

A common mistake: thinking that, like DELETE, TRUNCATE can be rolled back in a transaction. But TRUNCATE usually cannot be rolled back or undone, except in some DBMS (for example, PostgreSQL when working within a transaction).

Example (in most DBMS):

BEGIN;
TRUNCATE TABLE orders;
ROLLBACK; -- In most DBMS, this will not work, data lost!

Examples of real errors due to lack of knowledge about the nuances of the topic

---

Story

Deleting temporary data in a production database with TRUNCATE instead of TEST. The audit trigger for row deletion only fired on DELETE — information about lost data, time, and user is missing. This affected incident resolution.

---

Story

Clearing a table with TRUNCATE while having a foreign key. SQL threw an error due to referential integrity constraints, the script did not complete, automation stopped. Solution: temporarily remove foreign keys or use DELETE.

---

Story

Mass deletion of data via DELETE in large tables without batching: due to long locks, the database "froze", other transactions waited for locks to be released. The outcome — system downtime. Correct approach: use DELETE LIMIT/OFFSET if supported, or batch processing.