Difference Between DELETE and TRUNCATE Commands in SQL
💡 Concept Name
DELETE and TRUNCATE are SQL commands used to remove data from tables, but they differ in behavior, performance, and use cases.
📘 Quick Intro
DELETE removes rows one by one and logs each deletion, allowing conditional removal with WHERE clauses. TRUNCATE quickly removes all rows by deallocating data pages, without logging individual row deletions.
🧠 Analogy / Short Story
Think of DELETE as carefully picking items out of a box one by one, which takes time and keeps track of each item removed. TRUNCATE is like dumping the entire box at once and throwing it away quickly without recording each item removed. DELETE is flexible but slower; TRUNCATE is fast but less flexible.
🔧 Technical Explanation
- DELETE: Removes rows individually and records each deletion in the transaction log.
- TRUNCATE: Removes all rows by deallocating entire data pages, logging minimal metadata.
- DELETE can use WHERE clauses to remove specific rows; TRUNCATE removes all rows without condition.
- TRUNCATE is faster but cannot be used when foreign key constraints exist without disabling them first.
- DELETE operations can be rolled back; TRUNCATE can be rolled back if used inside a transaction.
- TRUNCATE resets identity counters; DELETE does not.
🎯 Purpose & Use Case
- ✅ Use DELETE when you need to remove specific rows based on conditions.
- ✅ Use TRUNCATE when you want to quickly remove all rows from a table.
- ✅ DELETE for tables with foreign key references where TRUNCATE is restricted.
- ✅ TRUNCATE for large tables when you want to reset data efficiently.
💻 Real Code Examples
-- DELETE specific rows
DELETE FROM Employees WHERE Department = 'Sales';
-- TRUNCATE entire table
TRUNCATE TABLE Employees;
❓ Interview Q&A
Q1: Can DELETE be used with WHERE clause?
A: Yes, DELETE supports conditional deletion with WHERE.
Q2: Does TRUNCATE log individual row deletions?
A: No, TRUNCATE logs only page deallocations.
Q3: Can TRUNCATE be rolled back?
A: Yes, if used inside a transaction.
Q4: Does TRUNCATE reset identity counters?
A: Yes, it resets identity values.
Q5: Can TRUNCATE be used on tables with foreign key constraints?
A: No, unless constraints are disabled.
Q6: Which command is faster, DELETE or TRUNCATE?
A: TRUNCATE is faster due to minimal logging.
Q7: Does DELETE remove rows one by one?
A: Yes, it deletes rows individually.
Q8: Can DELETE remove all rows?
A: Yes, if no WHERE clause is specified.
Q9: Are triggers fired with DELETE and TRUNCATE?
A: DELETE fires triggers; TRUNCATE does not.
Q10: Which command would you use to remove all rows quickly?
A: TRUNCATE.
📝 MCQs
Q1. Which command can delete specific rows with conditions?
- DELETE
- TRUNCATE
- DROP
- ALTER
Q2. Which command removes all rows faster?
- DELETE
- TRUNCATE
- UPDATE
- INSERT
Q3. Does TRUNCATE reset identity columns?
- No
- Yes
- Sometimes
- Depends on DBMS
Q4. Can DELETE be rolled back?
- No
- Yes
- Only in some DBMS
- Only with triggers
Q5. Which command logs individual row deletions?
- DELETE
- TRUNCATE
- MERGE
- SELECT
Q6. Can TRUNCATE be used with WHERE clause?
- Yes
- No
- Sometimes
- Depends on DBMS
Q7. Does TRUNCATE fire triggers?
- Yes
- No
- Only INSTEAD OF triggers
- Only AFTER triggers
Q8. Which command can be used on tables with foreign key constraints without disabling them?
- DELETE
- TRUNCATE
- DROP
- ALTER
Q9. Is TRUNCATE faster than DELETE?
- No
- Yes
- Only for small tables
- Depends on conditions
Q10. Does DELETE remove rows one by one?
- Yes
- No
- Depends on DBMS
- Only with WHERE clause
💡 Bonus Insight
TRUNCATE is ideal for quickly clearing large tables, but remember it cannot be used if foreign key constraints exist unless you disable them temporarily. DELETE offers fine-grained control but is slower due to logging and trigger firing.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!