使用NEWID()和持久化计算列时结果不一致

Question

在使用NEWID()与持久化的计算列组合时，我得到了奇怪的结果。我是否错误地使用了某个函数？

如果在创建列时未使用persisted关键字并且在选择时计算值，则会返回正确的值。 更新列（col1）也将返回正确的值。

DECLARE @test TABLE (
    Col1 INT,
    Contains2 AS CASE WHEN 2 IN (Col1) THEN 1 ELSE 0 END PERSISTED)

INSERT INTO @test (Col1) VALUES
    (ABS(CHECKSUM(NEWID()) % 5)),
    (ABS(CHECKSUM(NEWID()) % 5)),
    (ABS(CHECKSUM(NEWID()) % 5)),
    (ABS(CHECKSUM(NEWID()) % 5)),
    (ABS(CHECKSUM(NEWID()) % 5))

SELECT * FROM @test
UPDATE @test SET Col1 = Col1*1
SELECT * FROM @test

/*
Col1    Contains2
2   0
2   0
0   1
4   0
3   0

Col1    Contains2
2   1
2   1
0   0
4   0
3   0
*/

- Kristofer

---

我认为值得注意的是，即使省略了“PERSISTED”关键字，也可以获得预期的行为。你可能想在你的问题中强调这一点。 - Dan Guzman

@DanGuzman 很好，已更新问题。 - Kristofer

同时发布在 dba.se 上。 - Peter Vandivier

虽然代码相同，但问题并不相同。 - usr

Answer 1

显然，查询引擎为每一行计算两次随机数。第一次是为了Col1，第二次是为了持久化列的CASE语句。优化器不知道或者在这种情况下不关心NEWID是一个非确定性函数并调用它两次。实际上，它甚至可能没有选择。你想让优化器在幕后创建一个临时表，用生成随机数的表达式填充其Col1，然后读取该临时表并使用这些保存的中间结果来计算CASE表达式的结果，最后执行最终的INSERT吗？在这种情况下，优化器两次计算表达式而不将中间结果写入磁盘更便宜。在其他一些情况下（比如说，当你有不是5而是50亿行或额外的索引时），估计成本可能会有所不同，这种行为也会改变。我认为你无法做太多事情。只需注意这种行为。总是明确地将生成的随机数集保存到表中，然后根据它们执行进一步的计算。

我在SQL Server 2008和2014中复现了它。 这是我在SQL Server 2008中获得的执行计划，但它并不是非常有趣。在2014年，计划是相同的，除了没有Top运算符。

常量扫描操作符输出一个Union1009列表，稍后在计算标量中使用。我猜，这归结于常量扫描和/或计算标量操作符的实现细节。

观察行为告诉我们，在此处每行调用newid()两次。

- Vladimir Baranov

---

奇怪的是，执行计划显示随机值只被计算了一次。它们作为常量扫描的一部分被计算。之后，计算标量计算了计算列。 - usr

@usr，我看到一个“常量扫描”操作符，然后是一个用于计算CASE的“计算标量”，它使用了“常量扫描”的输出。在计划中，我没有明确看到“常量扫描”操作符的结果被存储在内存中而不是按需重新计算。无论如何，观察到的行为告诉我们，每行调用两次NEWID()。 - Vladimir Baranov

@usr，顺便提一下，运算符的名称 - Constant Scan 提示我们优化器认为 (ABS(CHECKSUM(NEWID()) % 5)) 的结果是一个常量，因此允许调用这个函数两次。 - Vladimir Baranov

@Kristofer，那个查询可能会产生非直观的结果，但很容易看出发生了什么。循环遍历5行。第i行：生成一个新的随机数，它可以等于i也可以不等于，如果相等，则将该行包含在结果集中。这样的查询可以返回0到5行之间的任何内容，尽管概率不同。效果与您最初的问题有些类似。 - Vladimir Baranov

在相关帖子中，用户Martin Smith评论道：“顺便说一句，这里已经报告过了。” https://connect.microsoft.com/SQLServer/Feedback/Details/2751288 - John

Answer 2

在测试过程中，我删除了与NEWID无关的函数，并展示了如果提前计算NEWID的结果。这可能对其他人有所帮助。

DECLARE @test TABLE (
InsertType VARCHAR(30),
Col1 VARCHAR(5),
Contains2 AS CASE WHEN (Col1) LIKE '%2%' THEN 1 ELSE 0 END) --depends on Col1

INSERT INTO @test (InsertType, Col1) VALUES
    ('Compute With Insert', LEFT(NEWID(), 5)),
    ('Compute With Insert', LEFT(NEWID(), 5)),
    ('Compute With Insert', LEFT(NEWID(), 5)),
    ('Compute With Insert', LEFT(NEWID(), 5)),
    ('Compute With Insert', LEFT(NEWID(), 5))

SELECT * FROM @test

DECLARE @A VARCHAR(5) = LEFT(NEWID(), 5);
DECLARE @B VARCHAR(5) = LEFT(NEWID(), 5);
DECLARE @C VARCHAR(5) = LEFT(NEWID(), 5);
DECLARE @D VARCHAR(5) = LEFT(NEWID(), 5);
DECLARE @E VARCHAR(5) = LEFT(NEWID(), 5);

SELECT @A, @B, @C, @D, @E;

INSERT INTO @Test (InsertType, Col1) VALUES
('Compute Before Insert', @A), ('Compute Before Insert', @B), ('Compute Before Insert', @C), ('Compute Before Insert', @D), ('Compute Before Insert', @E)

SELECT * FROM @test

InsertType                 Col1        Contains2
Compute With Insert        C5507        0
Compute With Insert        C17D7        0
Compute With Insert        D9087        1
Compute With Insert        E2DB0        0
Compute With Insert        7D1AF        1
Compute Before Insert      31050        0
Compute Before Insert      2954C        1
Compute Before Insert      9E205        1
Compute Before Insert      DDF05        0
Compute Before Insert      ED708        0

- John