SQL DELETE vs TRUNCATE: Key Differences

SQL DELETE vs TRUNCATE: Key Differences

Both DELETE and TRUNCATE remove rows from tables, but they work fundamentally differently and are suited to different scenarios.

DELETE

DELETE FROM orders
WHERE status = 'cancelled' AND order_date < '2024-01-01';

Characteristics:

• Removes rows one at a time (row-level operation)
• Supports WHERE clause for selective deletion
• Fires DELETE triggers for each row
• Fully logged (each deleted row is recorded in the transaction log)
• Can be rolled back within a transaction
• Does not reset auto-increment counters
• Slower for large-scale deletions

TRUNCATE

TRUNCATE TABLE temp_import_data;

Characteristics:

• Removes all rows by deallocating data pages (page-level operation)
• Cannot use WHERE clause (removes everything)
• Does not fire DELETE triggers in most databases
• Minimally logged (only page deallocations recorded)
• Rollback behavior varies by database (supported in PostgreSQL, not in MySQL)
• Resets auto-increment counters to the starting value
• Much faster for removing all rows

Comparison Table

Feature	DELETE	TRUNCATE
WHERE clause	Yes	No
Triggers	Fires	Does not fire
Logging	Full	Minimal
Rollback	Yes	Database-dependent
Speed (all rows)	Slow	Fast
Auto-increment	Preserved	Reset
Foreign keys	Checks per row	Blocked if referenced

When to Use DELETE

• Removing specific rows based on conditions
• When triggers must fire for audit trails
• When you need guaranteed rollback capability
• Deleting from tables with foreign key references

When to Use TRUNCATE

• Clearing staging or temporary tables
• Resetting test data between test runs
• Removing all data when triggers are not needed
• When performance matters and you need to clear millions of rows

Safe Deletion Pattern

DELETE FROM logs WHERE created_at < '2024-01-01' LIMIT 10000;

Batch deletion avoids long-running transactions. Repeat until zero rows are affected.

Understanding when to use DELETE versus TRUNCATE is key to managing data lifecycle efficiently while maintaining data integrity and audit requirements.