Order of Execution in SQL Server Variable Assignment Using Select

Order of execution in SQL Server variable assignment using SELECT

For variable assignment, Martin Smith answers this question here referencing MSDN:

If there are multiple assignment clauses in a single SELECT statement,
SQL Server does not guarantee the order of evaluation of the
expressions. Note that effects are only visible if there are
references among the assignments.

But...

If we're dealing with tables, instead of with variables, it is a different story.

In this case, Sql Server uses an All-At-Once operation, as discussed by Itzik Ben-Gan in T-Sql Fundamentals.

This concept states that all expressions in the same logical phase are evaluated as if the same point in time... regardless of their left-to-right position.

So when dealing with the corresponding UPDATE statement:

DECLARE @Test TABLE (
    i INT,
    j INT
)

INSERT INTO @Test VALUES (0, 0)

UPDATE @Test
SET
    i = i + 10,
    j = i

SELECT 
    i, 
    j 
FROM 
    @Test

We get the following results:

i           j
----------- -----------
10          0   

And by using the All-At-Once evaluation... in Sql Server you can swap column values in a table without an intermediate variable / column.

Most RBDMSs behave this way as far as I know, but MySql is an exception.

---

EDIT:

Note that effects are only visible if there are references among the
assignments.

I understand this to mean that if you have a SELECT statement such as the following:

SELECT
    @A = ColumnA,
    @B = ColumnB,
    @C = ColumnC
FROM MyTable

Then it doesn't matter what order the assignments are performed in... you'll get the same results no matter what. But if you have something like...

SELECT
    @A = ColumnA,
    @B = @A,
    @C = ColumnC
FROM MyTable

There is now a reference among the assignments (@B = @A), and the order that @A and @B are assigned now matters.

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

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.

How to assign a select result to a variable?

DECLARE @tmp_key int
DECLARE @get_invckey cursor 

SET @get_invckey = CURSOR FOR 
    SELECT invckey FROM tarinvoice WHERE confirmtocntctkey IS NULL AND tranno LIKE '%115876'

OPEN @get_invckey 

FETCH NEXT FROM @get_invckey INTO @tmp_key

DECLARE @PrimaryContactKey int --or whatever datatype it is

WHILE (@@FETCH_STATUS = 0) 
BEGIN 
    SELECT @PrimaryContactKey=c.PrimaryCntctKey
    FROM tarcustomer c, tarinvoice i
    WHERE i.custkey = c.custkey AND i.invckey = @tmp_key

    UPDATE tarinvoice SET confirmtocntctkey = @PrimaryContactKey WHERE invckey = @tmp_key
    FETCH NEXT FROM @get_invckey INTO @tmp_key
END 

CLOSE @get_invckey
DEALLOCATE @get_invckey

EDIT:
This question has gotten a lot more traction than I would have anticipated. Do note that I'm not advocating the use of the cursor in my answer, but rather showing how to assign the value based on the question.

SET versus SELECT when assigning variables?

Quote, which summarizes from this article:

1. SET is the ANSI standard for variable assignment, SELECT is not.
2. SET can only assign one variable at a time, SELECT can make multiple assignments at once.
3. If assigning from a query, SET can only assign a scalar value. If the query returns multiple values/rows then SET will raise an error. SELECT will assign one of the values to the variable and hide the fact that multiple values were returned (so you'd likely never know why something was going wrong elsewhere - have fun troubleshooting that one)
4. When assigning from a query if there is no value returned then SET will assign NULL, where SELECT will not make the assignment at all (so the variable will not be changed from its previous value)
5. As far as speed differences - there are no direct differences between SET and SELECT. However SELECT's ability to make multiple assignments in one shot does give it a slight speed advantage over SET.

Quirky update execution order in SQL server

Within an UPDATE statement, all assignments within the SET clause are executed as if they're all performed in parallel. As such, even your supposed execution order is incorrect. All occurrences of columns on the right hand side of the assignments will always reflect the value held in that column from before the UPDATE started. This leads to nice tricks such as:

UPDATE tab SET a = b, b = a

Which swaps the content of the a and b columns.

If you could edit your question to add more context, such as the existing contents of the table and what you're trying to achieve, I may be able to edit my answer and propose a concrete solution to the problem.

How to set value to variable using 'execute' in t-sql?

You can use output parameters with sp_executesql.

DECLARE @dbName nvarchar(128) = 'myDb'
DECLARE @siteId int 
DECLARE @SQL nvarchar(max) = N'SELECT TOP 1 @outputFromExec = Id FROM ' + quotename(@dbName) + N'..myTbl'
exec sp_executesql @SQL, N'@outputFromExec int out', @siteId out
select @siteId

---

Related Topics