SELECT assigns a value to local variable and refers it again. Are the results guaranteed by SQL Server?

Question

The SELECT assigns value to @v1 and uses @v1 to compute @v2 in the same statement. The results are @v1 = 100 and @v2=105.

Are these results always guaranteed by SQL Server? Is it guaranteed by SQL server that when it is referring to @v1 for computing @v2, all the side effects of @v1 = 100 are complete and SQL server indeed read @v1 after all the side effects were complete?

Any corner cases where SQL server might give different results?

declare
    @v1 int,
    @v2 int

-- Let's keep the order of assignment as it is listed here.
select
    @v1 = 100,
    @v2 = @v1 + 5

select
    @v1 as '@v1',
    @v2 as '@v2'

I looked up MSDN, but find anything around this.

Aside: There is a guarantee provided by [`update`](https://learn.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql): "SET @variable = column = expression sets the variable to the same value as the column. This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column." This allows for handy things like `update Foo set @Before = Bar, @After = Bar += @Offset where FooId = 42;`. — HABO, Oct 17 '17 at 20:32

Radim Bača · Answer 1 · 2017-10-18T08:04:59.907

3

Here you can find a nice demonstration showing that the evaluation order of expressions behind SELECT is never guaranteed. I guess that such simple statements will be always evaluated linearly by SQL Server. However, if you have the following statements

declare
    @v1 int = 0,
    @v2 int = 0

select
    @v1 = 100,
    @v2 = (SELECT @v1 + 5)

select
    @v1 as '@v1',
    @v2 as '@v2'

then the result is

@v1  @v2
100  5

That is a reason why assigning values into variables in SELECT instead of SET is considered to be a bad habbit.

EDIT:

As mentioned in the discussion, there is no other way to evaluate my query than to run the subquery first. Therefore, the order of expression evaluation is not a matter of optimizer choice anymore. On the other hand, the main message remains the same. The order of expression evaluation is not guaranteed and we should not rely on it in our SQL code.

edited Oct 18 '17 at 08:04

answered Oct 17 '17 at 18:22

Radim Bača

10,646
1
19
33

This answer is somewhat misleading IMO; the evaluation order of these selects is 100% deterministic as the "inner" select in the second statement is required to be evaluated before the "outer" one in the first can be analyzed. In effect, the inner `SELECT` creates a [closure](https://en.wikipedia.org/wiki/Closure_(computer_programming)) around the original value of `@v1` which is sometimes a very desirable characteristic. – Kittoes0124 Oct 17 '17 at 19:39
@Kittoes0124 it is not about the order of selects, but about the order of expressions behind SELECT. If you read the text from Training Kit 70-461 here: https://stackoverflow.com/questions/45367109/sql-assign-variable-with-subquery then it is clear that the expression order evaluation is not deterministic. – Radim Bača Oct 17 '17 at 19:51
Your particular example IS all about the order of selects though; expression evaluation has nothing to do with the effects we see in it because the order of the select statements is what ultimately determines that the result is 5 instead of 105. – Kittoes0124 Oct 17 '17 at 19:52
@Kittoes0124 you are right. I guess the reasons for the evaluation order change have to be cost-related. However, my example simply forces optimizer to evaluate the subquery first. – Radim Bača Oct 17 '17 at 20:12
Aye, the optimizer has no choice because it's a subquery which, by definition, has to be evaluated before the "main" query. I think it's a decent example of a potential edge case that the OP asked about but not necessarily for the reasons you stated. – Kittoes0124 Oct 17 '17 at 20:17

Damien_The_Unbeliever · Accepted Answer · 2017-10-18T08:20:37.697

Hidden in the wrong piece of documentation (it should, in my opinion, by in the SELECT @local_variable documentation) is this little gem:

all expressions in the SELECT list (including assignments) are not guaranteed to be executed exactly once for each output row.

That, in itself, should give you pause¹. I can't find anything specifically about the order of evaluation within a row but in the general case, no guarantees are given over such evaluation orders (when not using this MS extension to SQL for assignment, you're not allowed to have expressions that depend upon other expressions within the same SELECT clause since they're expected to be evaluated "as if" they're evaluated in parallel).

I wouldn't write code the assumes an assignment/evaluation order within a single statement.

It should also be noted, from the documentation that you linked to:

For assigning variables, we recommend that you use SET @local_variable instead of SELECT @local_variable.

Where, of course, no such question rears its head since SET only allows a single assignment.

¹The specific warning is one around where the right-hand side of the assignment contains the variable being assigned to, which we do not have here.

SELECT assigns a value to local variable and refers it again. Are the results guaranteed by SQL Server?

2 Answers2