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!