TRUNCATE Table — SQL Technical Interview Questions & Answers

TRUNCATE Table

TRUNCATE Table

  • TRUNCATE TABLE students; deletes every row fast and resets AUTO_INCREMENT. The table structure stays. It is DDL in MySQL — you usually cannot ROLLBACK a truncate like a DELETE in a transaction (engine/version details aside: treat TRUNCATE as ‘gone’).
  • DELETE FROM students; also removes all rows but logs each row, fires DELETE triggers, and can be rolled back in a transaction. Use DELETE when you need WHERE, triggers, or rollback. Use TRUNCATE when you want an empty table quickly in practice.
  • Trap: TRUNCATE on a table that other tables reference with FOREIGN KEY — InnoDB may refuse. Trap: thinking TRUNCATE is the same as DROP TABLE (DROP removes the table itself).
  • On the example next to this theory: TRUNCATE Table — dELETE removes matching rows. TRUNCATE clears a whole table faster (MySQL).

Technical Interview Tip: TRUNCATE = empty table, keep columns. DELETE = can WHERE and rollback.

Code Examples for TRUNCATE Table

TRUNCATE Table — 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;

TRUNCATE Table — dELETE removes matching rows. TRUNCATE clears a whole table faster (MySQL).

How to Explain TRUNCATE Table to an Interviewer

TRUNCATE vs DELETE all rows?

TRUNCATE is faster, resets AI DELETE can rollback and use WHERE

TRUNCATE vs DROP?

TRUNCATE keeps the table DROP removes the table