What is the difference between DELETE and TRUNCATE commands in SQL?
Direct Answer
This question distinguishes between row-level deletion and table-level reset operations in database management.
Why Interviewers Ask This
Candidates need to know the performance and structural implications of removing data. Interviewers check if you understand logging, rollback capabilities, and identity reset behaviors. Choosing the wrong command can lead to unnecessary performance degradation or accidental data loss.
How to Answer This Question
Contrast DELETE as a DML command that removes rows individually and logs each action. Contrast TRUNCATE as a DDL command that deallocates pages and resets counters. Mention that TRUNCATE cannot be rolled back in some contexts and does not fire triggers. Use a scenario involving large dataset cleanup.
Key Points to Cover
- DML vs DDL distinction
- Logging and rollback
- Performance impact
- Trigger behavior
Sample Answer
DELETE is a DML command that removes specific rows based on a condition and logs each deletion, allowing for rollback. It fires triggers and is slower on large tables. TRUNCATE is a DDL command that removes all rows inst…
Common Mistakes to Avoid
- Saying TRUNCATE deletes rows one by one
- Ignoring trigger differences
- Confusing syntax requirements
Sound confident on this question in 5 minutes
Answer once and get a 30-second AI critique of your structure, content, and delivery. First attempt is free — no signup needed.