TRUNCATE vs DELETE in SQL — What's the Difference?
DELETE removes rows one at a time and can be filtered and rolled back; TRUNCATE instantly empties a whole table and resets auto-increment counters.
Published September 19, 2026
DELETE is a DML statement that removes rows matching a WHERE clause (or all rows if omitted), logging each row removal and supporting transactions/rollback. TRUNCATE is a DDL statement that instantly deallocates an entire table's data, resets auto-increment counters, and typically cannot be filtered or rolled back in most databases.
Common causes
- TRUNCATE is implemented as a fast deallocation of the table's storage rather than row-by-row deletion, which is why it's so much faster on large tables but offers less control
How to fix it
- Use DELETE FROM table WHERE ... when you need to remove specific rows or want the operation to be part of a rollback-able transaction
- Use TRUNCATE TABLE table when you need to instantly empty an entire table and don't need row-level filtering
- Be aware TRUNCATE also resets AUTO_INCREMENT/serial counters back to their starting value, which DELETE does not
Example
DELETE FROM logs WHERE created_at < '2020-01-01';
TRUNCATE TABLE temp_import_staging;FAQ
Can TRUNCATE be rolled back?
In most databases (including MySQL with InnoDB), TRUNCATE is auto-committing and cannot be rolled back once executed — treat it as irreversible.