Variables and SET statements in SQL - Time & Space Complexity

Time Complexity: Variables and SET statements

O(n)

Understanding Time Complexity

When using variables and SET statements in SQL, it's important to understand how the time to run these commands changes as the amount of data or operations grows.

We want to know how the execution time changes when we assign or update variables multiple times.

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

DECLARE @counter INT = 0;
DECLARE @max INT = 1000;

WHILE @counter < @max
BEGIN
  SET @counter = @counter + 1;
END
    

This code uses a variable to count from 0 up to 999 by repeatedly updating it inside a loop.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: The SET statement inside the WHILE loop that updates the variable.
• How many times: It runs once for each loop iteration, which is equal to the value of @max.

How Execution Grows With Input

As the number @max increases, the number of times the SET statement runs grows directly with it.

Input Size (n)	Approx. Operations
10	10 SET operations
100	100 SET operations
1000	1000 SET operations

Pattern observation: The number of operations grows in a straight line with the input size.

Final Time Complexity

Time Complexity: O(n)

This means the time to complete the variable updates grows directly in proportion to how many times you run the loop.

Common Mistake

[X] Wrong: "Setting a variable once inside a loop is always fast and does not affect performance."

[OK] Correct: Even though one SET is quick, doing it many times inside a loop adds up and the total time grows with the number of iterations.

Interview Connect

Understanding how variable assignments inside loops affect performance shows you can think about how SQL code runs, which is a useful skill in real projects and interviews.

Self-Check

"What if we replaced the WHILE loop with a single SET statement that calculates the final value directly? How would the time complexity change?"