8

Given the following example:

declare @i int
select @i = 1, @i = 2
select @i

Will @i always be 2?

This is about the most trivial example I can think of, but I am considering using this for swapping values in variables. I also believe this method of assignment (select) is not ANSI compliant (however useful), but don't really care to have portable code in this case.

UPDATE

Thanks to @MichaelFredrickson, we have @MartinSmith's answer and reference to MSDN on this. I am now struggling with what the second sentence in this documentation means, exactly (emphasis added):

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.

The first sentence is plenty enough to keep me away from relying upon the behavior, however.

Community
  • 1
  • 1
Tim Lehner
  • 14,813
  • 4
  • 59
  • 76

1 Answers1

6

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.

Community
  • 1
  • 1
Michael Fredrickson
  • 36,839
  • 5
  • 92
  • 109
  • +1, good find on the answer and near-duplicate question. I had debated acknowledging the column swap counter example in my question, but left it out for brevity. Now, should I vote to close my own question? – Tim Lehner Feb 14 '13 at 22:11
  • 1
    @TimLehner I had a heck of a time finding the question containing Martin's answer... and the question you just posted was always at the top of my google searches... I think that at the very least, this question is still useful as a pointer to the other question, and a slightly extended discussion... – Michael Fredrickson Feb 14 '13 at 22:14