Difference between DELETE vs TRUNCATE vs DROP.

Question

Anubhav Sharma · Answer

Here’s a clear, short comparison of DELETE vs TRUNCATE vs DROP in SQL: 1. DELETE Removes specific rows (can use WHERE)Row-by-row operationCan be rolled back (inside transaction)Triggers are firedTable structure remains intact Example: 2. TRUNCATE Removes all rows from table (no WHERE)Faster (minimal logging)Cannot be rolled back (in most cases)Does NOT fire triggersResets identity (auto-increment)Table structure remains intact Example: 3. DROP Deletes entire tableRemoves data + structureCannot be rolled backFrees all spaceTable no longer exists Example: Quick Comparison Table Feature DELETE TRUNCATE DROP Remove Rows Yes (specific) Yes (all) Yes (all) WHERE Clause Yes No No Rollback Yes No* No Speed Slow Fast Fast Triggers Yes No No Structure Exists Yes Yes No When to Use Use DELETE → when you need control (filter rows)Use TRUNCATE → when clearing full table quicklyUse DROP → when you don’t need the table anymore

Feature	DELETE	TRUNCATE	DROP
Remove Rows	Yes (specific)	Yes (all)	Yes (all)
WHERE Clause	Yes	No	No
Rollback	Yes	No*	No
Speed	Slow	Fast	Fast
Triggers	Yes	No	No
Structure Exists	Yes	Yes	No

interview

Difference between DELETE vs TRUNCATE vs DROP.

Can you answer this question?

1 Answers

1. DELETE

2. TRUNCATE

3. DROP

Quick Comparison Table

When to Use