To zależy.
Po pierwsze
Co to jest wspólne wyrażenie tabeli?
(Nierekurencyjne) CTE jest traktowane bardzo podobnie do innych konstrukcji, które mogą być również używane jako wyrażenia tabeli wbudowanej w SQL Server. Pochodne tabele, widoki i funkcje wartościowane w tabeli wbudowanej. Zauważ, że chociaż BOL mówi, że CTE „można traktować jako tymczasowy zestaw wyników”, jest to opis czysto logiczny. Najczęściej nie jest on sam w sobie zmaterializowany.
Co to jest tabela tymczasowa?
To jest zbiór wierszy przechowywanych na stronach danych w tempdb. Strony danych mogą znajdować się częściowo lub całkowicie w pamięci. Dodatkowo tabela tymczasowa może być indeksowana i posiadać statystyki kolumnowe.
Dane testowe
CREATE TABLE T(A INT IDENTITY PRIMARY KEY, B INT , F CHAR(8000) NULL);
INSERT INTO T(B)
SELECT TOP (1000000) 0 + CAST(NEWID() AS BINARY(4))
FROM master..spt_values v1,
master..spt_values v2;
Przykład 1
WITH CTE1 AS
(
SELECT A,
ABS(B) AS Abs_B,
F
FROM T
)
SELECT *
FROM CTE1
WHERE A = 780
Zauważ, że w powyższym planie nie ma wzmianki o CTE1. Po prostu uzyskuje bezpośredni dostęp do tabel podstawowych i jest traktowany tak samo, jak
SELECT A,
ABS(B) AS Abs_B,
F
FROM T
WHERE A = 780
Przepisanie przez zmaterializowanie CTE do tymczasowej tabeli pośredniej byłoby tutaj ogromnie nieproduktywne.
Materializacja definicji CTE
SELECT A,
ABS(B) AS Abs_B,
F
FROM T
Wymagałoby to skopiowania około 8 GB danych do tabeli tymczasowej, a następnie nadal istnieje narzut związany z wyborem z niej.
Przykład 2
WITH CTE2
AS (SELECT *,
ROW_NUMBER() OVER (ORDER BY A) AS RN
FROM T
WHERE B % 100000 = 0)
SELECT *
FROM CTE2 T1
CROSS APPLY (SELECT TOP (1) *
FROM CTE2 T2
WHERE T2.A > T1.A
ORDER BY T2.A) CA
Powyższy przykład zajmuje na moim komputerze około 4 minut.
Tylko 15 wierszy z 1 000 000 losowo generowanych wartości pasuje do predykatu, ale kosztowne skanowanie tabeli odbywa się 16 razy w celu ich zlokalizowania.
Byłby to dobry kandydat do materializacji wyniku pośredniego. Równoważne przepisywanie tabeli tymczasowej zajęło 25 sekund.
INSERT INTO #T
SELECT *,
ROW_NUMBER() OVER (ORDER BY A) AS RN
FROM T
WHERE B % 100000 = 0
SELECT *
FROM #T T1
CROSS APPLY (SELECT TOP (1) *
FROM #T T2
WHERE T2.A > T1.A
ORDER BY T2.A) CA
Pośrednia materializacja części zapytania do tabeli tymczasowej może czasami być przydatna, nawet jeśli jest oceniana tylko raz - kiedy pozwala na rekompilację reszty zapytania z wykorzystaniem statystyk dotyczących zmaterializowanego wyniku. Przykład tego podejścia znajduje się w artykule SQL Cat When To Break Down Complex Queries .
W pewnych okolicznościach SQL Server użyje bufora do buforowania pośrednich wyników, np. CTE, i uniknie konieczności ponownej oceny tego poddrzewa. Jest to omówione w (zmigrowanym) elemencie Connect. Podaj wskazówkę, aby wymusić pośrednią materializację CTE lub tabel pochodnych . Jednak nie są tworzone żadne statystyki i nawet jeśli liczba zbuforowanych wierszy miała się znacznie różnić od szacowanej, nie jest możliwe, aby plan wykonania w toku dynamicznie dostosowywał się w odpowiedzi (przynajmniej w bieżących wersjach. Adaptacyjne plany zapytań mogą stać się możliwe w przyszłość).