What is the difference between TRUNCATE and DELETE in SQL?
❓ Interview Prompt: Compare `TRUNCATE TABLE` (DDL, fast drop/recreate, resets auto-increment) vs `DELETE FROM` (DML, row-by-row logging, rollback support).
Comparing table clearing commands:
| Property | 'DELETE FROM table' | 'TRUNCATE TABLE table' |
|---|---|---|
| Command Type | DML (Data Manipulation Language) | DDL (Data Definition Language) |
| Execution | Deletes rows one-by-one with transaction logging | Drops and recreates the underlying table structure |
| Speed | Slower on large datasets | Extremely Fast ($O(1)$ allocation drop) |
| Auto-Increment | Preserves auto-increment counter | Resets auto-increment counter to 1 |
| WHERE Clause | Supported ('WHERE id > 10') | Not Supported (Clears entire table) |
Code Implementation Example
-- Fast table wipe resetting auto-increment counters
TRUNCATE TABLE temp_import_logs;Use TRUNCATE TABLE to empty entire staging tables quickly, and DELETE FROM when filtering specific rows or needing transactional rollbacks.