Czy SQL Server może wypełniać kolumny PERSISTED danymi niezgodnymi z definicją?

W odpowiedzi na pytanie dotyczące dziwnych wartości w PERSISTEDkolumnie obliczeniowej. Odpowiedź zawiera kilka domysłów na temat tego, jak powstało to zachowanie.

Pytam: czy to nie jest zwykły błąd? Czy PERSISTEDkolumny mogą kiedykolwiek zachowywać się w ten sposób?

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
*/

Zauważ, że dane wydają się „niemożliwe”, ponieważ wartości wyliczonej kolumny nie odpowiadają jej definicji.

Dobrze wiadomo, że funkcje niedeterministyczne w zapytaniach mogą zachowywać się dziwnie, ale tutaj wydaje się to naruszać umowę utrwalonych kolumn obliczeniowych, a zatem powinno być nielegalne.

Wstawianie liczb losowych może być wymyślonym scenariuszem, ale co, jeśli wstawimy NEWID()wartości lub SYSUTCDATETIME()? Myślę, że jest to istotny problem, który może się praktycznie objawić.

To z pewnością błąd. Fakt, że col1wartości były wynikiem wyrażenia zawierającego liczby losowe, wyraźnie nie zmienia tego, jaka col2powinna być poprawna wartość . DBCC CHECKDBzwraca błąd, jeśli jest uruchamiany na stałej tabeli.

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

Daje (dla mojego testu, który miał jeden „niemożliwy” wiersz)

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).

Zgłasza to również

repair_allow_data_loss to minimalny poziom naprawy błędów wykrytych przez DBCC CHECKDB

A jeśli zostanie podjęta opcja naprawy, bezceremonialnie usuwa cały wiersz, ponieważ nie ma sposobu na stwierdzenie, która kolumna jest uszkodzona.

Dołączenie debugera pokazuje, że NEWID()jest on oceniany dwa razy na wstawiony wiersz. Raz przed CASEwyrażeniem jest oceniane i raz w nim.

Możliwym obejściem może być użycie

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

Który z tego czy innego powodu pozwala uniknąć problemu i ocenia wyrażenie tylko raz na wiersz.

Na podstawie komentarza wydaje się, że zgodą na pytanie PO jest to, że stanowi to błąd (tzn. Powinien być nielegalny).

OP odwołuje się do analizy Vladimira Baranova dotyczącej StackOverflow, w której stwierdza:

„Pierwszy raz dla kolumny 1, drugi raz dla instrukcji CASE utrwalonej kolumny.

Optymalizator nie wie lub w tym przypadku nie przejmuje się tym, że NEWID jest funkcją niedeterministyczną i wywołuje ją dwukrotnie ”.

Innymi słowy, należy się spodziewać, że [NEWID () wewnątrz] col1 ma tę samą wartość, którą właśnie wstawiłeś, jak podczas wykonywania obliczeń.

Byłoby to równoznaczne z tym, co dzieje się z błędem, gdzie NEWID jest tworzony dla Col1, a następnie tworzony ponownie dla utrwalonej kolumny:

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

W moich testach inne niedeterministyczne funkcje, takie jak RAND i wartości czasu, nie spowodowały tego samego błędu.

Według Martina zostało to zgłoszone Microsoftowi ( https://connect.microsoft.com/SQLServer/Feedback/Details/2751288 ), gdzie znajdują się komentarze z powrotem do tej strony i analizy StackOverflow (poniżej).