Introduction
SQL provides two commands for removing data from a table: DELETE, a Data Manipulation Language (DML) statement that removes rows one at a time based on a condition, and TRUNCATE, a Data Definition Language (DDL) statement that quickly empties an entire table by deallocating its storage. Although both remove data, they behave very differently in terms of logging, performance, rollback support, and side effects.
Cricket analogy: DELETE is like a scorer crossing out specific overs one by one after review, checking each entry against a rule; TRUNCATE is like wiping the entire scorebook clean at once to start a fresh innings, with no row-by-row review.
Syntax
-- Remove specific rows
DELETE FROM orders
WHERE status = 'cancelled';
-- Remove all rows but keep the table structure and log each row deletion
DELETE FROM orders;
-- Instantly empty the table, resetting identity/auto-increment counters
TRUNCATE TABLE orders;Explanation
DELETE removes rows one by one and evaluates the WHERE clause per row, so it fully supports filtering to remove only the rows you specify. Because it operates row-by-row, DELETE fires any triggers defined on the table, is fully logged in the transaction log, and can be rolled back within a transaction. TRUNCATE, by contrast, does not accept a WHERE clause; it always removes every row by deallocating the data pages that store the table's rows. It is minimally logged (typically just the page deallocations, not individual row deletions), which makes it much faster than an unfiltered DELETE on a large table. TRUNCATE also resets any identity or auto-increment counter back to its seed value, something DELETE never does. In many database engines, TRUNCATE cannot be used on a table that is referenced by a foreign key from another table unless that relationship is handled first, and in several engines it is not rollback-able once committed, or is only rollback-able within very limited transactional contexts.
Cricket analogy: DELETE reviews each ball against the WHERE condition and triggers a replay alert for every removed no-ball, fully logged so umpires can reverse a bad call; TRUNCATE can't selectively remove overs, wipes the whole innings instantly, resets the over counter to zero, and can't be undone once the umpire signals it.
Both DELETE and TRUNCATE are destructive. A DELETE statement without a WHERE clause removes every row in the table, and TRUNCATE always removes every row with no filtering option at all. Neither operation can be undone once committed on most systems, so always verify the target table and, for DELETE, the WHERE clause, before executing, and consider running inside a transaction you can roll back if something looks wrong.
Example
-- Row-by-row, filtered, logged, trigger-firing removal
DELETE FROM orders WHERE status = 'cancelled';
-- Fast full-table wipe, resets identity, minimally logged
TRUNCATE TABLE orders;
-- Attempting TRUNCATE on a table referenced by a foreign key
-- will fail in many engines unless the constraint is removed or handled:
-- TRUNCATE TABLE customers; -- may error if orders.customer_id references customers.idOutput
The DELETE statement removes only rows where status equals 'cancelled' and reports the exact number of rows deleted, e.g. '3 rows deleted', and any AFTER DELETE triggers execute once per removed row. The TRUNCATE statement empties the table entirely, executes almost instantly regardless of table size, and resets the next auto-increment value back to its starting point, but it does not report a per-row count and does not fire row-level triggers.
Cricket analogy: DELETE removes only the 'abandoned' matches from the season log and reports '3 matches deleted,' firing a stats-recalculation trigger for each one; TRUNCATE would wipe the entire season log instantly with no count and no per-match trigger firing.
Key Takeaways
- DELETE supports a WHERE clause and removes rows selectively; TRUNCATE always removes all rows and accepts no filter.
- DELETE is fully logged row-by-row and fires triggers; TRUNCATE is minimally logged and typically does not fire row-level triggers.
- DELETE can be rolled back inside a transaction on virtually all engines; TRUNCATE's rollback support is limited or unavailable depending on the database system.
- TRUNCATE resets identity/auto-increment counters to their seed value; DELETE does not reset them.
- TRUNCATE generally cannot be run on a table referenced by an active foreign key constraint without first addressing that relationship, while DELETE can respect referential integrity row by row.
Practice what you learned
1. Which of the following is a key difference between DELETE and TRUNCATE?
2. Why is TRUNCATE generally faster than an unfiltered DELETE on a large table?
3. What is a common restriction on using TRUNCATE?
4. What is the safest practice before running `DELETE FROM orders;` with no WHERE clause?
Was this page helpful?
You May Also Like
The INSERT Statement
Learn how to add new rows to a table using single-row, multi-row, and INSERT...SELECT forms.
The UPDATE Statement
Modify existing rows in a table using UPDATE with SET and WHERE, and understand the risks of a missing WHERE clause.
ACID Properties
The four guarantees—Atomicity, Consistency, Isolation, Durability—that make database transactions reliable.