How is data exchanged between two columns in SQL Server?

Question

Perhaps I could have done this with Google, but it seemed to me that it was rather difficult, so it would be useful to write down the answer in SA.

So, in the development field, if you want to exchange interchance values ​​of two variables, you need a third temporary variable.

eg.

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

string temp;

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

However, in SQL Update you can just say

UPDATE table
SET ColumnA = ColumnB, ColumnB = ColumnA

How does it work under the hood

• Does SQL Server set a snapshot of the entire row?
• Does SQL Server specify a snapshot of all rows updated at one time?
• Does the optimizer understand that it is exchanging columns and making a temporary variable backstage?

Cheers EoinC

Answer 1

SQL Server ?

, , .

, . :

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

UPDATE :

• WHERE. , WHERE, . WHERE, . , , :

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

• SET. , SET, .

  age 55. first_name last_name, . country - , SET.

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

• .

Answer 2

gbn, :

EDIT: : Serializable vs Snapshot. , .

Answer 3

SQL , , . / .

SQL declarative. , , . , (, , ) procedural

, , .

: SQL Server , Oracle,