Quick comparison
| DELETE | TRUNCATE | DROP | |
|---|---|---|---|
| Category | DML | DDL (in most databases) | DDL |
| Can filter with WHERE? | Yes | No | No |
| Removes | Matching rows | All rows | The entire table |
| Table structure afterward | Unchanged | Unchanged, empty | Gone entirely |
| Resets auto-increment/identity? | No | Usually, yes | N/A — table is gone |
| Fires row-level triggers? | Yes | Usually no | N/A |
| Relative speed on large tables | Slow (per-row) | Very fast | Very fast |
DELETE: row-by-row, filterable, logged
DELETE is a DML (Data Manipulation Language) statement — it removes rows, optionally filtered, and every removed row is individually logged, which is what makes it fully rollback-able:
DELETE FROM orders
WHERE status = 'cancelled'
AND order_date < '2025-01-01';The table, its columns, indexes, and constraints are completely untouched afterward — only the matching rows are gone.
TRUNCATE: fast, all rows, resets identity
TRUNCATE removes every row in one operation by deallocating the data pages the table uses, rather than deleting rows one at a time — no WHERE clause is possible, since it's all-or-nothing by design:
TRUNCATE TABLE staging_orders;
-- Every row gone, instantly, regardless of table size
-- Auto-increment / identity counter typically resets to its starting valueThe table structure survives — it's still there, just empty, ready to be inserted into again from a clean slate (including, in most databases, a reset ID sequence).
DROP: removes the table itself
DROP removes the table object entirely — not just its contents, but its column definitions, indexes, constraints, and the table's existence in the schema:
DROP TABLE staging_orders;
-- The table no longer exists at all — a subsequent SELECT errors with "table not found"DROP, there is no table to recreate rows into — the schema itself is gone. Recovering from this without a backup is not possible in most databases.Can each be rolled back?
| Command | Rollback behavior |
|---|---|
| DELETE | Always rollback-able inside an open transaction, in every mainstream database |
| TRUNCATE | Rollback-able inside a transaction in PostgreSQL and SQL Server; may auto-commit and be unrecoverable in some MySQL storage engine configurations |
| DROP | Rollback-able inside a transaction in PostgreSQL and SQL Server (which support transactional DDL); frequently auto-commits immediately in MySQL |
The safest practice, regardless of database: run any of these three inside an explicit transaction, verify the result, and only then COMMIT — and check the specific database's documentation on transactional DDL before assuming a rollback will work.
Performance comparison
On a table with 50 million rows, removing every row:
| Command | Why it's this fast (or slow) |
|---|---|
| DELETE FROM orders; | Slow — each of the 50 million rows is individually logged for potential rollback |
| TRUNCATE TABLE orders; | Fast — deallocates entire data pages at once, minimal per-row logging |
| DROP TABLE orders; | Fast — removes the table's storage allocation directly, no row-level work at all |
If the goal is genuinely "empty this table completely," TRUNCATE is almost always the right choice over an unfiltered DELETE — same end result, dramatically less work.
Common mistakes
- Using DELETE with no WHERE clause when TRUNCATE was the actual intent. Functionally similar result, but far slower and more heavily logged than necessary.
- Assuming TRUNCATE always rolls back like DELETE. This depends heavily on the database and storage engine — verify rather than assume.
- Running DROP when TRUNCATE was meant. The two look similar in casual conversation ("clear out the table") but DROP destroys the table definition itself — a much more serious and often irreversible action.
- Expecting a DELETE trigger to fire on a TRUNCATE. Application logic that depends on row-level delete triggers won't run during a TRUNCATE in most databases.
- Forgetting that DELETE doesn't reset auto-increment — a table that's had all rows deleted and then repopulated will have IDs continuing from where they left off, not restarting at 1.
Key takeaways
- DELETE is filterable and fully logged — the safest and most flexible, but slowest on large tables.
- TRUNCATE empties a table instantly but can't be filtered and typically resets identity counters.
- DROP removes the table itself — structure, indexes, and constraints all disappear, not just the data.
- Rollback support for TRUNCATE and DROP varies significantly by database — verify before relying on it.
- Match the command to the actual intent: filtered removal (DELETE), full reset of a table that should still exist (TRUNCATE), or genuinely removing the table (DROP).