DELETE vs TRUNCATE in SQL: When to Use Each (Visual Guide)

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.

The Quick Answer

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.

DELETE: Surgical Data Removal

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.

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;

Want to practice writing DELETE queries? SQLNoir's detective cases have you investigating crime scenes with real SQL.

TRUNCATE: The Nuclear Option

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:

• No WHERE clause - It removes ALL rows, no exceptions
• Resets identity columns - Auto-increment starts over at 1
• Does NOT fire triggers - No DELETE triggers execute
• Minimal logging - Only logs page deallocations, not individual rows

-- 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 1

Visual: What Actually Happens to Your Data

The fundamental difference is how each command removes data. DELETE works row by row, while TRUNCATE deallocates entire data pages.

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)

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)

When to Use DELETE vs TRUNCATE: Decision Flowchart

Not sure which to use? Follow this decision tree:

The 10 Key Differences

Here's the complete breakdown of how DELETE and TRUNCATE differ:

A few of these deserve extra attention:

DML vs DDL: Why It Matters

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.

Foreign Key Behavior

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;

Common Mistakes to Avoid

These are the traps developers fall into when choosing between DELETE and TRUNCATE:

Mistake 1: Using TRUNCATE on FK-Referenced Tables

TRUNCATE TABLE orders;

-- ERROR: Cannot truncate a table
-- referenced in a foreign key constraint

-- Option 1: DELETE child rows first
DELETE FROM order_items;
DELETE FROM orders;

-- Option 2: PostgreSQL CASCADE
TRUNCATE TABLE orders CASCADE;

Mistake 2: Expecting Triggers to Fire on 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;

-- Use DELETE if you need triggers
DELETE FROM suspects;

-- Each deleted row fires the trigger
-- Audit log is complete

Mistake 3: Using DELETE When TRUNCATE Would Be 100x Faster

-- Clearing 10 million rows from staging table
DELETE FROM staging_table;

-- Takes 45 minutes, fills transaction log

-- Same 10 million rows
TRUNCATE TABLE staging_table;

-- Takes 2 seconds

Performance: When TRUNCATE Really Matters

The performance difference between DELETE and TRUNCATE becomes dramatic as table size grows.

Why such a huge difference?

• DELETE is O(n) - Time scales linearly with row count. 10 million rows means 10 million row-level operations.
• TRUNCATE is O(1) - Constant time. It deallocates pages regardless of how many rows exist.

-- 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

Test Your Understanding

Database-Specific Behavior

TRUNCATE's rollback behavior varies by database. This is the most important difference to know:

BEGIN;

TRUNCATE TABLE suspects;

-- Changed your mind?
ROLLBACK;

-- Data is restored in PostgreSQL!

Frequently Asked Questions

The Bottom Line

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.