DELETE VS TRUNCATE VS DROP
| Aspect | DELETE | TRUNCATE | DROP |
|---|---|---|---|
| What it does | Removes selected rows | Removes all rows (empties table) | Removes the table object itself (schema + data) |
| WHERE clause | ✅ Yes | ❌ No | ❌ No |
| Speed | Slower (row-by-row) | Very fast (deallocate pages) | Instant (object removed) |
| Logging | Fully logged per row | Minimally logged (page dealloc) | DDL metadata change |
| Identity/Auto-increment | Unchanged |
Resets (SQL Server: to seed; PostgreSQL: RESTART IDENTITY optional; MySQL: resets to 1) |
N/A (object gone) |
| Constraints/Triggers | Fires DELETE triggers; respects FKs |
Usually doesn’t fire DELETE triggers; blocked if referenced by FKs (unless CASCADE supported) |
Drops constraints, indexes, triggers with the table |
| Transaction/rollback | DML → can rollback | DDL: PostgreSQL/SQL Server can rollback; MySQL does implicit commit (cannot rollback) | DDL: PostgreSQL/SQL Server can rollback; MySQL implicit commit |
| Space reclaim | Not immediate (pages remain until vacuum/shrink) | Frees data pages; resets high-water mark | Frees all space; removes metadata |
| Permissions |
DELETE on table |
Typically ALTER (or table owner) |
DROP privilege/ownership |
Typical usage
-
DELETE: remove specific rows or all rows while keeping identity values and firing business triggers.
DELETE FROM orders WHERE status = 'CANCELLED'; -- or DELETE FROM orders; -- all rows (slower than TRUNCATE) -
TRUNCATE: quickly empty a table (no WHERE), reset identity, keep schema.
-- SQL Server / Postgres / MySQL TRUNCATE TABLE orders; -- PG: add RESTART IDENTITY/CONTINUE IDENTITY -
DROP: remove the table completely.
DROP TABLE orders;
Foreign keys & gotchas
-
TRUNCATE is blocked if the table is referenced by a foreign key (SQL Server/MySQL).
PostgreSQL allowsTRUNCATE ... CASCADE(it will truncate dependent tables, so use carefully). -
Triggers:
TRUNCATEdoesn’t runDELETEtriggers (SQL Server/MySQL). PostgreSQL hasAFTER TRUNCATEtriggers if you define them. -
MySQL:
TRUNCATEandDROPare DDL and cause implicit commit—they cannot be rolled back. -
PostgreSQL/SQL Server: DDL is transactional—
TRUNCATE/DROPcan be rolled back inside an open transaction.
Quick decision
-
Need to remove some rows → DELETE (with
WHERE). -
Need to empty table fast and keep schema → TRUNCATE.
-
Need to remove table entirely → DROP.