SQL update using values from the same record

Question

In the following statement, will f1 always get the value that f2 used to have? Or will f2 sometimes get updated first and f1 winds up with NULL? I am under the impression that the new values are not available within the statement, that f2 has the old value while processing the record, but I can't find an authoritative place that says this.

UPDATE x
SET
    x.f1 = x.f2,
    x.f2 = NULL

Answer 1

Conceptually the operation happens "all at once" so it will use the "before" values

Indeed

UPDATE x
SET
 x.f1 = x.f2,
 x.f2 = x.f1

would also work fine to swap the two column values.

Answer 2

f1 will always get f2's previous value before the UPDATE.

Technically speaking the record is deleted, and reinserted. So SQL will work out what the new record should be, then delete the current record, and insert the new record afterwards.

This article regarding SQL Triggers may help explain:

The deleted table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table. The deleted table and the trigger table ordinarily have no rows in common.

The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.

http://msdn.microsoft.com/en-us/library/ms191300.aspx