DELETE vs TRUNCATE behavior in SQL - Visual Side-by-Side Comparison

Concept Flow - DELETE vs TRUNCATE behavior

Start

↓

Choose Operation

↓

DELETE

↓

Row-by-row

↓

deletion

↓

Reset identity

↓

Log each row

↓

Can use WHERE

↓

Slower, can

↓

be rolled back

↓

End

This flow shows how DELETE and TRUNCATE differ: DELETE removes rows one by one with logging and can use conditions; TRUNCATE quickly removes all rows by deallocating pages with minimal logging.

Execution Sample

SQL

DELETE FROM Employees WHERE Department = 'Sales';
TRUNCATE TABLE Employees;

DELETE removes only Sales department rows one by one; TRUNCATE removes all rows instantly.

Execution Table

Step	Operation	Action	Rows Affected	Logging Type	Can Use WHERE	Rollback Possible
1	DELETE	Scan table for Department='Sales'	N rows matching condition	Full logging per row	Yes	Yes
2	DELETE	Delete each matching row	Rows deleted one by one	Full logging per row	Yes	Yes
3	DELETE	Finish operation	Only Sales rows removed	Full logging	Yes	Yes
4	TRUNCATE	Deallocate data pages	All rows removed	Minimal logging	No	Yes
5	TRUNCATE	Reset identity counters	Identity reset to seed	Minimal logging	No	Yes
6	TRUNCATE	Finish operation	Table empty	Minimal logging	No	Yes

💡 DELETE stops after removing matching rows; TRUNCATE stops after deallocating all data pages.

Variable Tracker

Variable	Start	After DELETE Step 1	After DELETE Step 2	After TRUNCATE Step 4	After TRUNCATE Step 5	Final
Rows in Employees	1000	1000	950 (if 50 Sales rows)	0	0	0
Identity Seed	1001	1001	1001	1001	1001	1001
Logging Type	None	Full per row	Full per row	Minimal	Minimal	Minimal
Rollback Capability	Yes	Yes	Yes	Yes	Yes	Yes

Key Moments - 3 Insights

Why can't TRUNCATE use a WHERE clause like DELETE?

TRUNCATE removes all rows by deallocating data pages directly, so it cannot filter rows; DELETE removes rows one by one and can filter with WHERE (see execution_table rows 1 and 4).

Why is DELETE slower than TRUNCATE?

DELETE logs each row deletion fully and processes rows individually, while TRUNCATE uses minimal logging and removes all data pages at once (see execution_table Logging Type and Rows Affected columns).

Can you roll back a TRUNCATE operation?

Yes, TRUNCATE can be rolled back within a transaction just like DELETE, although it uses minimal logging and deallocates pages directly, while DELETE logs each row fully (see Rollback Possible column).

Visual Quiz - 3 Questions

Look at the execution_table, at which step does DELETE remove rows one by one?

A Step 2

B Step 1

C Step 4

D Step 5

💡 Hint

Check the 'Action' column for DELETE operation steps in execution_table.

According to variable_tracker, what is the number of rows after TRUNCATE Step 4?

A 1000

B 0

C 950

D 50

💡 Hint

Look at 'Rows in Employees' variable after TRUNCATE Step 4 in variable_tracker.

If DELETE had no WHERE clause, how would the execution_table change?

A Logging type would change to minimal

B Rows affected would be zero

C Rows affected would be all rows, similar to TRUNCATE but with full logging

D Rollback would not be possible

💡 Hint

Consider DELETE behavior with and without WHERE clause from execution_table rows 1-3.

Concept Snapshot

DELETE removes rows one by one with full logging and supports WHERE and rollback.
TRUNCATE quickly removes all rows by deallocating pages with minimal logging.
DELETE can filter rows; TRUNCATE removes all rows.
TRUNCATE resets identity counters; DELETE does not.
Both DELETE and TRUNCATE can be rolled back within a transaction.
Use DELETE for selective removal, TRUNCATE for fast full table clearing.

Full Transcript

This visual execution compares DELETE and TRUNCATE in SQL. DELETE scans the table and deletes rows matching a condition one by one, logging each deletion fully and allowing rollback. TRUNCATE removes all rows instantly by deallocating data pages with minimal logging, resets identity counters, but cannot filter rows. The execution table shows each step's action, rows affected, logging type, and rollback capability. The variable tracker follows the number of rows, identity seed, logging type, and rollback possibility through the steps. Key moments clarify why TRUNCATE cannot use WHERE, why DELETE is slower, and rollback behavior. The quiz tests understanding of these steps and effects. The snapshot summarizes the main differences and use cases.