Reference a column and update it in the same state

问题:

I have a table that I need to move a value from one column to another then set a new value in the original column.

declare @foo table (A int, B int);

insert into @Foo select 1, 0;

update @Foo
set B = A,
    A = 2;

After the update does B always contain 1 or is this non deterministic behavior and it will sometimes have a value of 2 due to A being updated first (and all my tests have just never hit just right the conditions to have it be 2)?

---

As a followup question if the answer is "B will always be 1", if I do the following will I still get the same result?

update @Foo
set A = 2,
    B = A;

回答1:

When doing an update, SQL Server keeps two versions of the row. The old one (aka deleted) and the new one (aka inserted.) The assignment itself only modifies the inserted row. All assignments use the same variable values from the deleted row. The order of the assignment does not matter.

You can visualize this using the output clause:

declare @t table (a int, b int)
insert @t values (1,2), (2,3)

update @t 
set     b=a
,       a=b
output  inserted.*
,       deleted.*

select * from @t

回答2:

B will always contain 1 and the order of the clauses makes no difference. Conceptually the operations happen "all at once".

The following also works in SQL to swap two values without requiring any intermediate variable.

update @Foo
set A = B,
    B = A;