I'm following up on [this question][1] about strange values in a `PERSISTED` computed column. The answer there makes a few guesses about how this behavior came to be. I'm asking the following: Is this not an outright bug? Are `PERSISTED` columns ever allowed to behave this way? 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 */ Note, that the data appears "impossible" because the values of the computed column do not correspond to its definition. It is well known that non-deterministic functions in queries can behave strangely but here this seems to violate the contract of persisted computed columns and, therefore, should be illegal. Inserting random numbers might be a contrived scenario but what if we were inserting `NEWID()` values or `SYSUTCDATETIME()`? I think this is a relevant issue that might practically manifest itself. [1]: https://stackoverflow.com/questions/38120684/inconsistent-results-with-newid-and-persistent-computed-column

or

This is certainly a bug. The fact that the `col1` values happened to be the result of an expression involving random numbers clearly doesn't change what the the correct value for `col2` is supposed to be. `DBCC CHECKDB` returns an error if this is run against a permanent table. 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 Gives (for my test run which had one "impossible" row) 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). It does also report that > repair_allow_data_loss is the minimum repair level for the errors > found by DBCC CHECKDB And if taken up on the repair option unceremoniously deletes the whole row as it has no way of telling which column is corrupted. Attaching a debugger shows that the `NEWID()` is being evaluated twice per inserted row. Once before the `CASE` expression is evaluated and once inside it. [![enter image description here][1]][1] A possible workaround might be to use INSERT INTO @test (Col1) SELECT ( ABS(CHECKSUM(NEWID()) % 5) ) FROM (VALUES (1),(1),(1),(1),(1)) V(X); Which for one reason or another avoids the issue and only evaluates the expression once per row. [1]: https://i.stack.imgur.com/HHKF7.png