Delete vs Truncate Command — SQL Technical Interview Questions & Answers

Delete vs Truncate Command

Delete vs Truncate Command

  • DELETE FROM t WHERE … removes some or all rows, can ROLLBACK in a transaction, fires DELETE triggers, does not reset AUTO_INCREMENT by itself (usually). TRUNCATE empties the table fast, resets AUTO_INCREMENT, typically cannot roll back like DELETE, no per-row delete triggers.
  • DROP TABLE removes the table too. Three different words — draw a tiny table on the board and say what remains after each.
  • Trap: TRUNCATE when you needed WHERE. Trap: DELETE FROM t as a ‘fast empty’ on a huge InnoDB table (TRUNCATE or drop/recreate may be better in maintenance windows).
  • On the example next to this theory: Delete vs Truncate Command — dELETE removes matching rows. TRUNCATE clears a whole table faster (MySQL).

Technical Interview Tip: DELETE rows, TRUNCATE empty, DROP table.

Code Examples for Delete vs Truncate Command

Delete vs Truncate Command — sample query

CREATE TABLE tags (id INTEGER PRIMARY KEY, label TEXT);
INSERT INTO tags VALUES (1, 'old'), (2, 'keep');
DELETE FROM tags WHERE label = 'old';
SELECT * FROM tags;

Delete vs Truncate Command — dELETE removes matching rows. TRUNCATE clears a whole table faster (MySQL).

How to Explain Delete vs Truncate Command to an Interviewer

Which can use WHERE?

DELETE

Which resets AUTO_INCREMENT?

TRUNCATE (typical)