Skip to content

DROP vs TRUNCATE TABLE 🗑️

Prerequisites: CREATE TABLE

Mentor's Note: This is a classic interview topic. "How do I empty a table?" The answer depends on whether you want to keep the table structure or destroy it entirely. 💡

🌟 The Scenario: The Office Cleanup 🏢

  1. DELETE: Taking files out of the cabinet one by one. (Slow, Detailed).
  2. TRUNCATE: Dumping the entire cabinet contents into the bin instantly. (Fast, keeps the cabinet).
  3. DROP: Throwing the cabinet itself into the crusher. (Destroys everything).

💻 1. DROP TABLE (Destruction) 💣

Removes the table definition AND all data.

DROP TABLE students;
  • Result: The table no longer exists.
  • Rollback?: No (Usually).

💻 2. TRUNCATE TABLE (Reset) ⚡

Removes all data but keeps the table structure.

TRUNCATE TABLE students;
  • Result: Table is empty (0 rows), but columns remain.
  • Performance: Much faster than DELETE because it doesn't log individual row removals.
  • Identity/Auto-Increment: Resets to 1.

📊 Comparison: DELETE vs TRUNCATE vs DROP

Feature DELETE TRUNCATE DROP
Type DML (Data) DDL (Definition) DDL (Definition)
Speed Slow (Row-by-row) Fast (Page deallocation) Instant
Structure Kept Kept Removed
Where Clause Yes (Can filter) No (All or nothing) No
Rollback Yes No (In most DBs) No

💡 Pro Tip

"Use TRUNCATE when you need to refresh a staging table or clear test data. Use DELETE when you need to remove specific users. Use DROP only when the feature is deprecated."

📈 Learning Path