Jakie reguły określają, kiedy SQL Server używa CTE jako „ogrodzenia optymalizacyjnego”?

Jakiś czas temu Brent Ozar opublikował post opisujący niektóre różnice między SQL Server a PostgreSQL:

Dwie ważne różnice między SQL Server a PostgreSQL

Pierwszy punkt („CTE to ogrodzenia optymalizacyjne”) przykuł moją uwagę, ponieważ jest oczywiste, że w podanym przykładzie SQL Server łączy CTE i główne zapytanie razem i optymalizuje je jako pojedyncze zapytanie (w przeciwieństwie do odwrotnego zachowania w PostgreSQL).

Jednak to zachowanie wydaje się sprzeczne z przykładami, które widziałem w innych blogach i klasach szkoleniowych, w których SQL Server traktuje CTE jako płot optymalizacyjny, który pozwala na lepsze wykorzystanie indeksów, lepszą wydajność itp. Na przykład:

Lepszy sposób na wybór gwiazdy

Wygląda więc na to, że SQL Server „honoruje” CTE SOMETIMES. Czy są dostępne jakieś dobre zasoby, które dokumentują konkretną listę znanych przypadków, w których SQL Server niezawodnie honoruje CTE jako płot optymalizacyjny (lub odwrotne zachowanie)?

... lista znanych przypadków, w których SQL Server niezawodnie honoruje CTE jako płot optymalizacyjny

Każda taka lista opierałaby się na zaobserwowanym zachowaniu, bez gwarancji wiarygodności.

Optymalizator kwerendy SQL Server nie traktuje wspólną ekspresję stołowego jako ogrodzenie optymalizacji per se , choć niektóre konstrukcje są oczywiście trudne, aby zoptymalizować drugiej. Rekurencyjne CTE są tego dobrym przykładem.

CTE są traktowane bardzo podobnie do widoków / funkcji wbudowanych / podkwerend / tabel pochodnych i wprowadzane do zapytania. Każde zaobserwowane zachowanie „ogrodzenia” zależy od tego, czy optymalizator albo nie będzie w stanie, albo nie zdecyduje się na optymalizację przez tę zasadniczo przepuszczalną granicę.

Ogólnie rzecz biorąc, im prostszy i bardziej „relacyjny” jest CTE, tym bardziej prawdopodobne jest, że optymalizator będzie w stanie przenosić bity.

Sugerowane, ale jeszcze nie wdrożone funkcje, które pozwoliłyby optymalizatorowi rozważyć lub zmusić go do zmaterializowania „wyniku” CTE.

• Optymalizator powinien rozważyć materializację wyników CTE
• Dodatkowa wskazówka dotycząca zapytania: OPCJA (MATERIALIZE (nazwa_cte, ..., n)

W międzyczasie najczęstszym obejściem jest jawne zmaterializowanie pośredniego zestawu wyników w tabeli tymczasowej lub zmiennej tabeli. Wymaga to oczywiście scenariusza nie ograniczonego do pojedynczego stwierdzenia.