04.08.17r.
W tym wpisie przybliżamy temat CTE (Common Table Expression), czyli tymczasowego wyniku zapytania używanego w operacjach takich jak SELECT, INSERT, UPDATE czy DELETE. Poznaj, jak skutecznie wykorzystać CTE w zapytaniach SQL, w tym w zapytaniach rekurencyjnych.
CTE - jest tymczasowym wynikiem zapytania budowanym na potrzeby instrukcji takich jak: SELECT, INSERT, UPDATE, DELETE. Koncepcyjnie przypomina tabele tymczasową lub podzapytanie. Podobieństwo do tabeli tymczasowej podyktowane jest faktem przechowania wyniku na potrzeby zapytania SELECT, INSERT, UPDATE, DELETE. Podobnie jak podzapytanie czas dostępu do danych CTE jest ograniczony - tylko na czas wykonania polecenia SELECT, INSERT, UPDATE lub DELETE. Znamienną cechą wyrażeń CTE jest możliwość odwoływania się do samego siebie co oznacza możliwość pisania zapytań rekurencyjnych. Cecha ta jest bardzo przydatna podczas odpytywania tabel , które posiadają relacje same do siebie (tabela pracownik z numerem przełożonego. Rysunek 1).
Expression_name – nazwa wyrażenia CTE
Column_name – opcjonalne aliasy zewnętrzne dla CTE_query_definition, ważne przy polach obliczeniowych nie posiadających aliasów bezpośrednio w klauzuli SELECT wyrażenia CTE.
CTE_query_definition – Zapytanie SELECT, którego wynik wypełnia wyrażenie CTE. SELECT musi spełniać podobne wymagania jak w przypadku CREATE VIEW. W przypadku użycia kilku zapytań SELECT muszą on być połączone jednym z operatorów: UNION ALL, UNION, EXCEPT lub INTERSECT aby powstał jeden zbiór wynikowy wypełniający CTE.
Wspólne wyrażenie tabeli może być przydatne podczas używania funkcji okna (temat omawiany w artykule T-SQL bez tajemnic cz. 2) funkcje okna mogą być użyte tylko w klauzuli SELECT lub
ORDER BY, co uniemożliwia nam filtrowanie kolumn w jednym zapytaniu SELECT.
Powyższy przykład przedstawia błąd 4108 o treści „Windowed functions can only appear in the SELECT or ORDER BY clauses.” Server nie pozwala na wybranie najdroższych produktów (pozycja w rankingu 1 kolumny pricerank) w każdej kategorii poprzez odwołanie się do wyniku działania funkcji RANK(). Problem ten możemy w prosty sposób obejść za pomocą CTE.
Wybrane uwagi z dokumentacji MSDN:
Następujące uwagi dla nie rekurencyjnych wyrażeń CTE.
• Wyrażenie CTE musi poprzedzać pojedynczą instrukcję SELECT, INSERT, UPDATE, lub DELETE, które odwołuje się do kolumn wyrażenia.
• Można definiować kilka wyrażeń CTE jedno po drugim oddzielając przecinkiem, ale nie można zagnieżdżać klauzuli WITH samej w sobie.
• Nie można używać następujących poleceń w zapytaniu budującym CTE (argument CTE_query_definition):
o ORDER BY (bez TOP)
o INTO
o OPTION dla podpowiedzi optymalizatora
o FOR BROWSE
• Należy poprzedzić wyrażenie CTE średnikiem w batch’u.
W następnym artykule zajmiemy się rekurencyjnymi wyrażeniami CTE.
------------------------------------------------------
Autor:
Adam Białas – MCT, MCSE: Data Platform, Business Intelligence