DELETE vs TRUNCATE behavior in SQL - Performance Comparison

Time Complexity: DELETE vs TRUNCATE behavior

O(n) for DELETE, O(1) for TRUNCATE

Understanding Time Complexity

When removing data from a table, the way the database handles the operation affects how long it takes.

We want to understand how the time to delete rows grows as the table gets bigger.

Scenario Under Consideration

Analyze the time complexity of these two commands:

DELETE FROM employees WHERE department_id = 5;

TRUNCATE TABLE employees;
    

The first removes rows matching a condition one by one; the second quickly removes all rows at once.

Identify Repeating Operations

Look at what repeats during each command:

• Primary operation for DELETE: Scanning and deleting each matching row individually.
• How many times: Once for each row that matches the condition.
• Primary operation for TRUNCATE: Quickly removing all rows by resetting storage without row-by-row deletion.
• How many times: Essentially once, no matter how many rows.

How Execution Grows With Input

As the number of rows increases:

Input Size (rows)	DELETE Operations	TRUNCATE Operations
10	About 10 row deletions	1 quick operation
100	About 100 row deletions	1 quick operation
1000	About 1000 row deletions	1 quick operation

DELETE time grows with the number of rows deleted; TRUNCATE time stays almost the same.

Final Time Complexity

Time Complexity: O(n) for DELETE, O(1) for TRUNCATE

DELETE takes longer as more rows match; TRUNCATE removes all rows quickly regardless of size.

Common Mistake

[X] Wrong: "DELETE and TRUNCATE take the same time because they both remove data."

[OK] Correct: DELETE removes rows one by one, which takes longer as rows increase, while TRUNCATE quickly resets the table without row-by-row work.

Interview Connect

Understanding how different commands scale helps you explain database behavior clearly and choose the right tool for fast data removal.

Self-Check

What if DELETE had no WHERE clause and removed all rows? How would its time complexity compare to TRUNCATE?