update tab1 set col1=col2,col2=col1

Question

How this query swap data of col1 and col2 of tab1 ? Basically for swapping we need temporary variable but here we are not. so how sql server does this ?

Answer 1

Just that:

UPDATE  tabl
SET     col1 = col2,
        col2 = col1

No DML query sees its result, to avoid Halloween problem.

Internally, an UPDATE lock is placed on the record (or data page), the old values are read and stored into the temp variables, then the lock is promoted to EXCLUSIVE and the new values (stored in temp variables) are written to appropriate columns.

Answer 2

My notes credit Joe Celko:

Each assignment in the <set clause list> is executed in parallel and each SET clause changes all the qualified rows at once. Or at least that is the theoretical model. In practice, implementations will first mark all of the qualified rows in the table in one pass, using the WHERE clause. If there were no problems, then the SQL engine makes a copy of each marked row in working storage. Each SET clause is executed based on the old row image and the results are put in the new row image. Finally, the old rows are deleted and the new rows are inserted. If an error occurs during all of this, then system does a ROLLBACK, the table is left unchanged and the errors are reported. This parallelism is not like what you find in a traditional third-generation programming language, so it may be hard to learn. This feature lets you write a statement that will swap the values in two columns, thus:

UPDATE MyTable
SET a = b, b = a;

This is not the same thing as

BEGIN ATOMIC
UPDATE MyTable
SET a = b;
UPDATE MyTable
SET b = a;
END;

In the first UPDATE, columns a and b will swap values in each row. In the second pair of UPDATEs, column a will get all of the values of column b in each row. In the second UPDATE of the pair, a, which now has the same value as the original value of b, will be written back into column b -- no change at all. There are some limits as to what the value expression can be. The same column cannot appear more than once in a <set clause list> -- which makes sense, given the parallel nature of the statement. Since both go into effect at the same time, you would not know which SET clause to use.