我正在跟踪
this question关于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))
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()值或SYSUTCDATETIME()会怎么样？我认为这是一个可能实际表现出来的相关问题.
解决方法
这当然是一个错误. 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));
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 is the minimum repair level for the errors
found by DBCC CHECKDB
如果采用修复选项,则会毫不客气地删除整行,因为它无法确定哪个列已损坏.
附加调试器显示每个插入行评估NEWID()两次.在评估CASE表达式之前和之后一次.
可能的解决方法可能是使用
INSERT INTO @test
(Col1)
SELECT ( ABS(CHECKSUM(NEWID()) % 5) )
FROM (VALUES (1),(1),(1)) V(X);
出于某种原因,这可以避免该问题,并且每行仅评估一次表达式.
