Czy COALESCE jest teraz do nabycia?

Jeden z moich programistów twierdzi, że COALESCE(column, default value) = default valuejest teraz do sargable. Czy to prawda?

Przeprowadziłem następujący test i myślę, że to sugeruje, że COALESCEnie można go sprzedawać.

USE tempdb;

SELECT @@VERSION;
-- Microsoft SQL Server 2016 (RTM-CU3-GDR) (KB3194717) - 13.0.2186.6 (X64)   Oct 31 2016 18:27:32   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 14393: ) (Hypervisor) 

CREATE TABLE Test 
(
    ID int primary key clustered, 
    Mod6 int null, 
    INDEX IX_Mod6 NONCLUSTERED (Mod6)
);

INSERT INTO Test (ID, Mod6)
SELECT object_id as ID, case when name like '%k%' then null else object_id % 6 end as Mod6
FROM sys.objects;

SELECT Mod6
FROM Test WITH (INDEX = IX_Mod6, FORCESEEK)
where Mod6 is null or Mod6 = 0;
-- Plan shows expected seek

SELECT Mod6
FROM Test WITH (INDEX = IX_Mod6, FORCESEEK)
WHERE COALESCE(Mod6, 0) = 0;
-- Error:
-- Msg 8622, Level 16, State 1, Line 20
-- Query processor could not produce a query plan because of the hints 
-- defined in this query. Resubmit the query without specifying any hints 
-- and without using SET FORCEPLAN.

sql-server index sql-server-2016

— Mitch
źródło

Czy twój programista kłócił się o jakiekolwiek dowody?

— Aaron Bertrand

Przeczytałem ten post: josef-richberg.squarespace.com/journal/2010/1/28/ ... Nie sądzę, aby można było uzyskać konkretną odpowiedź na ten post. (Ale może ktoś wie lepiej.)

— RLF

@RLF - w tym artykule nie ma nic, co sugerowałoby, że można go sprzedawać. Nie jest to dowód na to, że można go sprzedać. twitpic.com/107ms0 . Brak możliwości sargowania polega na tym, że zapobiega wyszukiwaniu w kolumnach używanych w CASEwyrażeniu - nie dlatego, że wynik wyrażenia nie może być następnie użyty do wyszukiwania w czymś innym.

— Martin Smith

@AaronBertrand, „Plan wykonania wyglądał tak samo bez względu na to, jak go piszę” - zignorował fakt, że żadne z nich nie wykonywało wyszukiwania na podstawie COALESCEkolumny d.

— Mitch

Och, nie winię cię za to, że zadałeś pytanie, zwłaszcza, że najpierw próbowałeś go obalić. Sugerowałem tylko, że może twój programista powinien nauczyć się robić to samo.

— Aaron Bertrand

Nie, COALESCEnie można wysiadać.

Twój własny test pokazuje to dobrze.

Wyjątkiem może być utworzenie kolumny obliczeniowej z COALESCEwyrażeniem i zaindeksowanie tego.

CREATE TABLE Test 
(
    ID int primary key clustered, 
    Mod6 int null, 
    Foo AS COALESCE(Mod6, 0),
    INDEX IX_Mod6 NONCLUSTERED (Mod6),
    INDEX IX2_Mod6 NONCLUSTERED (Foo),
);

Możesz skończyć z poszukiwaniem tego

SELECT Mod6
FROM Test WITH (INDEX = IX2_Mod6, FORCESEEK)
WHERE COALESCE(Mod6, 0) = 0;

ISNULL jest marginalnie bardziej podatny na sprzedaż, ponieważ jeśli jest całkowicie zbędny, można go zoptymalizować i nie uniemożliwić wyszukiwania.

tzn. jeśli kolumna Mod6jest zdefiniowana jako, NOT NULLto następujące może wygenerować wyszukiwanie.

SELECT Mod6
FROM Test 
WHERE ISNULL(Mod6, 0) = 0;

Ale to oczywiście nie zapewnia żadnych korzyści w porównaniu do zwykłego działania

WHERE Mod6 = 0

— Martin Smith
źródło