How does a swapping data between two tables in SQL Server work?

Question

I could probably google this, but it seemed quirky enough that it might be worth having logged as an answer on SA.

So in development land, if you want to swap interchance the values of two variables, you need a third temp variable.

e.g.

string x = "ABC";
string y = "DEF";

string temp;

temp = x;
x = y;
y = temp;

However in a SQL Update, you can simply say

UPDATE table
SET ColumnA = ColumnB, ColumnB = ColumnA

How does this work under the hood

• Does SQL Server take a snap shot of the entire row first ?
• Does SQL Server take a snap shot of all the rows being updated in one go ?
• Does the optimizer realize that it's doing a column interchange, and make a temp variable behind the scenes?

Cheers EoinC

Answer 1

The SQL command is not a serial, one after the other, step by step operation. It's a set operation on multiple columns/rows in one go.

SQL is declarative. You tell the engine what you want to, and it does it. Your client code (and your thinking perhaps) is procedural

Finally, possibly the best explanation lies in the article about the "halloween problem".

Note: how SQL Server manages it internally may be different to Oracle, but the same issues are solved by both

Answer 2

Does SQL Server take a snap shot of the entire row first?

In a way, yes it does.

This is an interesting scenario that highlights the difference between declarative and procedural code. Let us take the following example:

UPDATE 
    users 
SET 
    first_name = last_name, 
    last_name = first_name,
    age = 55
WHERE
    user_id = 100

The UPDATE statement would work somewhat like this:

• First it checks the WHERE clause. All the rows that match the WHERE clause would be marked as a subset. If there were no WHERE clause, then the entire table would have been marked. Using the above example, we could have a subset like the following:

    user_id  |  first_name  |  last_name  |  age  |  country
    ---------+--------------+-------------+-------+---------
    100      |  John        |  Doe        |  50   |  USA

• Then a new subset is constructed from the SET clause. Fields that are not mentioned in the SET clause are copied from the original subset.

  The age field in the new subset will be assigned the value of 55 directly. The same will happen for the first_name and last_name fields, but their new assignment values would be retrieved from the original subset. The country field is copied as-is from the original subset, since it was not mentioned in the SET clause.

    user_id  |  first_name  |  last_name  |  age  |  country
    ---------+--------------+-------------+-------+---------
    100      |  Doe         |  John       |  55   |  USA

• The original subset is then deleted from the table and the new subset is inserted.

Answer 3

To add to gbn, this may help understanding:

Halloween Protection

Read Committed Isolation Level

EDIT: Actually I wanted to paste this one: Serializable vs. Snapshot Isolation Level. But never mind, all are worth reading anyway.