Learn the key differences between DELETE and TRUNCATE in SQL with visual examples, decision flowcharts, and common mistakes to avoid.
Jump into the SQLNoir case files and put these tips to work.
Need to remove data from a SQL table? The wrong choice between DELETE and TRUNCATE could mean lost data, performance issues, or a transaction you can't roll back. Here's how to choose wisely.
DELETE is surgical removal. It can target specific rows with a WHERE clause, logs each deletion, and can always be rolled back within a transaction.
TRUNCATE is the nuclear option. It removes ALL rows instantly by deallocating data pages, doesn't fire triggers, and usually cannot be rolled back.
| Feature | DELETE | TRUNCATE |
|---|---|---|
| Removes | Specific rows (WHERE) or all | All rows only |
| Speed | Slower (row-by-row) | Faster (deallocates pages) |
| Can Rollback? | Yes | No (in most databases) |
| Fires Triggers? | Yes | No |
| Resets Identity? | No | Yes |
Quick reference: DELETE vs TRUNCATE
DELETE is a DML (Data Manipulation Language) command. It works with transactions, fires triggers on each deleted row, and logs every deletion to the transaction log. Think of it as carefully removing specific evidence from a case file while keeping a record of what you took.
suspectsalibi_verified = trueAnatomy of a DELETE statement
The WHERE clause is what makes DELETE powerful. You can remove exactly what you need and nothing more:
-- Remove suspects with verified alibis DELETE FROM suspects WHERE alibi_verified = true; -- Remove witnesses from a solved case DELETE FROM witnesses WHERE case_id = 'CASE-2024-001' AND case_status = 'closed';
Because DELETE logs each row, you can wrap it in a transaction and roll back if something goes wrong:
BEGIN TRANSACTION; DELETE FROM evidence WHERE collected_date < '2020-01-01'; -- Oops, deleted too much! ROLLBACK; -- Or if it looks good: -- COMMIT;
With an explicit transaction, you can undo DELETE before committing.
Want to practice writing DELETE queries? SQLNoir's detective cases have you investigating crime scenes with real SQL.
TRUNCATE is a DDL (Data Definition Language) command. It doesn't delete rows one by one. Instead, it deallocates the data pages that hold your table's data. Think of it as burning the entire evidence room rather than removing files one at a time.
-- Clear all rows from a staging table TRUNCATE TABLE evidence_staging; -- The table structure remains, but all rows are gone
Key characteristics of TRUNCATE:
-- Before TRUNCATE: next ID would be 50,001
SELECT MAX(id) FROM suspects; -- Returns 50000
TRUNCATE TABLE suspects;
-- After TRUNCATE: next ID starts at 1
INSERT INTO suspects (name) VALUES ('New Suspect');
SELECT id FROM suspects; -- Returns 1TRUNCATE resets auto-increment. DELETE does not.
The fundamental difference is how each command removes data. DELETE works row by row, while TRUNCATE deallocates entire data pages.
How DELETE works: row-by-row processing with full logging
DELETE processes each matching row individually. That's why it's slower but more controlled.
| id | suspect_name | cleared | |----|--------------|--------| | 1 | Smith | false | | 2 | Jones | true | | 3 | Brown | false | | 4 | Davis | true |
| id | suspect_name | cleared | |----|--------------|--------| | 1 | Smith | false | | 3 | Brown | false | (2 rows deleted, can be rolled back)
DELETE removes specific rows based on your WHERE condition
TRUNCATE, by contrast, deallocates the data pages in one operation:
| id | suspect_name | cleared | |----|--------------|--------| | 1 | Smith | false | | 2 | Jones | true | | 3 | Brown | false | | 4 | Davis | true |
| id | suspect_name | cleared | |----|--------------|--------| (empty - all rows removed, identity reset to 1)
TRUNCATE removes ALL rows instantly by deallocating pages
Not sure which to use? Follow this decision tree:
If you answered YES to any of the first three questions, use DELETE. Otherwise, TRUNCATE is faster.
Think you understand SQL data manipulation? Put your detective skills to work solving mysteries with real queries.
Start InvestigationHere's the complete breakdown of how DELETE and TRUNCATE differ:
| Aspect | DELETE | TRUNCATE |
|---|---|---|
| Command Type | DML (Data Manipulation) | DDL (Data Definition) |
| WHERE Clause | ✅ Supported | ❌ Not supported |
| Rollback | ✅ Always possible | ⚠️ Database-dependent |
| Trigger Execution | ✅ Fires triggers | ❌ Does not fire triggers |
| Identity Reset | ❌ Keeps current value | ✅ Resets to seed |
| Speed | 🐢 Slower (row-by-row) | 🚀 Faster (page deallocation) |
| Transaction Log | Logs each row | Logs page deallocations only |
| Permissions | DELETE permission | ALTER permission |
| Foreign Keys | Works (may cascade) | Fails if referenced |
| Space Reclaim | May not reclaim | Immediately reclaims |
DELETE vs TRUNCATE: Complete 10-point comparison
A few of these deserve extra attention:
DELETE is DML because it manipulates data. TRUNCATE is DDL because it restructures the table (deallocates pages). This classification affects permissions, transaction behavior, and how replication tools handle each command.
If other tables reference your table via foreign keys, TRUNCATE will fail. You must either delete the referencing rows first, drop the constraints, or use DELETE instead.
-- This fails if other tables reference 'cases' TRUNCATE TABLE cases; -- ERROR: Cannot truncate a table referenced in a -- foreign key constraint -- Instead, delete from child tables first: DELETE FROM evidence WHERE case_id IN (SELECT id FROM cases); DELETE FROM suspects WHERE case_id IN (SELECT id FROM cases); DELETE FROM cases;
These are the traps developers fall into when choosing between DELETE and TRUNCATE:
TRUNCATE TABLE orders; -- ERROR: Cannot truncate a table -- referenced in a foreign key constraint
•TRUNCATE fails when other tables reference this one
•Must use DELETE or drop constraints first
-- Option 1: DELETE child rows first DELETE FROM order_items; DELETE FROM orders; -- Option 2: PostgreSQL CASCADE TRUNCATE TABLE orders CASCADE;
•DELETE works with foreign keys
•CASCADE option in PostgreSQL truncates dependent tables
Always check for foreign key constraints before using TRUNCATE
-- Audit trigger logs all deletions CREATE TRIGGER log_deletes ON suspects AFTER DELETE FOR EACH ROW INSERT INTO audit_log ...; -- This does NOT fire the trigger! TRUNCATE TABLE suspects;
•TRUNCATE bypasses all triggers
•Audit logs will be missing entries
-- Use DELETE if you need triggers DELETE FROM suspects; -- Each deleted row fires the trigger -- Audit log is complete
•DELETE fires AFTER DELETE triggers
•All rows logged to audit table
TRUNCATE bypasses triggers entirely
-- Clearing 10 million rows from staging table DELETE FROM staging_table; -- Takes 45 minutes, fills transaction log
•Row-by-row processing takes forever
•Transaction log grows massive
-- Same 10 million rows TRUNCATE TABLE staging_table; -- Takes 2 seconds
•Deallocates pages instantly
•Minimal log space required
For staging tables and full clears, TRUNCATE is orders of magnitude faster
The performance difference between DELETE and TRUNCATE becomes dramatic as table size grows.
100x
TRUNCATE can be 100x faster than DELETE on tables with millions of rows
Benchmarks on 10M row tables across PostgreSQL, MySQL, SQL Server
Why such a huge difference?
-- 10 million row table DELETE FROM large_table; -- PostgreSQL: ~45 minutes -- MySQL: ~30 minutes -- SQL Server: ~60 minutes TRUNCATE TABLE large_table; -- PostgreSQL: 1-2 seconds -- MySQL: 1-2 seconds -- SQL Server: 1-2 seconds
Actual times vary by hardware, but the order-of-magnitude difference is consistent.
Q1.You need to remove all orders from 2023, but keep 2024 orders. Which command?
Q2.Your staging table has 10 million rows and needs to be cleared before each import. Which is faster?
Q3.You accidentally ran DELETE FROM users (without WHERE) and want to roll back. Can you?
Q4.Your table has an auto-increment ID at 50,000. After removing all rows, you want new rows to start at 1. Which command?
Ready to put your SQL skills to the real test?
Solve murder mysteries using nothing but your SQL queries in SQLNoir. Completely free.
Start Your Investigation →TRUNCATE's rollback behavior varies by database. This is the most important difference to know:
| Database | TRUNCATE Rollback? | Notes |
|---|---|---|
| MySQL | ❌ No | Implicit COMMIT. Cannot be rolled back. |
| PostgreSQL | ✅ Yes | Can rollback within explicit transaction |
| SQL Server | ✅ Yes | Can rollback within explicit transaction |
| Oracle | ✅ Yes | Can rollback within explicit transaction |
| SQLite | N/A | No TRUNCATE command. Use DELETE. |
TRUNCATE rollback support by database
BEGIN; TRUNCATE TABLE suspects; -- Changed your mind? ROLLBACK; -- Data is restored in PostgreSQL!
PostgreSQL wraps TRUNCATE in the transaction. MySQL does not.
DELETE FROM table_name; instead. The optimizer treats it efficiently when there's no WHERE clause.No. TRUNCATE always removes ALL rows. If you need to filter which rows to remove, use DELETE.
DDL (Data Definition Language). It's treated like a table structure operation, not a data manipulation operation like DELETE.
TRUNCATE is faster, especially on large tables. DELETE processes row-by-row while TRUNCATE deallocates data pages in bulk. For tables with millions of rows, TRUNCATE can be 100x faster.
It depends on your database. PostgreSQL, SQL Server, and Oracle allow rollback within explicit transactions. MySQL does not. Always test in your specific environment.
No. Unlike DELETE, TRUNCATE does not execute any triggers defined on the table. If you have audit triggers or cascade logic, use DELETE instead.
Use DELETE when you need precision (WHERE clause), safety (rollback), or triggers to fire. It's the careful, surgical approach.
Use TRUNCATE when you're clearing all rows from a table and speed matters more than recoverability. Perfect for staging tables, test resets, and bulk operations.
Both commands have their place. Knowing when to use each is the mark of a developer who understands SQL at more than just syntax level.