I'm following up on this question 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.