TRUNCATE vs DELETE vs DROP in SQL - Performance Comparison

Time Complexity: TRUNCATE vs DELETE vs DROP

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

Understanding Time Complexity

When removing data or tables in SQL, different commands work differently and take different amounts of time.

We want to understand how the time to run these commands changes as the amount of data grows.

Scenario Under Consideration

Analyze the time complexity of these SQL commands:

-- Delete all rows from a table
DELETE FROM employees;

-- Quickly remove all rows from a table
TRUNCATE TABLE employees;

-- Remove the entire table and its data
DROP TABLE employees;
    

These commands remove data or the table itself in different ways.

Identify Repeating Operations

Look at what each command does repeatedly:

• DELETE: Scans and removes each row one by one.
• TRUNCATE: Removes all rows by quickly resetting storage, no row-by-row work.
• DROP: Removes the whole table structure instantly, no row processing.
• Dominant operation: For DELETE, the row-by-row removal is the main repeated work.

How Execution Grows With Input

How time changes as the number of rows (n) grows:

Input Size (n)	DELETE Operations	TRUNCATE & DROP Operations
10	About 10 row removals	Almost constant time
100	About 100 row removals	Almost constant time
1000	About 1000 row removals	Almost constant time

DELETE time grows with the number of rows, but TRUNCATE and DROP stay fast no matter how many rows there are.

Final Time Complexity

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

DELETE takes longer as more rows exist, while TRUNCATE and DROP finish quickly regardless of size.

Common Mistake

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

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

Interview Connect

Knowing how these commands behave helps you explain efficient ways to clear data or remove tables in real projects.

Self-Check

"What if DELETE had a WHERE clause that only removed a few rows? How would that change the time complexity?"