Order of execution in SQL query?

Question

The following query is a contrived example that demonstrates a bug I found in a stored procedure this week.

CREATE TABLE #temp
(
    ID int IDENTITY(1,1),
    Value char(1)
)

INSERT INTO #temp(Value) Values('a')
INSERT INTO #temp(Value) Values('b')
INSERT INTO #temp(Value) Values('c')
INSERT INTO #temp(Value) Values('d')

DECLARE
    @i int,
    @ID int,
    @Count int,
    @Value char(1)

SELECT @Count = COUNT(*) FROM #temp
SET @i = 1
SET @ID = 2

WHILE @i < @Count
BEGIN
    SELECT 
        @ID = ID,
        @Value = (SELECT Value FROM #temp WHERE ID = @ID)
    FROM
        #temp
    WHERE
        @i = ID

    PRINT @Value

    SET @i = @i + 1
END

At first glance the output should be a b c d but it isn't! It's b b c d. So the order of execution within a statement is not what we might assume it to be.

Is there a specific order of execution that can be relied on?

Answer 1

In SQL, all expressions that appear in the same logical query processing phase are evaluated as if at the same point in time.

By this I mean, we cannot say that @ID= ID will be evaluated first, and then

@Value = (SELECT Value FROM #temp WHERE ID = @ID).

Besides, the order of execution of clauses is :

FROM

WHERE

GROUP BY

HAVING

SELECT

ORDER BY

That's why the aliases of the SELECT clause can only be referenced in the ORDER BY clause.

Cheers.

Answer 2

I'm surprised that it prints four letters on your system, @i < @Count should limit the output to 3 rows.

You can usually rely on column assignments being evaluated in order. But a subquery can be evaluated in a different order (or even be converted into a join for efficiency.) If you separate the assignments from the query using a subquery:

SELECT  @ID = ID
,       @Value = TheVal
FROM    (
        SELECT  ID
        ,       (SELECT Value FROM #temp WHERE ID = @ID) as TheVal
        FROM    #temp
        WHERE   @i = ID
        ) sub

The result should be reliable. Here, it's b a b because it starts with @id = 2, and then increases it with every loop after updating the TheVal value.

Relying on this kind of tricks makes for code that is hard to maintain. It can sometimes be necessary for performance, but if it isn't, try to write for clarity :)

Answer 3

The WHERE clause in the line

@Value = (SELECT Value FROM #temp WHERE ID = @ID)

is uncorrelated with the WHERE clause here

    #temp
WHERE
    @i = ID

So, 1st loop

• @i = 1, @ID = 2
• In #temp, @ID = 2 so you get b
• You then assign @ID with 1

2nd loop

• @i = 2, @ID = 1
• In #temp, @ID = 1 so you get a
• You then assign @ID with 2

3rd loop

• @i = 3, @ID = 2
• In #temp, @ID = 2 so you get b
• You then assign @ID with 3

Then it stops because of @i < @count

SQL has no "order of execution" as such because it's declarative. The SELECT clause is evalauted in one go, there is no expectation that says @ID will be assigned before it uses on the next line.

Answer 4

Not sure what you are trying to achieve, and how you get your results.

I pasted your code into MSSQL 2008, and got

b a b

First, you have a "less than Count" comparison, resulting in only 3 records.

Next, you have two contradicting WHERE clauses, one with ID and one with i. The SELECT updates the ID.

Remove the subselect, and change to <= @Count, and you should get "a b c d"

Answer 5

Let's take a look closer to your query:

1. The first run:

   SELECT @ID = ID, @Value = (SELECT Value FROM #temp WHERE ID = @ID) FROM #temp WHERE @i = ID

Here

 @i=1
   @ID=2

so after

 @Value = (SELECT Value FROM #temp WHERE ID = @ID)

we get:

 @Value = 'b'

and the @ID value will equal 1 because of @ID=ID, since ID= 1 is the only value available after the

WHERE @i = ID

is executed.

1. The second run

   @i=2 @ID=1

This run will print

'a'

3 . The third run @id=3 @ID=2

this run will print

'b'

Then the loop terminates !

that's why you got 'b' 'a' 'b'