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.

Practice

1. Which statement correctly describes the difference between DELETE and TRUNCATE in SQL?

easy

A. TRUNCATE is slower than DELETE because it logs each row removal.

B. TRUNCATE removes rows one by one and can use a WHERE clause; DELETE removes all rows quickly without logging each row.

C. DELETE and TRUNCATE both remove rows one by one and support WHERE clauses.

D. DELETE removes rows one by one and can use a WHERE clause; TRUNCATE removes all rows quickly without logging each row.

Solution

1. Step 1: Understand DELETE behavior

   DELETE removes rows individually and supports filtering with WHERE.

2. Step 2: Understand TRUNCATE behavior

   TRUNCATE removes all rows quickly without logging each row, and does not support WHERE.

3. Final Answer:

   DELETE removes rows one by one and can use a WHERE clause; TRUNCATE removes all rows quickly without logging each row. -> Option D

4. Quick Check:

   DELETE = selective row removal, TRUNCATE = fast full clear [OK]

Hint: DELETE filters rows; TRUNCATE clears all fast [OK]

Common Mistakes:

• Thinking TRUNCATE supports WHERE clause
• Confusing which command logs each row
• Believing DELETE is faster than TRUNCATE

2. Which of the following is the correct syntax to remove all rows from a table named Employees using TRUNCATE?

easy

A. DELETE FROM Employees;

B. TRUNCATE TABLE Employees;

C. TRUNCATE Employees WHERE id > 10;

D. REMOVE ALL FROM Employees;

Solution

1. Step 1: Recall TRUNCATE syntax

   The correct syntax to remove all rows quickly is TRUNCATE TABLE table_name;.

2. Step 2: Check options for correctness

   DELETE FROM Employees; uses DELETE; REMOVE ALL FROM Employees; is invalid SQL; TRUNCATE Employees WHERE id > 10; incorrectly adds WHERE to TRUNCATE; TRUNCATE TABLE Employees; matches the correct syntax.

3. Final Answer:

   TRUNCATE TABLE Employees; -> Option B

4. Quick Check:

   TRUNCATE syntax = TRUNCATE TABLE [OK]

Hint: TRUNCATE always uses TABLE keyword [OK]

Common Mistakes:

• Adding WHERE clause to TRUNCATE
• Using DELETE syntax for TRUNCATE
• Using non-existent REMOVE command

3. Given a table Orders with 1000 rows, what will be the number of rows after executing DELETE FROM Orders WHERE OrderDate < '2023-01-01'; if 300 rows have OrderDate before 2023?

medium

A. 700 rows remain

B. 0 rows remain

C. 1000 rows remain

D. Syntax error

Solution

1. Step 1: Understand DELETE with WHERE clause

   The DELETE command removes only rows matching the condition: OrderDate < '2023-01-01'.

2. Step 2: Calculate remaining rows

   Since 300 rows match the condition, they are deleted. Remaining rows = 1000 - 300 = 700.

3. Final Answer:

   700 rows remain -> Option A

4. Quick Check:

   1000 - 300 = 700 rows left [OK]

Hint: DELETE with WHERE removes only matching rows [OK]

Common Mistakes:

• Assuming DELETE removes all rows without WHERE
• Confusing DELETE with TRUNCATE behavior
• Thinking syntax error due to date format

4. You run the command TRUNCATE TABLE Customers WHERE Country = 'USA'; but get an error. What is the most likely cause?

medium

A. TRUNCATE does not support WHERE clauses.

B. The table Customers does not exist.

C. TRUNCATE requires a WHERE clause.

D. You must use DELETE instead of TRUNCATE for all tables.

Solution

1. Step 1: Check TRUNCATE command rules

   TRUNCATE removes all rows and does not allow filtering with WHERE.

2. Step 2: Identify error cause

   Using WHERE with TRUNCATE causes a syntax error because TRUNCATE does not support WHERE clauses.

3. Final Answer:

   TRUNCATE does not support WHERE clauses. -> Option A

4. Quick Check:

   TRUNCATE = no WHERE allowed [OK]

Hint: TRUNCATE never uses WHERE clause [OK]

Common Mistakes:

• Assuming TRUNCATE supports WHERE
• Thinking table absence causes this error
• Believing DELETE must always replace TRUNCATE

5. You want to quickly remove all rows from a large table Logs but keep the table structure and reset identity counters. Which command should you use and why?

hard

A. Use DELETE FROM Logs WHERE 1=1; because it is faster than TRUNCATE.

B. Use DELETE FROM Logs; because it removes rows and resets identity counters.

C. Use TRUNCATE TABLE Logs; because it removes all rows fast and resets identity counters.

D. Use DROP TABLE Logs; to remove all rows and keep structure.

Solution

1. Step 1: Identify command for fast full table clearing

   TRUNCATE TABLE removes all rows quickly and resets identity counters.

2. Step 2: Compare with DELETE and DROP

   DELETE removes rows slowly and does not reset identity counters; DROP removes table structure.

3. Final Answer:

   Use TRUNCATE TABLE Logs; because it removes all rows fast and resets identity counters. -> Option C

4. Quick Check:

   TRUNCATE = fast clear + reset identity [OK]

Hint: TRUNCATE clears fast and resets identity counters [OK]

Common Mistakes:

• Thinking DELETE resets identity counters
• Using DROP when structure must be kept
• Believing DELETE is faster than TRUNCATE