What is the difference between DELETE and TRUNCATE commands?
This SQL question compares two ways to remove data, focusing on performance and rollback capabilities. It tests practical database usage knowledge.
Why Interviewers Ask This
Choosing the right command affects performance and safety. Interviewers check if you understand the implications of row-by-row deletion versus table reset. This shows attention to detail in database maintenance tasks.
How to Answer This Question
Contrast DELETE (DML, row-by-row, triggers fired, rollbackable) with TRUNCATE (DDL, fast, no triggers, usually irreversible). Mention WHERE clause availability. Discuss transaction log usage. Explain when to use each based on volume and safety requirements.
Key Points to Cover
- DML vs DDL distinction
- Trigger behavior differences
- Performance implications
- Rollback capabilities
Sample Answer
DELETE is a DML command that removes rows one by one, allowing a WHERE clause to filter data. It fires triggers and logs each deletion, making it slower but fully rollbackable. TRUNCATE is a DDL command that deallocates…
Common Mistakes to Avoid
- Saying TRUNCATE deletes rows individually
- Ignoring trigger behavior
- Confusing syntax capabilities
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.