主页 / dba / 问题 / 142675
Accepted
usr
Asked: 2016-07-01 05:34:40 +0800 CST

SQL Server 用不符合定义的数据填充 PERSISTED 列是否合法？

  • 772

我正在跟进有关计算列中奇怪值的问题。PERSISTED那里的答案对这种行为是如何发生的做出了一些猜测。

我在问以下问题：这不是一个彻头彻尾的错误吗？是否PERSISTED允许列以这种方式运行？

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

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 --shows impossible data

UPDATE @test SET Col1 = Col1*1 --"fix" the data by rewriting it

SELECT * FROM @test --observe fixed data

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

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

请注意，数据显示为“不可能”，因为计算列的值与其定义不对应。

众所周知，查询中的非确定性函数可能会表现得很奇怪，但这似乎违反了持久计算列的约定，因此应该是非法的。

插入随机数可能是一个人为的场景，但如果我们插入NEWID()值 orSYSUTCDATETIME()呢？我认为这是一个可能实际表现出来的相关问题。

sql-server sql-server-2014

2 个回答

  1. Best Answer

    这当然是一个错误。这些值恰好是涉及随机数的表达式的结果这一事实col1显然不会改变正确的值col2应该是什么。DBCC CHECKDB如果这是针对永久表运行的，则返回错误。

    create table test (
    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));

DBCC CHECKDB

    给出（对于我的测试运行有一个“不可能”的行）

    Msg 2537, Level 16, State 106, Line 17
Table error: object ID 437576597, index ID 0, partition ID 72057594041008128, alloc unit ID 72057594046251008 (type In-row data), page (1:121), row 0. The record check (valid computed column) failed. The values are 2 and 0.
DBCC results for 'test'.
There are 5 rows in 1 pages for object "test".
CHECKDB found 0 allocation errors and 1 consistency errors in table 'test' (object ID 437576597).

    它还确实报告说

    repair_allow_data_loss 是 DBCC CHECKDB 发现的错误的最低修复级别

    如果选择修复选项，则会毫不客气地删除整行，因为它无法判断哪一列已损坏。

    附加调试器显示NEWID()每个插入的行被评估两次。一次在计算CASE表达式之前，一次在表达式内部。

    在此处输入图像描述

    一个可能的解决方法可能是使用

    INSERT INTO @test
            (Col1)
SELECT ( ABS(CHECKSUM(NEWID()) % 5) )
FROM   (VALUES (1),(1),(1),(1),(1)) V(X);

    由于某种原因，这避免了这个问题，并且每行只计算一次表达式。

    • 9

  2. 根据评论对话，共识似乎是对 OP 问题的回答是这确实构成了一个错误（即应该是非法的）。

    OP 引用了 Vladimir Baranov 在 StackOverflow 上的分析，他们指出：

    “第一次用于 Col1，第二次用于持久列的 CASE 语句。

    优化器不知道，或者在这种情况下不关心 NEWID 是一个不确定的函数并调用它两次。”

    换句话说，应该预期 [the NEWID() within] col1 与您刚刚插入的值与您进行计算时的值相同。

    这与错误所发生的情况是同义的，其中为 Col1 创建 NEWID，然后为持久列再次创建：

    INSERT INTO @Test (Col1, Contains2) VALUES
(NEWID(), CASE WHEN (NEWID()) LIKE '%2%' THEN 1 ELSE 0 END)

    在我的测试中，其他非确定性函数（如 RAND 和时间值）不会导致相同的错误。

    Per Martin，已将此问题提交给 Microsoft ( https://connect.microsoft.com/SQLServer/Feedback/Details/2751288 )，其中有对此页面的评论和 StackOverflow 分析（如下）。

    • 2

相关问题