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 data pages instantly, removing all rows without logging individual deletions. It cannot use a WHERE clause and typically resets auto-increment counters. Use DELETE for selective removal and TRUNCATE for clearing entire tables quickly.
Common Mistakes to Avoid
- Saying TRUNCATE deletes rows individually
- Ignoring trigger behavior
- Confusing syntax capabilities
Practice This Question with AI
Answer this question orally or via text and get instant AI-powered feedback on your response quality, structure, and delivery.
Related Interview Questions
What is the difference between LIKE and equals operators in SQL?
Easy
TCSWhat is the difference between UNION and UNION ALL?
Easy
Describe a PRIMARY KEY and how it differs from a UNIQUE key
Medium
What is ER model in the DBMS?
Medium
FlipkartWhat is GUI and how does it differ from CLI?
Easy
FlipkartDefine Bridge in Computer Networks
Easy
Flipkart