Views for security and abstraction in SQL - Time & Space Complexity

Time Complexity: Views for security and abstraction

O(n)

Understanding Time Complexity

When using views in databases, it's important to understand how the time to get results changes as data grows.

We want to know how the database handles queries on views as the underlying tables get bigger.

Scenario Under Consideration

Analyze the time complexity of this view query.

CREATE VIEW EmployeeNames AS
SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE Active = 1;

SELECT * FROM EmployeeNames WHERE LastName = 'Smith';
    

This view shows active employees' names. The query fetches all active employees named 'Smith'.

Identify Repeating Operations

Look at what repeats when the query runs.

• Primary operation: Scanning the Employees table rows to find active employees.
• How many times: Once per query, but it checks each row in the Employees table.

How Execution Grows With Input

As the Employees table grows, the work to find matching rows grows too.

Input Size (n)	Approx. Operations
10	About 10 row checks
100	About 100 row checks
1000	About 1000 row checks

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

Final Time Complexity

Time Complexity: O(n)

This means the time to get results grows linearly with the number of rows in the Employees table.

Common Mistake

[X] Wrong: "Using a view makes queries instantly faster regardless of data size."

[OK] Correct: Views are saved queries, not stored results. The database still processes the underlying data each time, so bigger tables mean more work.

Interview Connect

Understanding how views affect query time helps you explain database design choices clearly and confidently in interviews.

Self-Check

What if we added an index on the LastName column? How would the time complexity change?