2

Two similar situations outlined below. The first being table columns where we run a series of calculations across columns a, b and c. We do the same thing using declared variables and the result is different to the answer for columns.

It appears that SQL Server persists the value of the table columns for the duration of the command whereas the values of the variables will change during the command.

In a case where you mix variables and table columns the results are still different - but consistent to the descriptions above.

In other RDBMS's that we use there is no difference between these two. One poignant example of where these behave the same is Sybase.

So why is there a difference? Is there something that can alter this behaviour and make it consistent?

/* Column version */

create table #CalcTest (
  a int not null,
  b int not null,
  c int not null
)

insert into #CalcTest ( a, b, c )
values ( 4, 14, 24 )

update #CalcTest
set  b = a + c,
     a = 5,
     c = b - a

select a, b, c from #CalcTest

--output 5 28 10
--Note further - if you break up the update command into 3 commands c = 23

/* Variable version */

declare @a int, @b int, @c int

select @a = 4, @b = 14, @c = 24

select    @b = @a + @c,
          @a = 5,
          @c = @b - @a

select @a, @b, @c

--output 5 28 23
--Note c is 23 where it was previously 10!
  • There is one tip for you in the documentation. "For assigning variables, we recommend that you use SET @local_variable instead of SELECT @local_variable.". No explanation in there as to why but it could because of what you have here. – Mikael Eriksson Oct 23 '17 at 6:32
  • you can always do this rextester.com/QWY94758 – Martin Smith Oct 23 '17 at 6:40
  • @Martin Smith: I haven't seen that syntax before and it's good to know. However, we aren't going to be able to use that across the board. :-) Too many changes but thanks anyway. – RowanPD Oct 23 '17 at 8:33
  • @MikaelEriksson: Interesting how their Set command doesn't allow us to chain the setting of variables, i.e. the "complex" select of variables above is forcibly broken into 3 separate Sets, hence forcing us to expect the result we get here. – RowanPD Oct 23 '17 at 9:07
1

It is just how SQL Server works. More specifically, how its UPDATE statement works.

One SELECT statement with variables in your example is equivalent to three separate SET statements.

SET @b = @a + @c;
SET @a = 5;
SET @c = @b - @a;

The calculations here are sequential. The order in which you write these lines is important as in traditional programming language.

UPDATE statement is not sequential in this sense. It reads current values of all columns of a row into memory (internal variables behind the scene) and uses these cached values in calculations. These cached values are not changed. The final result is written back onto disk in one go, not column by column.

I think, the most vivid example would be an example how you can swap values of two columns with this statement:

update #CalcTest
set  b = a,
     a = b

In traditional procedural languages you have to use third variable to store the value:

temp = a;
a = b;
b = temp;

UPDATE in SQL Server has this variable implicitly. I don't know how other DBMSs implement UPDATE, but at least Postgres and Oracle seem to work in the same way as SQL Server. While MySQL not.


EDIT:

Before I mark this as the correct answer I will clarify and say that it's not so much how updates work but that it is just how columns work and, no, we can't change or control it.

My interpretation from this and other research elsewhere is this:

  • Columns are updated atomically in SqlServer. They get their new values all at the same time at the conclusion of the statement. Hence no partial updates or updates of one and not the other. All or nothing.

  • Variables (even in the same statement as columns) are not updated atomically. They get their new value part way through the statement one after another and their changes can feed into each other as they go. This is just like most programming languages.

  • Also, SqlServer will not guarantee the order in which variables are updated in a Select (and I suspect the same is true of Updates). This is probably due to the optimiser and how it schedules the cost of complex instructions like sub-selects. Simple stuff like that presented here has never (as far as I can see) been processed in anything other that the order it's written in. Just don't rely on that order.

  • Microsoft recommends using the Set command to set variable values where that statement does not refer to tables or views, i.e. like those presented here. However, you can't chain up variables in a Set like you can in a Select. Also, Microsoft say this and then go right ahead and continue using Select to set variables in their examples. :-)

  • I don’t think it is guaranteed to be sequential if there are multiple variables being assigned in a SELECT. I’ve looked for this promise before when assigning variables that depend on previous variables and didn’t find it anyway. – Martin Smith Oct 23 '17 at 6:54
  • @Vladimir: If I merge the update and the select I get the following SQL which gives different answers for the c column than the @c variable. update #CalcTest set b = a + c, a = 5, c = b - a, @b = @a + @c, @a = 5, @c = @b - @a – RowanPD Oct 23 '17 at 8:54
  • 1
    @MartinSmith, I can't find a good reference. By "sequential" in the first place I meant something opposite to "parallel" operation that happens in UPDATE. Since there is no explicit guarantee in the docs, the code should not rely on specific order in which parts of SELECT are executed. I'd say, the behaviour is undefined. The server may calculate @b = @a + @c first , then @a = 5; And it may calculate @a = 5 first, then @b = @a + @c. I think, that bottom line is: if your expressions depend on each other as in this example, use an explicit sequence of SET statements. – Vladimir Baranov Oct 23 '17 at 11:47
  • @RowanPD, I guess, your example proves that in SQL Server UPDATE statement works differently for columns and variables. There are many examples of so called "quirky update" in SQL Server and there are many warnings that you should not rely on this undocumented behaviour. Here are few random examples: stackoverflow.com/q/4082481/4116017 , stackoverflow.com/q/32881117/4116017 – Vladimir Baranov Oct 23 '17 at 12:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.