What is the difference between DELETE and TRUNCATE?
Short answer: DELETE removes rows one by one and can filter with WHERE; TRUNCATE removes all rows at once and is much faster.
What to cover
- DELETE supports WHERE, fires row-level triggers and logs each row.
- TRUNCATE removes all rows by deallocating pages and doesn't fire DELETE triggers. It resets the identity counter in MySQL and SQL Server (PostgreSQL only with RESTART IDENTITY).
- Rollback: DELETE can be rolled back; TRUNCATE can too in PostgreSQL and SQL Server inside a transaction, but it's DDL with an implicit commit in MySQL and Oracle.
Example answer
DELETE is a row-by-row operation: I can add a WHERE clause, triggers fire for each row, and every row is logged. TRUNCATE empties the whole table at once by deallocating its data pages, so it's much faster and doesn't fire DELETE triggers. Whether it can be rolled back depends on the database — yes in PostgreSQL, no in MySQL.
Avoid
- Saying TRUNCATE can never be rolled back — it depends on the database.
In a live interview, AnswerInterview shows an answer like this about 2 seconds after the question — and corrects you if you slip up. Try 30 minutes free (₹349 Week Pass after).