Updatable views and limitations in SQL - Time & Space Complexity

Time Complexity: Updatable views and limitations

O(n)

Understanding Time Complexity

When we update data through a view, the database must translate that update to the underlying tables.

We want to understand how the time to perform updates grows as the data size increases.

Scenario Under Consideration

Analyze the time complexity of updating a simple updatable view.

CREATE VIEW EmployeeView AS
SELECT EmployeeID, Name, DepartmentID
FROM Employees
WHERE DepartmentID = 10;

UPDATE EmployeeView
SET Name = 'Alex'
WHERE EmployeeID = 5;
    

This code updates the name of an employee in a view filtered by department.

Identify Repeating Operations

Look for repeated work the database does when processing the update.

• Primary operation: Searching the underlying Employees table for the matching EmployeeID.
• How many times: Once per update statement, but the search may scan rows depending on indexing.

How Execution Grows With Input

The time to find the employee depends on how the table is searched.

Input Size (n)	Approx. Operations
10	10 (if no index, scan all rows)
100	100 (full scan) or fewer if indexed
1000	1000 (full scan) or fewer if indexed

Pattern observation: Without an index, the search grows linearly with table size; with an index, it stays fast.

Final Time Complexity

Time Complexity: O(n)

This means the update time grows roughly in proportion to the number of rows in the underlying table when no index is used.

Common Mistake

[X] Wrong: "Updating a view always takes constant time regardless of table size."

[OK] Correct: The database must find the matching rows in the base table, which can take longer as the table grows if no index helps.

Interview Connect

Understanding how updates through views translate to base tables helps you explain database behavior clearly and shows you grasp practical data handling.

Self-Check

"What if the view joins multiple tables? How would that affect the time complexity of updates?"