DROP TABLE and ALTER TABLE in SQL - Time & Space Complexity

Time Complexity: DROP TABLE and ALTER TABLE

O(n)

Understanding Time Complexity

When we change or remove tables in a database, it takes some time to do so.

We want to understand how this time grows as the table size or structure changes.

Scenario Under Consideration

Analyze the time complexity of these SQL commands.

ALTER TABLE employees
ADD COLUMN birthdate DATE;

ALTER TABLE employees
DROP COLUMN middle_name;

ALTER TABLE employees
RENAME TO staff;

DROP TABLE staff;

This code removes a table and changes its structure by adding and dropping columns and renaming the table.

Identify Repeating Operations

Look for repeated work inside these commands.

• Primary operation: Scanning or updating all rows when changing table structure.
• How many times: Once per command, but some commands may touch every row.

How Execution Grows With Input

Changing or dropping a table depends on how many rows it has.

Input Size (rows)	Approx. Operations
10	About 10 row updates or deletions
100	About 100 row updates or deletions
1000	About 1000 row updates or deletions

Pattern observation: The work grows roughly in direct proportion to the number of rows.

Final Time Complexity

Time Complexity: O(n)

This means the time to drop or alter a table grows roughly in step with the number of rows in the table.

Common Mistake

[X] Wrong: "Dropping or altering a table always happens instantly, no matter the size."

[OK] Correct: The database often needs to update or remove every row, so bigger tables take more time.

Interview Connect

Understanding how table changes scale helps you explain database behavior clearly and confidently.

Self-Check

"What if we only rename a table without changing its data? How would the time complexity change?"