更新行为

Question

当我在更新查询文本中犯了错误时，我发现出现了不可预测的查询结果。以下是更新的查询文本：

DECLARE @T TABLE (Id int,[Name] nvarchar(100),RNA int)

INSERT INTO @T(Id,[Name])
SELECT [Id],[Name] 
FROM (VALUES (1,N'D'),
             (2,N'B'),
             (3,N'S'),
             (4,N'A'),
             (5,N'F')
     ) AS vtable([Id],[Name])

UPDATE @T
SET RNA = T.RN
FROM (
  select PP.Name,ROW_NUMBER() OVER(ORDER BY PP.Name) RN,PP.RNA from @T PP
) T

select * from @T

我知道错误出在哪里：

UPDATE @T

应该是

UPDATE T

但为什么使用“坏”查询的结果看起来像这样:

Id   Name  RNA
---- ----- -------
1    D     1
2    B     5
3    S     1
4    A     5
5    F     1

我猜1和5的值是MIN（Id）和MAX（Id）。 执行计划如下： 这种错误在每种情况下都会发生吗？ 如果是，这种行为有任何实际价值吗？

- Dalex

Answer 1

对于每种错误情况，处理方式都不同。你有一个非确定性的更新语句，也就是说在你的子查询 T 中，RN 的任何值理论上都可以应用到 @T 中的任何值。你实际上正在运行以下的 UPDATE 版本：

SELECT  *
FROM    @t a
        CROSS JOIN
        (   SELECT  TOP 1
                    PP.Name,
                    ROW_NUMBER() OVER(ORDER BY PP.Name) RN,
                    PP.RNA 
            FROM    @T PP
            ORDER BY NEWID()
        ) T
OPTION (FORCE ORDER);

根据在线手册，如果UPDATE语句包括一个FROM子句且未指定只有每个要更新的列出现的每个值仅有一个可用的方式，那么该语句的结果将是未定义的，也就是说，如果UPDATE语句不具有确定性，则其结果是不确定的。

如果在UPDATE语句中使用FROM子句，但没有明确指定每个列出现的值仅有一个可用的方式，那么该语句的结果是未定义的，即UPDATE语句不具有确定性。

稍有趣的是，如果运行上述代码，每次都会得到不同的结果（除了1/25的概率会连续两次得到相同的结果），如果去掉使用NEWID()进行随机排序，则每行都会得到相同的RN值，但更新的结果始终返回2个不同的RN值。我并不惊讶结果没有随机排序而保持一致，因为在没有数据更改和未引入任何随机因素的情况下，无论运行多少次，我都期望优化器提供相同的执行计划。

由于UPDATE查询中没有明确指定排序顺序，所以顺序取决于叶子上记录的顺序，如果记录的顺序被改变，则结果也会改变。这可以通过将@T的记录插入具有不同ID的新表格中来展示。

DECLARE @T2 TABLE (Id int,[Name] nvarchar(100),RNA int);
INSERT @T2 
SELECT  id, Name, NULL
FROM    @T
ORDER BY ROW_NUMBER() OVER(ORDER BY  NEWID())
OPTION (FORCE ORDER);

UPDATE @T2
SET RNA = T.RN
FROM (
  select PP.Name,ROW_NUMBER() OVER(ORDER BY PP.Name) RN,PP.RNA from @T2 PP
) T

SELECT  *
FROM    @T2;

我看不出来为什么这总是RN的最小或最大值，我认为你需要深入挖掘优化器才能找到答案。这可能是一个更适合DBA Stack Exchange的新问题。

- GarethD