What is the difference between DELETE and TRUNCATE commands in SQL?
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 instantly by deallocating data pages, resetting auto-increment counters. It is faster but cannot be rolled back in many systems and does not fire triggers. For clearing a test table, TRUNCATE is preferred.
Common Mistakes to Avoid
- Saying TRUNCATE deletes rows one by one
- Ignoring trigger differences
- Confusing syntax requirements
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 RDBMS and DBMS?
Easy
FlipkartWhat is ER model in the DBMS?
Medium
FlipkartWhat is database normalization and why is it important?
Medium
FlipkartWhat is the difference between authentication and authorization?
Easy
FlipkartHow do you prioritize tasks when multiple deadlines are approaching?
Medium
Flipkart