CTE w T-SQL można porównać do tymczasowej tabeli. Prawdziwa siła CTE jednak tkwi w możliwości rekurencyjnego przechodzenia przez węzły. Ogólna zasada tworzenia wygląda następująco:

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition ) 

exoression_name stanowi nazwę obiektu CTE. Następnie listujemy wszystkie kolumny, które będą występować w CTE. Za słowem kluczowym AS występuje selekcja danych – tak jak w zwykłych widoku. Stwórzmy więc obiekt CTE:

WITH FirstCte (FirstName,LastName)
AS
(
    SELECT FirstName,LastName from Address where ID_PERSON in (1,2,3);
)

Korzystanie z CTE niczym nie różni się od wykonywania zapytań na zwykłej tabeli:

select FirstName FROM FirstCte order BY LastName desc;

CTE służy jednak głównie do rekurencyjnych zapytań. Klasycznym przykładem jest struktura pracowników w firmie – każdy ma przełożonego itp. Dla testów stwórzmy tabelę, która zawiera 3 kolumny (ID_EMPLOYEE,ID_MANAGAER,TITLE). Definicja CTE składa się zawsze z przynajmniej dwóch zapytań  rozdzielonych klauzulą UNION ALL. Pierwsze zapytania to tzw. kotwica (anchor) – generuje pierwszy wiersz drzewa. Druga z kolei wykorzystuje zdefiniowaną kotwicę oraz odwołuje się do samego obiekt CTE, tym samym powodując rekurencję. Podejrzewam, że w tej chwili jest to kompletnie niezrozumiałe więc przejdźmy od razu do przykładu:

WITH EmployeeStructureCte (ManagerID, EmployeeID, Title, Level)
AS
(
    SELECT e.ManagerID, e.EmployeeID, e.Title, 
        0 AS Level
    FROM Employees as e WHERE ManagerID IS NULL --kotwica
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, e.Title,
        Level + 1
    FROM Employees as e    
    INNER JOIN EmployeeStructureCte AS cte
        ON e.ManagerID = cte.EmployeeID;
)

Prześledźmy po kolei działanie powyższej struktury (która może wydawać się dziwna na pierwszy rzut oka). Najpierw wywoływana jest kotwica, która zwróci:

Nic nadzwyczajnego – prosta zapytanie SELECT. Drugie zapytanie zwróci np. taki wiersz (lub grupę wierszy):

Następny krok to już czysta rekurencja- wykorzystujemy właśnie zwrócony wiersz i szukamy podwładnych np.:

Rekurencja zakończy się w momencie gdy będzie “brakowało” podwładnych:) Wtedy nastąpi połączenie wszystkich wierszy za pomocą UNION ALL. Wygenerowany CTE można wykorzystać np. aby zwrócić wyłącznie pracowników danego szczebla:

SELECT * FROM EmployeeStructureCte where Level = 3