SQL Server 中的两列之间交换数据是如何工作的？

发布于 2024-08-21 00:49:36 字数 473 浏览 1 评论 0原文

我可能可以用谷歌搜索这个，但它看起来很奇怪，可能值得在 SA 上作为答案记录下来。

因此，在开发领域，如果您想交换两个变量的值，则需要第三个临时变量。

例如，

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

string temp;

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

但是在 SQL 更新中，您可以简单地说

UPDATE table
SET ColumnA = ColumnB, ColumnB = ColumnA

这在幕后是如何工作的

SQL Server 是否首先拍摄整行的快照？
SQL Server 是否一次性拍摄所有正在更新的行的快照？
优化器是否意识到它正在进行列交换，并在幕后创建临时变量？

干杯EoinC

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

墨离汐 2024-08-28 00:49:36

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

然后从表中删除原始子集并插入新子集。

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