Łączenie danych z funkcjami

Łączenie danych z funkcjami to zapytanie zestawiające tabelę z wynikiem funkcji tabelarycznej, np. przez APPLY.

Na czym polega zestawienie tabeli z funkcją tabelaryczną

Łączenie danych z funkcjami polega na potraktowaniu wyniku funkcji zwracającej tabelę jako źródła wierszy dołączanego do tabeli, widoku albo innego zapytania. Funkcja tabelaryczna może przyjmować wartości pochodzące z aktualnie przetwarzanego wiersza. Dzięki temu zapytanie nie musi najpierw tworzyć osobnego, niezależnego zbioru wynikowego. Dla każdego wiersza źródła może zostać wywołana funkcja, a jej wynik zostaje zestawiony z danymi tego wiersza.

W składni Transact-SQL służą do tego operatory CROSS APPLY i OUTER APPLY. Oba operatory pozwalają odwołać się wewnątrz prawej części wyrażenia do kolumn dostępnych w lewej części. Jest to istotna różnica względem zwykłego, niezależnego podzapytania, które nie może bezpośrednio korzystać z wartości bieżącego wiersza tabeli zewnętrznej. Prawa część może być funkcją tabelaryczną, podzapytaniem albo konstrukcją generującą zależny wynik.

CROSS APPLY i OUTER APPLY a obecność wiersza wynikowego

CROSS APPLY zachowuje się podobnie do złączenia wewnętrznego. Jeżeli funkcja tabelaryczna nie zwróci żadnego wiersza dla danego rekordu z lewej strony, ten rekord nie pojawi się w wyniku końcowym. Operator jest przydatny wtedy, gdy wynik funkcji ma być wymaganym uzupełnieniem danych podstawowych. Przykładowo tabela klientów może zostać zestawiona z funkcją wyliczającą zamówienia dla konkretnego identyfikatora klienta. Klienci bez pasujących zamówień zostaną pominięci przez CROSS APPLY.

OUTER APPLY zachowuje wszystkie wiersze z lewej strony. Gdy funkcja nie zwróci danych, kolumny pochodzące z prawej strony przyjmą wartość NULL. Ten wariant jest odpowiedni, gdy brak wyniku funkcji ma być widoczny w raporcie, a nie ma usuwać rekordu głównego. Wybór operatora wpływa więc bezpośrednio na liczność wyniku oraz na sposób interpretacji braku powiązanych danych. Nie jest to wyłącznie różnica składniowa.

W praktyce warto sprawdzić, czy funkcja może zwrócić wiele wierszy dla jednego rekordu lewej tabeli. Jeżeli tak się stanie, wiersz tabeli podstawowej zostanie powtórzony dla każdego wiersza zwróconego przez funkcję. Może to być zamierzone, gdy potrzebne są szczegółowe rekordy, ale może też prowadzić do pozornego zawyżenia liczby danych. Ograniczenie wyniku funkcji, zastosowanie odpowiednich warunków oraz świadome wybranie kolumn pomagają uniknąć błędnej interpretacji raportu.

Parametry zależne od bieżącego wiersza

Najważniejszą cechą takiego połączenia jest korelacja pomiędzy lewą i prawą stroną operatora. Parametr funkcji może otrzymać na przykład identyfikator urządzenia, datę, kod kategorii albo inną wartość z aktualnego rekordu tabeli. Funkcja jest wtedy uruchamiana w kontekście konkretnego wiersza, a jej wynik można filtrować, sortować lub ograniczać w ramach tego samego zapytania. Taki model dobrze opisuje sytuacje, w których reguła pobierania danych zależy od wartości znajdujących się w tabeli nadrzędnej.

Przykładowa konstrukcja może wyglądać następująco: SELECT u.Id, f.Status FROM Urzadzenia AS u CROSS APPLY dbo.PobierzStatus(u.Id) AS f;. Funkcja PobierzStatus otrzymuje identyfikator z tabeli Urzadzenia, a jej kolumny są dostępne pod aliasem f. Sam przykład pokazuje mechanizm korelacji, ale poprawność zapytania zależy od rzeczywistej definicji funkcji, typów parametrów i nazw obiektów w konkretnej bazie. Funkcja musi zwracać wynik zgodny z użyciem przewidzianym w zapytaniu.

Podczas analizy należy zwrócić uwagę na typy danych, możliwość wystąpienia wartości NULL oraz warunki filtrujące umieszczone wewnątrz funkcji. Różnica między brakiem wiersza a wierszem zawierającym wartości NULL może mieć znaczenie dla dalszych obliczeń. Istotne jest również ustalenie, czy funkcja nie wykonuje kosztownych operacji dla każdego rekordu osobno. Przy dużej liczbie wierszy takie wywołania mogą obciążyć procesor, pamięć albo podsystem dyskowy, dlatego wynik powinien być oceniany razem z planem wykonania i rzeczywistym czasem działania.

Kontrola poprawności zapytania i diagnostyka wyników

Diagnostykę należy rozpocząć od porównania liczby wierszy w tabeli źródłowej z liczbą wierszy po zastosowaniu operatora. Spadek liczności przy CROSS APPLY może oznaczać brak wyników funkcji, a nie błąd samego zapytania. Z kolei wzrost liczności może wynikać z tego, że funkcja zwraca kilka rekordów dla jednego wiersza. Warto przeanalizować przykładowe identyfikatory, dla których wynik jest pusty, powielony albo zawiera nieoczekiwane wartości.

Przydatne jest tymczasowe wyświetlenie kolumn identyfikujących rekord lewej strony oraz wszystkich kolumn zwracanych przez funkcję. Pozwala to ustalić, czy parametr został przekazany właściwie i czy funkcja używa oczekiwanej wartości. Należy także sprawdzić kolejność filtrowania. Warunek zastosowany wewnątrz funkcji może zmienić liczbę zwracanych wierszy, natomiast warunek zastosowany dopiero w zapytaniu zewnętrznym działa na już zestawionym wyniku. Te dwa miejsca nie zawsze są równoważne pod względem wyniku i kosztu wykonania.

W przypadku nieprawidłowych rezultatów trzeba zweryfikować definicję funkcji, uprawnienia do obiektów, zgodność typów parametrów oraz plan wykonania. Sama obecność APPLY nie gwarantuje ani poprawności logicznej, ani dobrej wydajności. Błąd może wynikać z niewłaściwego klucza korelacji, niejednoznacznych założeń dotyczących pustego wyniku albo niekontrolowanego powielania rekordów. Przy zastosowaniach na laptopie lub komputerze PC analiza takiego zapytania wymaga dostępu do konkretnej bazy, definicji funkcji i przykładowych danych, ponieważ bez tego można potwierdzić składnię, lecz nie rzeczywistą semantykę wyniku.