Q7: What is the difference between DELETE, TRUNCATE, and DROP commands?
These commands clear records or schema definitions with varying performance and transactional characteristics.
DELETE vs TRUNCATE vs DROP
DELETE:
DML | Row-by-Row | Slow | RollbackableFires triggers, logs individual row deletions in transaction WAL.
TRUNCATE:
DDL | Page Allocation Reset | FastDeallocates data pages directly, bypasses row triggers.
DROP:
DDL | Schema Removal | DestructiveRemoves data, indexes, constraints, and table metadata from dictionary.
Key Takeaways
- TRUNCATE is a high-performance DDL operation that resets identity seeds and deallocates data pages.
- DELETE supports conditional WHERE filtering and logs individual row removals.