DELETE TRUNCATE DROP Interview Questions

DELETE removes rows. I can use WHERE, it is logged row by row in most engines, I can roll it back in a transaction, and triggers fire. TRUNCATE empties the table by deallocating pages. It is much faster, usually cannot take a WHERE, and may reset identity. Trigger behavior depends on the database. DROP removes the table itself, definition and data. I use DELETE when I need to remove some rows, TRUNCATE when I want the table empty fast, DROP when the table should not exist. I would mention PostgreSQL TRUNCATE is transactional; some MySQL DDL is not.

TRUNCATE deallocates pages and is minimally logged in many engines. DELETE rows one by one and fires row triggers. I cannot TRUNCATE if I need a WHERE, if FK children exist without cascade, or if I need a detailed trigger per row. TRUNCATE also resets identity in some dialects. I would not TRUNCATE in a transaction if the engine treats it as DDL that auto-commits. I check the dialect before I say 'just truncate'.

I would DELETE or TRUNCATE, not DROP, if the table structure, grants, and indexes should stay. DROP removes the object. Recreating it is how people lose permissions and identity settings. If I am in a migration and replacing the table, DROP plus CREATE or swap might be fine. I would say I empty with TRUNCATE when I mean empty, and DROP when I mean the table should disappear.

I would batch DELETE ... LIMIT n in a loop with small transactions, or partition-switch the old data out, or copy keepers to a new table. A single huge DELETE can bloat WAL, lock, and tank replicas. I would do it off-peak and watch replication lag. This is operations SQL, and interviewers like it because everyone has been burned.

DELETE fires row triggers. TRUNCATE typically does not fire per-row triggers. That is why audit tables can go silent after a truncate. I would not truncate an audited table without checking. DROP is even more gone. I mention this whenever they treat truncate as a fast delete. Side effects differ.