CREATE VIEW syntax in SQL - Time & Space Complexity

Time Complexity: CREATE VIEW syntax

O(n)

Understanding Time Complexity

When we create a view in SQL, we define a saved query. Understanding how the time to run this query grows helps us know how it will perform as data grows.

We want to see how the cost of using a view changes when the underlying data gets bigger.

Scenario Under Consideration

Analyze the time complexity of this view creation and usage.

CREATE VIEW RecentOrders AS
SELECT OrderID, CustomerID, OrderDate
FROM Orders
WHERE OrderDate >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY);

SELECT * FROM RecentOrders;
    

This view selects orders from the last 30 days. When we query the view, it runs this filter on the Orders table.

Identify Repeating Operations

• Primary operation: Scanning the Orders table rows to check the OrderDate condition.
• How many times: Once for each row in Orders every time the view is queried.

How Execution Grows With Input

As the number of orders grows, the work to find recent orders grows too.

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

Pattern observation: The number of operations grows directly with the number of rows in the Orders table.

Final Time Complexity

Time Complexity: O(n)

This means the time to get results from the view grows linearly with the number of orders.

Common Mistake

[X] Wrong: "Creating a view makes the query run faster automatically."

[OK] Correct: A view just saves the query. The database still runs the full query each time, so the time depends on the data size and query complexity.

Interview Connect

Understanding how views work and their time cost shows you know how databases handle saved queries and data growth. This skill helps you write efficient queries and explain performance clearly.

Self-Check

"What if we added an index on OrderDate? How would that change the time complexity when querying the view?"