Indeksy są dobrym mechanizmem na optymalizację często powtarzających się zapytań. Przykładowo rozważmy następujące zapytanie:

SELECT FirstName,LastName FROM Persons where Age>30

Dla dużej ilości danych, wykonanie powyższego kodu może trochę potrwać. Jeśli dodalibyśmy indeks na kolumnie Age, czas wykonania znaczącą by się skrócił ponieważ dane byłyby w pewnym stopniu sortowane w pamięci (w dużym uproszczeniu dane są przechowywane w strukturze drzewiastej, która znacznie przyśpiesza selekcję).

Tematem postu jednak nie są podstawy indeksów a jego typy. Clusted to podstawowy typ, który jest automatycznie tworzony dla kluczy głównych. Dla tabeli może powstać wyłącznie jeden taki indeks ponieważ stanowi on tak naprawdę fizyczne uporządkowanie danych. Wiersze dodawane do tabeli, domyślnie są zatem sortowanie po kluczu głównym. Każde wywołanie po kluczu indeksu clusted jest zawsze  zapytaniem na tzw. covered indeks – indeksie pokrytym. W przypadku powyższego zapytania, o ile indeks został stworzony na kolumnie Age, zapytanie zostanie w pełni zoptymalizowane. W przypadku indeksów non-clusted wszystkie kolumny występujące w zapytaniu (także FirstName, LastName) muszą być dołączone do indeksu w sekcji include – tworzymy więc indeks na Age, z dołączanymi kolumnami FirstName oraz LastName. W przeciwnym przypadku (brak kolumn w include) stworzenie indeksu nie byłoby w stanie w pełni zoptymalizować zapytania.

Liczba indeksów non-clusted jest również ograniczona – maksymalnie 249 chodź to zależy od wersji systemu bazodanowego.

To co warto zrozumieć, to fakt, że indeksy optymalizują wyłącznie operacje selekcji. Ze względu na potrzebę przebudowania drzewa po każdej operacji INSERT, DELETE, UPDATE po dodaniu indeksów są one znacznie wolniejsze.

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