Funkcja tabelaryczna

Funkcja tabelaryczna to funkcja SQL zwracająca zestaw wierszy, wykorzystywana podobnie jak tabela w zapytaniu.

Funkcja zwracająca wynik w postaci tabeli

Funkcja tabelaryczna, określana także jako table-valued function, jest obiektem języka SQL, którego wynikiem nie jest pojedyncza liczba, tekst ani data, lecz zestaw wierszy opisany przez określone kolumny. Dzięki temu można używać jej w zapytaniu podobnie jak tabeli lub widoku. Funkcja przyjmuje opcjonalne parametry wejściowe, wykonuje zdefiniowaną logikę i zwraca relację, czyli uporządkowany logicznie zbiór rekordów. Każdy rekord może zawierać wiele kolumn, a typy tych kolumn powinny być znane na etapie definiowania funkcji albo wynikać z jej deklaracji, zależnie od konkretnego silnika SQL.

Przykładowym zastosowaniem jest funkcja przyjmująca identyfikator klienta i zwracająca listę jego zamówień. Zamiast powtarzać w wielu zapytaniach warunki łączenia tabel klientów, zamówień i pozycji zamówień, można umieścić tę logikę w jednym obiekcie. Następnie wynik funkcji może zostać użyty w klauzuli FROM, połączony z innymi tabelami przez JOIN, ograniczony warunkiem WHERE albo poddany agregacji, na przykład za pomocą COUNT lub SUM. Funkcja nie staje się jednak fizyczną kopią danych: podczas wykonania silnik oblicza jej wynik zgodnie z definicją i przekazanymi parametrami.

Parametry, kolumny i sposób wywołania

Najważniejszą cechą funkcji tabelarycznej jest określony kontrakt wejścia i wyjścia. Kontrakt wejścia obejmuje liczbę, kolejność i typ parametrów, natomiast kontrakt wyjścia opisuje kolumny zwracanych wierszy. Parametrem może być na przykład identyfikator użytkownika, zakres dat, status dokumentu albo kod produktu. Wartości parametrów wpływają na zbiór rekordów, ale nie powinny zmieniać struktury wyniku, jeżeli aplikacja oczekuje stabilnego schematu. Zmiana nazwy kolumny, typu danych lub ich kolejności może wymagać modyfikacji zapytań korzystających z funkcji.

Wywołanie funkcji tabelarycznej zwykle wymaga umieszczenia jej w części zapytania przeznaczonej dla źródeł danych. W zależności od silnika SQL może być potrzebna składnia charakterystyczna dla funkcji tabelarycznych, w tym nawiasy po nazwie funkcji i alias tabeli. Alias ułatwia odwoływanie się do kolumn oraz poprawia czytelność bardziej rozbudowanych zapytań. Jeżeli funkcja zależy od kolumn bieżącego wiersza innego źródła danych, silnik może wymagać specjalnego rodzaju połączenia bocznego, znanego w niektórych implementacjach jako lateral join albo odpowiednik konstrukcji APPLY. Tego szczegółu nie można przenosić bezpośrednio między różnymi systemami baz danych, ponieważ składnia i możliwości zależą od konkretnego produktu.

Różnica między funkcją tabelaryczną a widokiem

Widok jest zapisanym zapytaniem, które zazwyczaj udostępnia wynik o stałej strukturze i nie przyjmuje parametrów w taki sam sposób jak funkcja. Funkcja tabelaryczna może natomiast generować wynik zależny od argumentów przekazanych podczas wywołania. Z tego powodu nadaje się do wielokrotnego używania tej samej logiki dla różnych klientów, przedziałów czasu lub identyfikatorów. Widok może być dobrym źródłem danych dla zapytań ogólnych, natomiast funkcja tabelaryczna sprawdza się wtedy, gdy wynik musi być filtrowany według wartości przekazanych przez zapytanie.

Funkcja tabelaryczna różni się również od procedury składowanej. Procedura może wykonywać operacje modyfikujące dane, zwracać wiele zestawów wyników, ustawiać kody powrotu i prowadzić rozbudowane sterowanie przebiegiem. Funkcja tabelaryczna jest przeznaczona przede wszystkim do użycia jako element wyrażenia SQL zwracającego dane. W wielu silnikach obowiązują ograniczenia dotyczące efektów ubocznych funkcji, dlatego nie należy zakładać, że każda funkcja może wykonywać INSERT, UPDATE lub DELETE. Dokładny zakres dozwolonych operacji, sposób definiowania typu zwracanego wyniku oraz obsługa wyjątków są zależne od dokumentacji używanego silnika.

Wydajność i kontrola wyników funkcji

Wydajność funkcji tabelarycznej zależy od jej implementacji, liczby przetwarzanych wierszy, filtrów, indeksów oraz sposobu, w jaki optymalizator zapytania ocenia jej koszt. Funkcja zawierająca proste zapytanie z selektywnymi warunkami może działać poprawnie także dla dużych tabel, jeżeli silnik może wykorzystać odpowiednie indeksy. Problemy pojawiają się między innymi wtedy, gdy funkcja wykonuje kosztowne operacje dla każdego wiersza zewnętrznego zapytania, zwraca znacznie więcej danych niż potrzebuje odbiorca albo ukrywa złożoną logikę przed optymalizatorem. Sam fakt umieszczenia zapytania w funkcji nie gwarantuje poprawy szybkości działania.

Podczas diagnostyki należy sprawdzić rzeczywisty plan wykonania, liczbę zwracanych wierszy, czas odczytu oraz obciążenie procesora i pamięci, korzystając z narzędzi dostępnych w danym systemie baz danych. Warto porównać wykonanie funkcji z równoważnym zapytaniem zapisanym bezpośrednio, a także przetestować przypadki brzegowe: brak wyników, jeden rekord, bardzo duży zakres danych i wartości parametrów o nierównomiernym rozkładzie. Należy zweryfikować, czy typy parametrów odpowiadają typom kolumn, ponieważ niejawne konwersje mogą uniemożliwić użycie indeksu. Trzeba też kontrolować wartości NULL, duplikaty oraz kolejność wyników. Funkcja tabelaryczna nie gwarantuje uporządkowania wierszy, jeżeli zapytanie wywołujące nie zawiera jawnej klauzuli ORDER BY.