Dlaczego agregacja tego strumienia jest konieczna?

Sprawdź to zapytanie. To dość proste (definicje tabel i indeksów oraz skrypt repro znajdują się na końcu wpisu):

SELECT MAX(Revision)
FROM dbo.TheOneders
WHERE Id = 1 AND 1 = (SELECT 1);

Uwaga: „ORAZ 1 = (WYBIERZ 1) ma jedynie na celu uniknięcie automatycznej parametryzacji tego zapytania, co - jak się wydawało - dezorientuje problem - w rzeczywistości otrzymuje ten sam plan z tą klauzulą lub bez niej.

A oto plan ( wklej link do planu) :

Ponieważ jest tam „pierwsza pozycja”, byłem zaskoczony widokiem operatora agregującego strumień. Nie wydaje mi się to konieczne, ponieważ gwarantuje się, że będzie tylko jeden rząd.

Aby przetestować tę teorię, wypróbowałem to logicznie równoważne zapytanie:

SELECT MAX(Revision)
FROM dbo.TheOneders
WHERE Id = 1
GROUP BY Id;

Oto plan tego ( wklej link do planu ):

Rzeczywiście, grupa według planu jest w stanie przetrwać bez operatora agregacji strumienia.

Zauważ, że oba zapytania odczytują „wstecz” od końca indeksu i wykonują „top 1”, aby uzyskać maksymalną wersję.

Czego tu brakuje? Czy agregacja strumienia faktycznie działa w pierwszym zapytaniu, czy może być w stanie ją wyeliminować (a to tylko ograniczenie optymalizatora, że tak nie jest)?

Nawiasem mówiąc, zdaję sobie sprawę, że nie jest to niewiarygodnie praktyczny problem (oba zapytania zgłaszają 0 ms procesora i upływający czas), jestem tylko ciekawy, jakie są tutaj elementy wewnętrzne / zachowanie.

Oto kod instalacyjny, który uruchomiłem przed uruchomieniem dwóch powyższych zapytań:

DROP TABLE IF EXISTS dbo.TheOneders;
GO

CREATE TABLE dbo.TheOneders
(
    Id INT NOT NULL,
    Revision SMALLINT NOT NULL,
    Something NVARCHAR(23),

    CONSTRAINT PK_TheOneders PRIMARY KEY NONCLUSTERED (Id, Revision)
);
GO

INSERT INTO dbo.TheOneders
    (Id, Revision, Something)
SELECT DISTINCT TOP 1000 
    1, m.message_id, 'Do...'
FROM sys.messages m
ORDER BY m.message_id
OPTION (MAXDOP 1);

INSERT INTO dbo.TheOneders
    (Id, Revision, Something)
SELECT DISTINCT TOP 100 
    2, m.message_id, 'Do that thing you do...'
FROM sys.messages m
ORDER BY m.message_id
OPTION (MAXDOP 1);
GO

— Josh Darnell
źródło

Możesz zobaczyć rolę tego agregatu, jeśli żaden wiersz nie pasuje do WHEREklauzuli.

SELECT MAX(Revision)
FROM   dbo.TheOneders
WHERE  Id = 1
       AND 1 = 1 /*To avoid auto parameterisation*/
       AND Id%3 = 4  /*always false*/

W takim przypadku zero agregatów przechodzi do agregatu, ale nadal emituje jeden, ponieważ NULLw tym przypadku ma zostać zwrócona poprawna semantyka .

Jest to agregat skalarny w przeciwieństwie do agregatu wektorowego.

Twoje zapytanie „logicznie równoważne” nie jest równoważne. Dodanie GROUP BY Idspowoduje, że będzie to agregacja wektorów, a poprawnym zachowaniem byłoby zwrócenie żadnych wierszy.

Zobacz Zabawa ze skalarnymi i wektorowymi agregatami, aby uzyskać więcej informacji na ten temat.

— Martin Smith
źródło