TRUNCATEDML
Remove all rows from a table instantly, without logging individual row deletions.
Efficiently removes all rows from a table by deallocating pages rather than deleting row-by-row. Much faster than DELETE for full table clears. Cannot be rolled back in some databases without explicit transaction handling; check your engine.
| Database System | Support Status | Since Version | Notes |
|---|---|---|---|
| MySQL | ✓ Supported | all | Resets AUTO_INCREMENT counter to its starting value. Cannot be rolled back in InnoDB without an explicit transaction. |
| PostgreSQL | ✓ Supported | all | Uniquely transactional — can be rolled back within a transaction. Supports RESTART IDENTITY / CONTINUE IDENTITY (sequence handling) and CASCADE / RESTRICT (foreign key handling). |
| SQL Server | ✓ Supported | all | Resets identity counter. Cannot truncate a table referenced by an active foreign key constraint. |
| Oracle | ✓ Supported | all | DDL operation — implicitly commits the current transaction. Cannot be rolled back. |
| SQLite | ✗ Not Supported | — | No TRUNCATE command. Use DELETE FROM tbl (no WHERE clause). Reclaim space with VACUUM. |
Much faster than DELETE on large tables because it deallocates data pages rather than logging row-by-row. PostgreSQL is unique in making TRUNCATE fully transactional. SQLite lacks the command entirely.
Resets AUTO_INCREMENT counter to its starting value. Cannot be rolled back in InnoDB without an explicit transaction.
Uniquely transactional — can be rolled back within a transaction. Supports RESTART IDENTITY / CONTINUE IDENTITY (sequence handling) and CASCADE / RESTRICT (foreign key handling).
Resets identity counter. Cannot truncate a table referenced by an active foreign key constraint.
DDL operation — implicitly commits the current transaction. Cannot be rolled back.
No TRUNCATE command. Use DELETE FROM tbl (no WHERE clause). Reclaim space with VACUUM.