Obliczony indeks kolumny nie jest używany

Chcę mieć szybkie wyszukiwanie w oparciu o to, czy dwie kolumny są równe. Próbowałem użyć kolumny obliczeniowej z indeksem, ale wydaje się, że SQL Server go nie używa. Jeśli po prostu użyję statycznie wypełnionej kolumny bitów z indeksem, otrzymam oczekiwane wyszukiwanie indeksu.

Wydaje się, że istnieją jeszcze takie pytania, ale żadne z nich nie koncentruje się na tym, dlaczego indeks nie byłby używany.

Tabela testowa:

CREATE TABLE dbo.Diffs
    (
    Id int NOT NULL IDENTITY (1, 1),
    DataA int NULL,
    DataB int NULL,
    DiffPersisted  AS isnull(convert(bit, case when [DataA] is null and [DataB] is not null then 1 when [DataA] <> [DataB] then 1 else 0 end), 0) PERSISTED ,
    DiffComp  AS isnull(convert(bit, case when [DataA] is null and [DataB] is not null then 1 when [DataA] <> [DataB] then 1 else 0 end), 0),
    DiffStatic bit not null,
    Primary Key (Id)
    )

create index ix_DiffPersisted on Diffs (DiffPersisted)
create index ix_DiffComp on Diffs (DiffComp)
create index ix_DiffStatic on Diffs (DiffStatic)

I zapytanie:

select Id from Diffs where DiffPersisted = 1
select Id from Diffs where DiffComp = 1
select Id from Diffs where DiffStatic = 1

I wynikające z tego plany wykonania:

— David Faivre
źródło

Odpowiedzi:

Spróbuj z COALESCEzamiast ISNULL. Dzięki ISNULLSQL Server nie wydaje się być w stanie wypchnąć predykatu względem węższego indeksu i dlatego musi skanować klaster, aby znaleźć informacje.

CREATE TABLE dbo.Diffs
    (
    Id int NOT NULL IDENTITY (1, 1),
    DataA int NULL,
    DataB int NULL,
    DiffPersisted  AS COALESCE(convert(bit, case when [DataA] is null 
      and [DataB] is not null then 1 when [DataA] <> [DataB] 
      then 1 else 0 end), 0) PERSISTED ,
    DiffComp  AS COALESCE(convert(bit, case when [DataA] is null 
      and [DataB] is not null then 1 when [DataA] <> [DataB] 
      then 1 else 0 end), 0),
    DiffStatic bit not null,
    Primary Key (Id)
    );

To powiedziawszy, jeśli pozostaniesz przy statycznej kolumnie, filtrowany indeks może być bardziej sensowny i będzie miał niższe koszty We / Wy (wszystkie w zależności od tego, ile wierszy zwykle pasuje do predykatu filtru), np .:

CREATE INDEX ix_DiffStaticFiltered 
  ON dbo.Diffs(DiffStatic)
  WHERE DiffStatic = 1;

— Aaron Bertrand
źródło

Bardzo interesujące, nie pomyślałbym o tym. Wygląda COALESCEna to, że w tym momencie możesz się po prostu pozbyć ; Wydaje mi się, że CASEinstrukcja już zwróciła 0lub 1, ale ISNULLbyła obecna tylko po to, aby SQL Server zwrócił wartość zerową BITdla kolumny obliczanej. Jednak COALESCEnadal da kolumnę zerowalną. Więc jedynym skutkiem tej zmiany, z lub bez COALESCE, jest to, że kolumna obliczeniowa jest teraz zerowa, ale można użyć wyszukiwania indeksu.

— Geoff Patterson

@Geoff Tak, to prawda. Ale w tym przypadku, ponieważ wiemy, przez komputerowej NULL definicja kolumny naprawdę nie jest możliwe wyjście, to tylko naprawdę się liczy, jeśli używamy tej tabeli jako źródła SELECT INTO.

— Aaron Bertrand

To jest niesamowita informacja - dziękuję! Moim celem końcowym jest to, aby kolumny DataA i DataB były używane jako „brudne” uuids, aby umożliwić asynchroniczną aktualizację zdenormalizowanych kolumn w rekordzie, więc nie powinno być zbyt wielu, gdzie flaga Diff wynosi 1. Jeśli użyję statycznego pole, wtedy myślałem o dodaniu wyzwalacza do monitorowania dwóch UUID i aktualizacji pola.

— David Faivre,

Ponadto, jak wskazał @GeoffPatterson, czy nie mogę używać COALESCE? Dlaczego miałbym to zatrzymać?

— David Faivre,

@David Prawdopodobnie możesz upuścić COALESCE. Starałem się zachować wygląd i intencję twojego oryginalnego kodu i nie testowałem bez niego, więc testowanie będzie na tobie. (Nie umiem też wyjaśnić, dlaczego ISNULLtam byłeś ).

— Aaron Bertrand

Jest to specyficzne ograniczenie obliczonej przez SQL Server logiki dopasowywania kolumn, gdy ISNULLużywany jest element zewnętrzny , a typem danych kolumny jest bit.

Zgłoszenie błędu

Aby uniknąć tego problemu, można zastosować jedno z następujących obejść:

Nie należy używać peryferyjnych ISNULL(jedyny sposób na utworzenie kolumny obliczeniowej NOT NULL).
Nie należy używać bittypu danych jako końcowego typu kolumny obliczanej.
Utwórz kolumnę obliczeniową PERSISTEDi włącz flagę śledzenia 174 .

Detale

Sedno problemu polega na tym, że bez flagi śledzenia 174 wszystkie obliczone odwołania do kolumn w zapytaniu (nawet utrwalone) są zawsze rozszerzane do definicji podstawowej na bardzo wczesnym etapie kompilacji zapytania.

Idea rozszerzenia polega na tym, że może on umożliwiać uproszczenia i przepisywanie, które mogą działać tylko na definicji, a nie na samej nazwie kolumny. Na przykład mogą istnieć predykaty w zapytaniu odnoszące się do tej kolumny obliczeniowej, które mogłyby uczynić część obliczeń zbędną lub w inny sposób bardziej ograniczoną.

Po rozważeniu wczesnych uproszczeń i ponownych zapisów, kompilacja zapytania próbuje dopasować wyrażenia w zapytaniu do kolumn obliczonych (wszystkie kolumny obliczone, nie tylko te pierwotnie znalezione w tekście zapytania).

Niezmienione obliczone wyrażenia kolumnowe dopasowują się z powrotem do oryginalnej kolumny obliczeniowej bez problemu w większości przypadków. Wygląda na to, że występuje błąd związany z dopasowaniem wyrażenia bittypu do skrajnegoISNULL . Dopasowywanie kończy się niepowodzeniem w tym konkretnym przypadku, nawet jeśli szczegółowe badanie elementów wewnętrznych wykazuje, że powinno się to udać.

— Paul White 9
źródło