What is the difference between DELETE and TRUNCATE command?
Compares row-by-row deletion with table reset, focusing on performance and rollback capabilities.
Why Interviewers Ask This
Understanding these commands is essential for database maintenance. Interviewers check if you know the performance implications and transactional differences.
How to Answer This Question
Contrast DELETE (DML, row-by-row, loggable, slower) with TRUNCATE (DDL, fast, resets identity, less logging). Mention WHERE clause capability in DELETE. Discuss rollback possibilities and trigger firing differences.
Key Points to Cover
- Row vs Table level
- Logging differences
- Rollback capability
- Performance impact
Sample Answer
DELETE removes specific rows and logs each deletion, allowing rollback and firing triggers. TRUNCATE removes all rows instantly, resetting the table structure and identity columns, but cannot be rolled back easily in some databases. DELETE is safer for selective removal, while TRUNCATE is faster for clearing entire tables.
Common Mistakes to Avoid
- Thinking TRUNCATE deletes data permanently
- Ignoring trigger behavior
- Confusing DDL and DML classifications
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 DELETE and TRUNCATE commands in SQL?
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