🗑️ DELETE removes rows. TRUNCATE removes all rows.
Both remove data. DELETE can use WHERE, fires triggers. TRUNCATE is faster, resets identity, cannot use WHERE.
📝 DELETE
DELETE FROM users WHERE id = 123; DELETE FROM users WHERE status = 'inactive'; - Can use WHERE (selective) - Fires triggers - Slower (logs each row) - Identity not reset - Can be rolled back
⚡ TRUNCATE
TRUNCATE TABLE users; - No WHERE (removes ALL rows) - Does not fire triggers - Faster (deallocates data) - Resets identity - Can be rolled back (in transaction)
🎯 When to Use
✅ Use DELETE when: - Need to remove specific rows (WHERE) - Need triggers to fire - Want to keep identity values - Need to log each deletion ✅ Use TRUNCATE when: - Removing all rows - Need maximum performance - Want to reset identity - Don't need triggers - Need to free disk space
💡 Key Differences
- DELETE: Row-by-row, slower, can rollback
- TRUNCATE: All rows, faster, can rollback
- DELETE: Keeps identity seed
- TRUNCATE: Resets identity seed
- TRUNCATE cannot be used with foreign key references
“TRUNCATE is much faster than DELETE. Used TRUNCATE for test data. Identity reset automatically. Know the difference.”
