SELECT Explanation, Example FROM Pro.Knowledge
FacebookRSS

Wielokrotne grupowanie – GROUPING SETS, ROLLUP, CUBE

Tworząc raporty czy zestawienia bezpośrednio z bazy transakcyjnej, wchodzimy na teren analityki biznesowej BI (Business Intelligence) w zwykłej bazie OLTP (OnLine Transaction Processsing). W środowiskach komercyjnych, granica pomiędzy OLTP a bazami OLAP (OnLine Analytical Processing) jest czasem całkowicie zatarta. Zdarza się tak w sytuacji gdy po prostu nie ma wdrożonych, dedykowanych rozwiązań BI. Całość raportowania odbywa się wtedy wprost z bazy transakcyjnej.

Analityka biznesowa (raportowanie), cechuje się wieloma operacjami przekształcania danych i przekuwania ich na konkretną wiedzę. Takie zapytania bywają kosztowne jeśli chodzi o wydajność. Dlatego żeby nie obciążać systemu transakcyjnego, wykonywana są w osobnych strukturach. Jest to powód dla którego tworzone są hurtownie danych i wszelkiej maści rozwiązania BI.

W artykule tym opisuje rozszerzenia T-SQL pozwalające na wielokrotne grupowanie (wstęp do kostek analitycznych). Opisywane tu metody, bazują na zagadnieniach dotyczących grupowania danych i łączenia pionowego zbiorów, prezentowanych w tym kursie we wcześniejszych artykułach.


Wielokrotne grupowanie

W T-SQL mamy dostępnych kilka rozszerzeń składni, pozwalających na sięgnięcie do wiedzy analitycznej. Obok funkcji szeregujących i analitycznych, jednymi z ciekawszych są trzy funkcje pozwalające na wykonywanie wielokrotnych grupowań w zwykłej (pojedynczej) kwerendzie.

Wyobraźmy sobie taki scenariusz (działając na danych z bazy Northwind) :

Chcemy utworzyć raport, przedstawiający informacje o sprzedaży w latach 1997-1998 na kilku poziomach szczegółowości. Zagregowane dane na temat liczby zleceń, chcemy wyświetlić względem lat, kwartałów i na koniec całościowo (jako najwyższy poziom agregacji).

Wielokrotne_grupowanie_01

Tego typu rozbicie wielopoziomowej agregacji, można wykorzystać np. w aplikacji raportującej, w której mamy możliwość „drążenia danych”, czy po prostu w zwykłym raporcie. Powyższy przykład to nic innego jako fragment kostki analitycznej z kilkoma wymiarami czasu.

Znając już operacje na zbiorach (UNION) i możliwości grupowania (GROUP BY), mógłbyś to zadanie rozwiązać za pomocą tych elementarnych konstrukcji, np. w taki sposób :

USE Northwind
GO
 
-- Query1
SELECT 1 as Poziom, NULL as Rok, NULL as Kw , count(OrderId) as OrderQty
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
 
UNION
 
SELECT 2, Year(Orderdate) as Rok, NULL, count(OrderId) as OrderQty
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY Year(Orderdate) 
 
UNION
 
SELECT 3,Year(Orderdate)  as Rok, DatePart(q,Orderdate), count(OrderId) as OrderQty
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY Year(Orderdate)  , DatePart(q,Orderdate)
ORDER BY 2,1

Zaprezentowane powyżej rozwiązanie, łączenia wyników grupowań tej samej kwerendy w różnych kombinacjach, jest mało efektywne.

Z punktu widzenia przetwarzania są to trzy osobne kwerendy, które odpytują te same dane (dublujące się odczyty) i procesowane są niezależnie. Z pomocą przychodzą nam rozszerzenia T-SQL, pozwalające zrealizować tego typu scenariusze znacznie prościej i wydajniej.


ROLLUP

ROLLUP rozszerza funkcjonalność klauzuli GROUP BY, o możliwość tworzenia tzw. kostek analitycznych połówkowych. Funkcja ta, przyjmuje jako parametry, analogicznie jak w zwykłym grupowaniu, atrybuty (nazwy kolumny) tabel wejściowych lub ich przekształcenia.

Jeśli zastosujesz ROLLUP (a,b,c), wykonane zostaną grupowania dla kolejnych kombinacji atrybutów :

  • GROUP BY (a,b,c)
  • GROUP BY (a,b)
  • GROUP BY (a)
  • oraz po całości, czyli GROUP BY () – co w praktyce zapisujemy bez jawnego grupowania (wszystkie kolumny tworzą część danych surowych)

ROLLUP to równoważnik realizacji N+1 grupowań. W przypadku podania trzech (a,b,c) atrybutów, zostaną wykonane 3 + 1 = 4 operacje. Co warto podkreślić, wydajnościowo będzie to znacznie bardziej efektywne zapytanie, niż osobne grupowania i łączenia zbiorów za pomocą UNION, pokazane w poprzednim przykładzie (Query1).

Zobaczmy teraz rozwiązanie naszego scenariusza, czyli na trzech poziomach za pomocą ROLLUP :

-- Query2
SELECT Year(Orderdate)  as Rok, DatePart(q,Orderdate)  as Q , count(OrderId) as OrderQty
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY ROLLUP( Year(Orderdate)  , DatePart(q,Orderdate) )

Wielokrotne_grupowanie_02

Sam zapis kwerendy, jak również jej plan wykonania (Query2) przy tym podejściu jest znacznie prostszy i co ważniejsze – bardziej efektywny. Wystarczy zerknąć na porównanie wydajności zapytań :

Wielokrotne_grupowanie_03

a także statystyki odczytów :

Wielokrotne_grupowanie_04

Odnosząc się do poprzedniego przykładu –3 krotnie została zmniejszona liczba odczytów (tu też widać, że każda kwerenda w UNION, była realizowana niezależnie).

Sam wynik może jeszcze nie być do końca satysfakcjonujący (brakuje informacji o poziomach agregacji), ale tym zajmiemy się w dalszej części tego artykułu – przy okazji omówienia funkcji GROUPING_ID().


CUBE

Drugą funkcjonalnością wielokrotnego grupowanie to CUBE czyli pełna kostka analityczna. Stosujemy ją podobnie jak ROLLUP w poleceniu GROUP BY. Jej działanie to wykonanie grupowania we wszystkich wymiarach. Czyli jeśli podamy 3 atrybuty, to wykonanych zostanie 2^3 = 8 operacji.

Chciałbym przy okazji przypomnieć, jedną z podstawowych zasad pisania zapytań. Zawsze pobieramy tylko takie dane, jakie w danym momencie potrzebujemy. CUBE wykonuje dla nas 2^N-tej grupowań – to kosztuje. Odnieśmy się do naszego przykładu i zamieńmy ROLLUP na CUBE :

SELECT Year(Orderdate)  as Rok, DatePart(q,Orderdate)  as Q , count(OrderId) as OrderQty
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY CUBE(Year(Orderdate)  , DatePart(q,Orderdate))

Wielokrotne_grupowanie_05

Dla dwóch atrybutów w CUBE(), wykonane zostały 4 grupowania. W porównaniu do ROLLUP, pojawił się dodatkowy poziom, tylko po kolumnie Q – kwartały. Niezależnie od roku, mamy informacje o całkowitej liczbie zleceń w kwartałach.

W praktyce, CUBE stosujemy w procesach ETL, rzadko w zwykłych widokach czy zapytaniach, właśnie ze względu na ilość grupowań (pełna kostka).


GROUPING SETS

Na koniec zostawiłem wisienkę na torcie, czyli najbardziej elastyczny i chyba najczęściej wykorzystywany w praktyce sposób na wielokrotne grupowanie.

Za pomocą GROUPING SETS możemy określić konkretne poziomy grupowań. ROLLUP i CUBE opisane wcześniej miały z góry narzuconą liczebność grup. W tym przypadku, możemy jawnie określić zbiory atrybutów po których wykonamy agregacje. Jest to więc także bardziej wydajna, bo oszczędna metoda, gdy nie potrzebujemy tylu wymiarów, ile dają dwie poprzednie metody. Z tego też powodu, jest chętnie stosowana na przykład w widokach, czy zwykłych kwerendach.

Odpowiednikiem ROLLUP i rozwiązaniem naszego scenariusza za pomocą GROUPING SETS będzie poniższa kwerenda :

SELECT Year(Orderdate)  as Rok, DatePart(q,Orderdate)  as Q , count(OrderId) as OrderQty
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY GROUPING SETS
(
	(),
	(Year(Orderdate)) ,
	(Year(Orderdate)  , DatePart(q,Orderdate))
 
)
ORDER BY Rok, Q

Sama struktura jest intuicyjna i wymaga tylko określenia zbiorów grupujących. Będą to atrybuty tabel wejściowych lub ich przekształcenia, analogicznie jak w zwykłym grupowaniu, po których będą wykonywane kolejne agregacje.

Załóżmy, że nie interesuje nas grupowanie podsumowujące całą sprzedaż w zadanym okresie (po całości), ale chcemy wyświetlić tylko raport na dwóch poziomach szczegółowości . Zagregowane dane na temat ilości zamówień w poszczególnych latach i kwartałach.

SELECT Year(Orderdate)  as Rok, DatePart(q,Orderdate)  as Q , count(OrderId) as OrderQty
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY GROUPING SETS
(
	(Year(Orderdate)) ,
	(Year(Orderdate)  , DatePart(q,Orderdate))
 
)
ORDER BY Rok, Q

Wielokrotne_grupowanie_06

W takim scenariuszu, najlepiej właśnie użyć GROUPING SETS – zgodnie z zasadą, pobierania tylko takich danych jakie są nam potrzebne.


Funkcje GROUPING() oraz GROUPING_ID()

Z przedstawionymi powyżej trzema metodami grupowania wielokrotnego, skojarzone są dwie specjalne funkcje skalarne. W grupowaniu wielokrotnym, nie mamy jawnie podanej informacji, na jakim poziomie dana operacja się odbywa. Za pomocą tych funkcji, możemy zidentyfikować, czy dana kolumna, tworzy sekcję grupującą czy nie. Jest to szczególnie przydatne, w ostatecznym sortowaniu wyniku, aby np. otrzymać taki raport jak w pierwszym przykładzie tego artykułu.

Funkcja GROUPING(), przyjmuje jako parametr, dowolny atrybut (zazwyczaj nazwę kolumny), używany w grupowaniu. Zwraca wartość 0 lub 1, w zależności od tego, czy dana kolumna, wchodzi w skład sekcji grupującej (0) czy nie(1). Trochę to na odwrót w przyjętej powszechnie logice, ale można się przyzwyczaić.

Zobaczmy jej działanie na przykładzie :

SELECT Year(Orderdate)  as Rok, DatePart(q,Orderdate)  as Q , count(OrderId) as OrderQty , 
 
 
	GROUPING( Year(Orderdate) ) as [Grupowanie po roku],
	GROUPING( DatePart(q,Orderdate) )  as [Grupowanie po kwartale],
	GROUPING( Year(Orderdate) ) +  GROUPING( DatePart(q,Orderdate) ) as Poziom 
 
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY ROLLUP(Year(Orderdate)  , DatePart(q,Orderdate))
ORDER BY Rok, Poziom DESC

Wielokrotne_grupowanie_07

Na najwyższym poziome grupowania, czyli bez wnikania w wartości lat i kwartałów, zarówno atrybut YEAR(OrderDate) jak i DATEPART(q,OrderDate) nie tworzą sekcji grupującej. Funkcja GROUPING() dla tych atrybutów zwraca wartość 1.

Analogicznie w wierszu drugim i siódmym, mamy grupowanie tylko po latach, bez wnikania w wartości kwartałów. Jest to odpowiednik GROUP BY YEAR(OrderDate). Wartość GROUPING(YEAR(OrderDate)) będzie w tym przypadku 0, ponieważ ten atrybut tworzy sekcję grupującą.

Jak widać na tym przykładzie, za pomocą GROUPING(), możemy obliczyć wartość identyfikującą poziom grupowania.

Drugą funkcją, która już bez zbędnych ceregieli wyznaczy nam od razu identyfikator, jest GROUPING_ID().

Wynik takiej funkcji jest zawsze w postaci bitowej zamienionej na dziesiętną. Poniżej przykład obliczania jej wartości dla ROLLUP ( a, b, c ).

Wielokrotne_grupowanie_08

Odnieśmy się na koniec do naszego przykładu. Będziemy mieli dwa atrybuty grupujące, zatem wartości GROUPING_ID, będzie dla ROLLUP (a , b) zwracała wartość 0, 1 oraz 3.

SELECT Year(Orderdate)  as Rok, DatePart(q,Orderdate)  as Q , count(OrderId) as OrderQty , 
 
 
	GROUPING( Year(Orderdate) ) as [Grupowanie po roku],
	GROUPING( DatePart(q,Orderdate) )  as [Grupowanie po kwartale],
	GROUPING_ID( Year(Orderdate) ,  DatePart(q,Orderdate) ) as PoziomBinarny
 
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY ROLLUP(Year(Orderdate)  , DatePart(q,Orderdate))
ORDER BY Rok, PoziomBinarny DESC

Wielokrotne_grupowanie_09


Podsumowanie

Wielokrotne grupowanie za pomocą GROUPING SETS, ROLLUP I CUBE sprawdza się szczególnie w procesach ETL (Extract, Transform and Load). Rozszerza także możliwości zwykłych kwerend, skracając ich zapis i poprawiając wydajność w porównaniu do tradycyjnych metod wykorzystujących pojedyncze grupowanie i łączenie zbiorów za pomocą UNION.

Funkcje tabelaryczne w SQL

Funkcje tabelaryczne, jak sama nazwa wskazuje, w wyniku działania zwracają tabelę. Różnią się od skalarnych tym, że skoro zwracają pełnoprawny zbiór, to można je używać bezpośrednio we FROM. Krótko mówiąc, możemy traktować je w zapytaniach jak zwykłe tabele.

W artykule tym przedstawię konstrukcje i możliwości funkcji tabelarycznych dostępnych w SQL Server. Sposób ich tworzenia oraz typowe zastosowania. Opiszę tu także, związane szczególnie z nimi, specjalne typy złączeń – CROSS APPLY / OUTER APPLY.


Funkcje tabelaryczne proste (inline)

W SQL Server mamy dostępne dwie grupy funkcji tabelarycznych. Pierwszy z nich – funkcje proste, zwracają wynik pojedynczego zapytania. Są nazywane również widokami sparametryzowanymi. Jest to chyba najlepsza analogia aby zapamiętać ich przeznaczenie. Działają w bardzo podobny sposób jak zwykłe widoki. Ich przewagą jest możliwość stosowania parametrów przekazywanych do ich wnętrza.

W definicji tego typu funkcji, możemy umieścić tylko jedno zapytanie. Nie mamy możliwości stosowania w nich żadnej wyrafinowanej logiki, procedur czy zmiennych – do tego służą funkcje złożone (multi-statement). Z tego powodu konstrukcja jest mocno uproszczona, nie zawiera bloku BEGIN … END. Całość zawarta jest w poleceniu RETURN().

Utwórzmy funkcję, która będzie zwracała numery, daty i wartości zamówień danego Klienta.

CREATE FUNCTION [dbo].[CustOrders] 
(	
  -- początek definicji identyczny jak dla funkcji skalarnych
  -- mamy możliwość określenia od 0 – 1024 parametrów
  @CustomerID nchar(5)
)
RETURNS TABLE      -- funkcja zwracać będzie tabelę
AS
RETURN 
(
   SELECT o.CustomerID, o.OrderID,  o.OrderDate,
          CAST( SUM( UnitPrice * Quantity * (1-Discount)) as smallmoney) as OrderValue
   FROM dbo.Orders o inner join dbo.[Order Details] od on o.OrderID = od.OrderId
   WHERE o.CustomerID = @CustomerID
   GROUP BY o.CustomerID, o.OrderID, OrderDate, ShipCity
 
)
GO

Taka funkcja, może posłużyć np. w aplikacji web do pobierania aktualnych informacji o zleceniach w profilu Klienta. Sposób jej wywołania jest bardzo prosty. Podobnie jak w przypadku innych funkcji (np. skalarnych) wywołujemy je po dwuczłonowej nazwie.

SELECT *
FROM dbo.CustOrders('BOLID') AS TabZam
ORDER BY OrderValue

Funkcje_tabelaryczne_SQL_01

Jak widać funkcja tabelaryczna, zwraca zbiór. Możemy z nią wykonywać złączenia identycznie jak z każdym innym zbiorem (tabelą). Pobierzmy, więc dodatkowe informacje o Kliencie :

SELECT c.CustomerID, c.CompanyName, c.ContactName, c.City, c.Country, TabZam.*
FROM dbo.Customers C INNER JOIN dbo.CustOrders('BOLID') AS TabZam
	ON c.CustomerID = TabZam.CustomerID
ORDER BY OrderValue

Funkcje_tabelaryczne_SQL_02


CROSS APPLY i OUTER APPLY

Omawiając funkcje tabelaryczne, trzeba koniecznie wspomnieć o specjalnych typach złączeń. Poza standardowymi INNER, OUTER JOIN, mamy do dyspozycji dwa dodatkowe bazujące na operatorze APPLY. Dedykowane są dla wyrażeń tabelarycznych, czyli w szczególności funkcji. Są one bardzo przydatne i dość proste w użyciu.

Ich zastosowanie najlepiej od razu pokazać na przykładzie. W poprzednim zadaniu, połączyliśmy tabelę z funkcją, do której jawnie przekazaliśmy parametr CustomerID = ‘BOLID’.

W ten sposób, zostały zwrócone wszystkie zamówienia dla konkretnego Klienta, plus informacje o nim z tabeli dbo.Customers.

W sytuacji, gdy chcemy wielokrotnie uruchomić funkcję, czyli na przykład wywołać ją dla wszystkich Klientów z Hiszpanii, możemy wykorzystać do tego złączenia typu CROSS lub OUTER APPLY.

CROSS APPLY – używamy zazwyczaj do połączenia zbioru (np. tabeli) z dowolną funkcją tabelaryczną. Operator APPLY, pozwala przekazać jako parametr wejściowy, np. do funkcji tabelarycznej, wartość ze zbioru z lewej strony tego złączenia. Brzmi skomplikowanie? Jeden obraz (przykład) wart jest więcej niż 1000 słów.

Załóżmy, że chcemy wyświetlić detale zamówień i Klientów, podobnie jak w poprzednim przykładzie, ale dla wszystkich, pochodzących z Hiszpanii.

Jest ich pięciu :

SELECT c.CustomerID, c.CompanyName, c.ContactName, c.Country  
FROM dbo.Customers c
WHERE Country = 'Spain'
ORDER BY c.CustomerID

Funkcje_tabelaryczne_SQL_03

Stosując operator łączenia wyrażeń tablicowych APPLY, możemy wykonać to zadanie, za pomocą jednej kwerendy. Kolejno dla każdego rekordu, ze zbioru stojącego po lewej stronie operatora, zostanie wywołana nasza funkcja.

SELECT c.CustomerID, c.CompanyName, c.ContactName, c.Country  , TabZam.*
FROM dbo.Customers c 
                     CROSS APPLY                                   dbo.CustOrders(c.CustomerID) TabZam
WHERE Country = 'Spain'
ORDER BY c.CustomerID, OrderValue

Kolejno dla każdego pojedynczego wiersza z tabeli dbo.Customers, wykonywane jest złączenie typu CROSS JOIN. Stąd informacja o danym Kliencie jest połączona z każdy rekordem, zwracanym w danym wywołaniu przez naszą funkcję.

Funkcje_tabelaryczne_SQL_04

Zauważ że w wynikach brakuje Klienta o CustomerID = ‘FISSA’. CROSS APPLY – działa analogicznie do połączenia wewnętrznego INNER JOIN. Klient, nie złożył żadnych zamówień, funkcja dbo.CustOrders() nie zwróciła żadnego rekordu i dlatego identycznie jak w INNER JOIN, rekord został usunięty ze zbioru wynikowego.

Żeby otrzymać w wyniku również rekordy „niedopasowane” z tabeli po lewej stronie, trzeba użyć OUTER APPLY.

Działa analogicznie jak CROSS APPLY z tą różnicą, że jest odpowiednikiem połączenia zewnętrznego. Zatem podobnie jak w LEFT OUTER JOIN, na koniec przetwarzania, do zbioru wynikowego, dorzucone będą jeszcze wszystkie rekordy z tabeli po lewej stronie operatora.

Kwerenda uwzględniająca wszystkich Klientów, wyglądać będzie tak :

SELECT c.CustomerID, c.CompanyName, c.ContactName, c.Country  , TabZam.*
FROM dbo.Customers c OUTER APPLY  dbo.CustOrders(c.CustomerID) TabZam
WHERE Country = 'Spain'
ORDER BY c.CustomerID, OrderValue

Funkcje_tabelaryczne_SQL_05

Jak widać sposób użycia i działanie złączeń bazujących na operatorze APPLY, jest proste i intuicyjne. Warto przy okazji dopowiedzieć, że za jego pomocą, możemy łączyć nie tylko funkcje ale też inne wyrażenia tablicowe – np. podzapytania.

Poprzednią kwerendę wykorzystującą funkcję tabelaryczną, można zapisać alternatywnie w ten sposób :

SELECT c.CustomerID, c.CompanyName, c.ContactName, c.Country  , TabZam.*
FROM dbo.Customers c OUTER APPLY  ( 
 -- zamiast funkcji, wyrażenie tabelaryczne
   SELECT o.CustomerID, o.OrderID,  o.OrderDate,
 CAST( SUM( UnitPrice * Quantity * (1-Discount)) as smallmoney) as OrderValue
   FROM dbo.Orders o inner join dbo.[Order Details] od on o.OrderID = od.OrderId
   WHERE o.CustomerID = c.CustomerID
   GROUP BY o.CustomerID, o.OrderID, OrderDate, ShipCity ) TabZam
 
WHERE Country = 'Spain'
ORDER BY c.CustomerID, OrderValue

Funkcje tabelaryczne złożone (multi-statement)

W sytuacjach, gdy potrzebujemy większej złożoności programistycznej, możemy wykorzystać funkcje typu Multi-Statement. Konstrukcja takiej funkcji jest odrobinę bardziej rozbudowana.

Poza parametrami, które definiujemy identycznie jak w pozostałych typach, obowiązkowa jest definicja zbioru wynikowego. Po słowie kluczowym RETURNS, nadajemy zwracanej tabeli nazwę, oraz określamy kolejno wszystkie jej atrybuty (kolumny). Podobnie jak w przypadku tworzenia zwykłej tabeli, kolumny muszą być opisane konkretnymi typami danych. Możemy przypisać im tutaj wartości domyślne, IDENTITY, czy klucz podstawowy.

W ramach przetwarzania takiej funkcji, musimy do tej wynikowej tabeli wrzucać jawnie (INSERT INTO) elementy.

Napiszmy praktyczny przykład takiej funkcji, która będzie dzieliła nam string na części (słowa). Kluczem podziału będzie separator, dowolnie określany przez użytkownika jako parametr.

Funkcja rozbijająca złączony tekst na kawałki, ma praktyczne zastosowanie. Przydaje się, gdy musimy poszatkować kolekcję przekazywanych argumentów lub dokonać podziału na etapie normalizacji.

Funkcję możemy zapisać przynajmniej na dwa sposoby. Na początek przykład obrazujący funkcje typu Multi-Statement czyli z logiką proceduralną.

CREATE  FUNCTION [dbo].[SplitString]
(
	@InputStr varchar(8000), 
	@Delimiter char(1) = ','
)
-- teraz definicja zbioru wynikowego
RETURNS @temptable TABLE 
(
	SingleStr varchar(8000)
) 
AS
BEGIN
 
	DECLARE @index int 
	DECLARE @tempStr varchar(8000) 
 
 
	SET @index = 1 
	IF LEN( @InputStr )<1 OR @InputStr IS NULL RETURN 
 
	WHILE @index != 0 
	BEGIN 
		SET @index = CHARINDEX(@Delimiter,@InputStr) 
 
		IF @index!=0 
			SET @tempStr = left(@InputStr,@index - 1) 
		ELSE 
			SET @tempStr = @InputStr 
 
		IF(LEN(@tempStr)>0)  -- tu piszemy do tabeli wynikowej
			INSERT INTO @temptable(SingleStr) VALUES(@tempStr) 
 
		SET @InputStr = right(@InputStr,LEN(@InputStr) - @index) 
		IF LEN(@InputStr) = 0 BREAK 
	END
RETURN
END

Z ciekawszych elementów tej konstrukcji jest jawne wrzucanie elementów do tabeli wynikowej (za pomocą INSERT INTO). W wyniku znajdą się tylko takie elementy, które zostaną w ten sposób dodane. Ostatecznie, słowem kluczowym RETURN, przerywamy działanie tej funkcji.

Sposób użycia – identyczny jak w przypadku prostych funkcji tabelarycznych. Na początek przykłady z dwoma różnymi separatorami (domyślny to przecinek) :

-- separator w postaci spacji
SELECT * FROM dbo.SplitString ('Ala ma kota',' ')
 
-- z separatorem domyślnym (przecinkiem)
SELECT * from dbo.SplitString ('12345,65489,23546',DEFAULT)

Funkcje_tabelaryczne_SQL_06

Teraz przykład użycia tej funkcji z operatorem APPLY. Potrzebnych będzie trochę danych testowych. Wygenerujemy tabelę tymczasową, która dla każdego Klienta będzie zawierała zagregowaną kolumnę z ostatnimi pięcioma zleceniami.

USE Northwind
GO
 
WITH Ord as 
(
	SELECT CustomerId, OrderId, 
               ROW_NUMBER() OVER(Partition BY CustomerId order by OrderId DESC) AS RN
	FROM dbo.Orders
)
SELECT o1.CustomerID, 
	STUFF((
 
		SELECT ',' + Cast(o2.OrderID as varchar )
		FROM Ord o2 
		WHERE o2.CustomerID = o1.CustomerID and o2.RN<=5
		FOR XML PATH('')
 
	),1,1,'') as Last5OrderNo into #tempdata
FROM  Ord o1
WHERE o1.RN <= 5
GROUP BY o1.CustomerID
 
SELECT * from #tempData
 
SELECT * from #tempData t CROSS APPLY dbo.[SplitString] (Last5OrderNo ,',')

Funkcje_tabelaryczne_SQL_07

Tabelaryczną funkcję dzielenia stringów na kawałki, można zapisać inaczej, z wykorzystaniem wspólnych wyrażeń tablicowych CTE. Jednak to rozwiązanie, pomimo że znacznie zgrabniejsze, okazuje się mniej efektywne.

Zresztą możesz samemu porównać :

CREATE FUNCTION [dbo].[SplitStringCTE]
(
	@InputStr varchar(8000), 
	@Delimiter char(1) = ','
)
 
RETURNS  TABLE 
AS
RETURN(
 
	WITH SplitString AS (
 
	SELECT
	  CASE WHEN CHARINDEX(@Delimiter, @InputStr)=0 THEN @InputStr
	   ELSE LEFT(@InputStr, CHARINDEX(@Delimiter, @InputStr)-1) END SPLITED,
	  CASE WHEN CHARINDEX(@Delimiter, @InputStr)=0 THEN NULL
	   ELSE RIGHT(@InputStr, LEN(@InputStr)-CHARINDEX(@Delimiter, @InputStr)) END TODO
 
	UNION ALL
 
	SELECT
	  CASE WHEN CHARINDEX(@Delimiter, TODO)=0 THEN TODO
	    ELSE LEFT(TODO, CHARINDEX(@Delimiter, TODO)-1) END SPLITED,
	  CASE WHEN CHARINDEX(@Delimiter, TODO)=0 THEN NULL
	    ELSE RIGHT(TODO, LEN(TODO)-CHARINDEX(@Delimiter, TODO)) END TODO
	FROM SplitString WHERE TODO IS NOT NULL
 
	)
	SELECT SPLITED FROM SplitString
 
)

Podsumowanie

Funkcje tabelaryczne oraz połączenia z operatorem APPLY, są dość często wykorzystywane w praktyce. W dyskusjach na temat wydajności, podobnie jak w przypadku podzapytań skorelowanych, pojawiają się skrajne stanowiska, że zawsze są kosztowne i powinno się ich unikać. Takie podejście mocno zniechęca do testowania alternatywnych rozwiązań, które nieraz okazują się całkiem efektywne.

Skalarne funkcje użytkownika

Funkcje skalarne, zwracają zawsze pojedynczą wartość określonego typu. Z tego względu, można je stosować w różnych miejscach np.:

  • w SELECT tworząc wyrażenia, czy pojedyncze kolumny wynikowe.
  • budując warunki filtracji w WHERE, HAVING lub złączeń we FROM.
  • w definicjach tabel – określając wartości domyślne kolumn lub ograniczeniach sprawdzających (CHECK CONSTRAINT)
  • w innych obiektach programistycznych.

W artykule tym przedstawiam sposób tworzenia i zastosowanie skalarnych funkcji użytkownika w pisaniu zapytań SQL.


Stosowanie skalarnych funkcji użytkownika

Najlepiej wyjaśnić sens funkcji na praktycznym przykładzie.

Stwórzmy funkcję, która będzie zwracała liczbę dni roboczych pomiędzy dwoma datami.

W pracy zawodowej szczególnie raportując np. efektywność procesów musiałem po takie informacje sięgać.

Na początek wersja okrojona, aby nie komplikować zbytnio tematu i skupić się na czystej konstrukcji oraz sposobie użycia. W naszym uproszczeniu, zakładam że liczba dni roboczych pomiędzy datami, to różnica w dniach pomiędzy datą START i END, minus liczba sobót i niedziel występujących w tym przedziale czasu.

Za pomocą T-SQL możemy zapisać ten wzór w następujący sposób (dla przykładu liczba dni roboczych w marcu 2014):

SELECT	(DATEDIFF(dd, '2014-03-01', '2014-03-31') + 1)
	-(DATEDIFF(wk, '2014-03-01', '2014-03-31') * 2)
   	-(CASE WHEN DATENAME(dw, '2014-03-01') = DATENAME(dw,6) THEN 1 ELSE 0 END)
	-(CASE WHEN DATENAME(dw, '2014-03-31') = DATENAME(dw,5) THEN 1 ELSE 0 END)
       as WorkingDays
WorkingDays
-----------
21

(1 row(s) affected)

Jak widać, wzór ten nie uwzględnia żadnych innych dni wolnych (świąt) poza sobotami i niedzielami. W pełni funkcjonalną wersję, uwzględniającą dodatkowe dni wolne oraz szczegółowe wyjaśnienie powyższego wzoru znajdziesz tutaj.

Skoro już wiemy jak obliczyć liczbę dni roboczych, zastosujmy tą wiedzę w praktyce.
Napiszmy kwerendę, która zwróci z bazy Northwind informacje o zleceniach, które były realizowane dłużej niż 20 dni roboczych. Pokażemy informacje o tych zleceniach oraz o czasie ich realizacji dniach roboczych.

USE Northwind 
GO
 
SELECT OrderId, OrderDate, ShippedDate, (DATEDIFF(dd, OrderDate, ShippedDate) + 1)
	-(DATEDIFF(wk, OrderDate, ShippedDate) * 2)
   	-(CASE WHEN DATENAME(dw, OrderDate) = DATENAME(dw,6) THEN 1 ELSE 0 END)
	-(CASE WHEN DATENAME(dw, ShippedDate) = DATENAME(dw,5) THEN 1 ELSE 0 END) as WorkingDays
FROM dbo.Orders
WHERE   (DATEDIFF(dd, OrderDate, ShippedDate) + 1)
	-(DATEDIFF(wk, OrderDate, ShippedDate) * 2)
   	-(CASE WHEN DATENAME(dw, OrderDate) = DATENAME(dw,6) THEN 1 ELSE 0 END)
	-(CASE WHEN DATENAME(dw, ShippedDate) = DATENAME(dw,5) THEN 1 ELSE 0 END) > 20
ORDER BY WorkingDays DESC
OrderId     OrderDate  ShippedDate WorkingDays
----------- ---------- ----------- -----------
10660       1997-09-08 1997-10-15  28
10777       1997-12-15 1998-01-21  28
10924       1998-03-04 1998-04-08  26
…
10788       1997-12-22 1998-01-19  21
10840       1998-01-19 1998-02-16  21
10978       1998-03-26 1998-04-23  21

(35 row(s) affected)

Przyznasz, że samo zapytanie jest długaśne i mało czytelne. Biorąc pod uwagę fakt, że informacja o liczbie dni roboczych pomiędzy datami, może się przydać wielokrotnie, warto utworzyć z tego wzoru funkcję użytkownika. Zresztą tylko w tym przykładzie, powtarzam obliczenia według tego długaśnego wzoru dwukrotnie – w WHERE oraz w SELECT.


Tworzenie i modyfikacja funkcji skalarnych

Funkcje tworzymy, modyfikujemy lub usuwamy za pomocą klasycznych konstrukcji komend DDL (Data Definition Language). Służą do tego polecenia CREATE, ALTER lub DROP FUNCTION.

Tworząc nową funkcję, można skorzystać z szablonu, dostępnego w Management Studio (prawy strzał na podkatalogu z funkcjami skalarnymi > New Scalar-Valued Function).

Funkcje_Skalarne_SQL_UDF_01

W jej definicji, po nazwie, mamy możliwość określenia parametrów wejściowych. Są opcjonalne i można ich zdefiniować całkiem sporo – max. 1024. Następnie po słowie kluczowym RETURNS, koniecznie musi znaleźć się deklaracja typu, zwracanej wartości np. całkowitej (RETURNS int).

Właściwe ciało funkcji, umieszczamy w bloku BEGIN …. END. Słowem kluczowy RETURN wywołujemy jej zakończenie – jawne zwrócenie podanej za nim wartości.

Definicję naszej nowej funkcji możemy zapisać w ten sposób :

  CREATE FUNCTION dbo.LiczbaDniRoboczych 
  (   
       -- Funkcja nie uwzględnia świąt, innych dni wolnych poza sobotą i niedzielą
       -- określenie parametrów wejściowych – są opcjonalne
       -- u nas konieczne są dwa, określające zakres dat
      @StartDate datetime,
      @EndDate datetime
  )
  -- określenie typu zwracanej wartości (to obowiązkowo)
  RETURNS int 
  AS 
  -- ciało funkcji 
  BEGIN
 
	RETURN  (DATEDIFF(dd, @StartDate, @EndDate) + 1)
	        -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
   	        -(CASE WHEN DATENAME(dw, @StartDate) =  DATENAME(dw,6) THEN 1 ELSE 0 END)
    	        -(CASE WHEN DATENAME(dw, @EndDate) =  DATENAME(dw,5) THEN 1 ELSE 0 END)
 
  END;

Nasza funkcja zawiera dwa parametry – początek i koniec zakresu dat. W wyniku jej działania, zwracana jest wartość liczbowa całkowita – typ integer.


Wywoływanie funkcji skalarnych

Korzystanie ze skalarnych funkcji użytkownika jest bardzo proste, ale trzeba pamiętać o kilku zasadach. Przede wszystkim wywołujemy je zawsze za pomocą nazwy przynajmniej dwuczłonowej, zawierającej schemat (u nas jest to dbo) w którym została utworzona.

Dzięki zastosowaniu funkcji, nasza długaśna kwerenda zostanie zgrabnie odchudzona do takiej postaci :

SELECT OrderId, OrderDate, ShippedDate, 
	dbo.LiczbaDniRoboczych(OrderDate, ShippedDate) as WorkingDaysFROM dbo.Orders
WHERE dbo.LiczbaDniRoboczych(OrderDate, ShippedDate) > 20ORDER BY WorkingDays DESC

Zauważ, że funkcja ta jest deterministyczna i z uwagi na swoją prostotę (nie sięga do żadnych innych zbiorów), jej wydajność przetwarzania będzie identyczna jak w przypadku umieszczenia całej logiki w zapytaniu. Nie ponosimy, więc żadnych dodatkowych kosztów a znacznie łatwiej zarządzać takim kodem.


Wartości domyślne

Funkcje skalarne mogą mieć określone wartości domyślne dla parametrów wejściowych. Używa się ich inaczej niż w przypadku procedur składowanych. Wywołując taką funkcję musimy jawnie deklarować chęć użycia wartości domyślnej za pomocą słowa DEFAULT.

W kolejnym przykładzie, utworzymy funkcję, której zadaniem będzie konwersja liczby dziesiętnej na postać liczbową o innej podstawie.

Domyślnie będzie konwertowała zadaną liczbę na postać binarną (podstawa 2). Można też będzie podać jawnie inną podstawę (parametr @Podstawa) w zakresie 2-10.

CREATE FUNCTION dbo.KonwersjaDziesietnej
(
	@LiczbaDoKonwersji bigint,
	@Podstawa int = 2 -- domyślnie na binarną
)
RETURNS varchar(1000)
AS
BEGIN
	-- funkcja działa poprawnie tylko dla podstawy <2-10>
	IF NOT (@Podstawa between 2 and 10) RETURN -1
 
	DECLARE @Wynik varchar(1000) = ''
 
	WHILE @LiczbaDoKonwersji > 0 BEGIN
 
		SET @Wynik = @Wynik + CAST((@LiczbaDoKonwersji % @Podstawa) AS varchar)
		SET @LiczbaDoKonwersji = @LiczbaDoKonwersji / @Podstawa
 
	END
 
	RETURN REVERSE(@Wynik)
 
END
 
 
-- domyślnie czyli z DEFAULT konwersja liczby dziesiętnej na binarną
SELECT dbo.KonwersjaDziesietnej (54,DEFAULT) as Bitowo

Funkcje_Skalarne_SQL_UDF_02
Zwróć uwagę, że tym razem pokazałem ciut większą złożoność programistyczną, którą możemy się posługiwać w definicjach funkcji. Poprzednio całość była zawarta w jednym poleceniu zwracającym wartość – RETURN(). W tym przykładzie wykonuję szereg „skomplikowanych” obliczeń :) dla zwracanej przez tą funkcję wartości.

Zaprezentowane powyżej funkcje, wykonywały stosunkowo proste przekształcenia, nie sięgając właściwie do żadnych zbiorów (tabel). Oczywiście możemy wykonywać za ich pomocą znacznie bardziej złożone operacje.


Porównanie wydajności funkcji użytkownika z innymi metodami

Napiszemy teraz funkcję, która dla danego Klienta będzie zwracała wartość średnią jego zleceń. Porównamy wydajność jej działania z zapytaniem skorelowanym oraz zwykłym łączeniem tabel.

Chcemy wyświetlić zlecenia naszych Klientów, których wartość jest większa niż średnia liczona dla każdego z nich osobno.

Zadanie takie realizować może na kilka sposobów, np. za pomocą podzapytania skorelowanego :

-- Query 1 – z podzapytaniem skorelowanym
SELECT  o1.CustomerID, o1.OrderID , SUM(od1.UnitPrice * od1.Quantity) as OrdValue
FROM dbo.Orders o1 INNER JOIN dbo.[Order Details] od1 on o1.OrderID = od1.OrderID
GROUP BY o1.CustomerID, o1.OrderID
HAVING SUM(od1.UnitPrice * od1.Quantity) > 
(
	SELECT  AVG( OrdValue )  -- obliczenie średniej
	FROM 
	(
	   SELECT o2.CustomerID, o2.OrderID, SUM(od2.UnitPrice * od2.Quantity) as OrdValue
	   FROM dbo.Orders o2 INNER JOIN dbo.[Order Details] od2 on o2.OrderID = od2.OrderID
	   WHERE o2.CustomerID = o1.CustomerID -- dla zleceń danego Klienta
	   GROUP BY o2.CustomerID, o2.OrderID
	) a 
)

Jednym z kluczowych problemów tego zadania, jest obliczanie średniej wartości zleceń, dla każdego z Klientów osobno. Napiszmy więc funkcję obliczającą taką wartość, która będzie przyjmowała jako parametr identyfikator Klienta.

CREATE FUNCTION dbo.CustomerAvgOrderValue
(
	@CustomerID nchar(5)
)
RETURNS decimal(10,2)
BEGIN	
      RETURN ( 
	SELECT AVG( OrdValue )  -- obliczenie średniej
	FROM 
	(
	    SELECT SUM(od2.UnitPrice * od2.Quantity) as OrdValue
	    FROM dbo.Orders o2 INNER JOIN dbo.[Order Details] od2 
                                              ON o2.OrderID = od2.OrderID
	    WHERE o2.CustomerID = @CustomerID -- dla zleceń danego Klienta
	    GROUP BY o2.OrderID
	) a 
       )
END

Teraz możemy przepisać pierwszą kwerendę, na taką, która korzysta z nowo utworzonej funkcji. Będzie z pewnością bardziej czytelna, ale sprawdźmy co stanie się z wydajnością.

-- Query 2 – z użyciem funkcji skalarnej 
 
SELECT o1.CustomerID, o1.OrderID , SUM(od1.UnitPrice * od1.Quantity) as OrdValue 
FROM dbo.Orders o1 INNER JOIN dbo.[Order Details] od1 ON o1.OrderID = od1.OrderID
GROUP BY o1.CustomerID, o1.OrderID
HAVING SUM(od1.UnitPrice * od1.Quantity) > dbo.CustomerAvgOrderValue(o1.CustomerID)
go

Na pierwszy rzut oka, analizując plany wykonania i statystykę odczytów, włączone za pomocą polecenia

SET STATISTICS IO ON

wydaje się że poprawiliśmy dzięki niej wydajność (i to znacznie) :

Funkcje_Skalarne_SQL_UDF_03
Funkcje_Skalarne_SQL_UDF_04

Jeśli przyjrzymy się czasowi wykonania kwerendy i analizie statystyk IO za pomocą np. Profilera lub Extended Events, wynik okaże się znacznie gorszy. Co więcej widać że zdarzają się sytuację w których nie można polegać na prezentowanym planie wykonania zapytania i tylko dogłębna analiza wydajności doprowadzi nas do prawdy o tym co jest grane. Więcej na ten temat znajdziesz w artykule dotyczącym pomiarów wydajności zapytań.

CREATE EVENT SESSION queryperf ON SERVER 
ADD EVENT sqlserver.sql_statement_completed 
ADD TARGET package0.event_file(SET filename=N'D:\PerfTest\PerfTest.xel',
	max_file_size=(2),max_rollover_files=(100)) 
WITH (  MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS, 
             MAX_DISPATCH_LATENCY=120 SECONDS,MAX_EVENT_SIZE=0 KB, 
             MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON); 
 
ALTER EVENT SESSION [queryperf] ON SERVER STATE = START; 
 
DBCC FREEPROCCACHE; 
DBCC DROPCLEANBUFFERS; 
CHECKPOINT
 
SELECT  /* Query1 */ o1.CustomerID, o1.OrderID , SUM(od1.UnitPrice * od1.Quantity) as OrdValue
FROM dbo.Orders o1 INNER JOIN dbo.[Order Details] od1 on o1.OrderID = od1.OrderID
GROUP BY o1.CustomerID, o1.OrderID
HAVING SUM(od1.UnitPrice * od1.Quantity) > 
(
	SELECT  AVG( OrdValue )  -- obliczenie średniej
	FROM 
	(
	   SELECT o2.CustomerID, o2.OrderID, SUM(od2.UnitPrice * od2.Quantity) as OrdValue
	   FROM dbo.Orders o2 INNER JOIN dbo.[Order Details] od2 on o2.OrderID = od2.OrderID
	   WHERE o2.CustomerID = o1.CustomerID -- dla zleceń danego Klienta
	   GROUP BY o2.CustomerID, o2.OrderID
	) a 
)
 
DBCC FREEPROCCACHE; 
DBCC DROPCLEANBUFFERS; 
CHECKPOINT
 
SELECT /* Query2 */ o1.CustomerID, o1.OrderID , SUM(od1.UnitPrice * od1.Quantity) as OrdValue
FROM dbo.Orders o1 INNER JOIN dbo.[Order Details] od1 ON o1.OrderID = od1.OrderID
GROUP BY o1.CustomerID, o1.OrderID
HAVING SUM(od1.UnitPrice * od1.Quantity) > dbo.CustomerAvgOrderValue(o1.CustomerID)
 
DROP EVENT SESSION queryperf ON SERVER; 
 
SELECT *  
FROM   ( 
       SELECT  duration=e.event_data_XML.value('(//data[@name="duration"]/value)[1]','int') 
       ,       cpu_time=e.event_data_XML.value('(//data[@name="cpu_time"]/value)[1]','int') 
       ,       physical_reads=e.event_data_XML.value('(//data[@name="physical_reads"]/value)[1]','int') 
       ,       logical_reads=e.event_data_XML.value('(//data[@name="logical_reads"]/value)[1]','int') 
       ,       writes=e.event_data_XML.value('(//data[@name="writes"]/value)[1]','int') 
       ,       statement=e.event_data_XML.value('(//data[@name="statement"]/value)[1]','nvarchar(max)') 
       ,       TIMESTAMP=e.event_data_XML.value('(//@timestamp)[1]','datetime2(7)') 
       ,       * 
       FROM    ( 
               SELECT CAST(event_data AS XML) AS event_data_XML 
               FROM sys.fn_xe_file_target_read_file(N'D:\PerfTest\PerfTest*.xel', NULL, NULL, NULL) 
               )e 
       )q 
WHERE  q.[statement] LIKE '%Query%'  
ORDER  BY q.[timestamp] ASC

Extended_Test


Podsumowanie

Funkcje skalarne przede wszystkim poprawiają czytelność i wygodę pisania zapytań. Korzystać z nich należy z rozwagą, bo zazwyczaj wykonywane będą dla każdego rekordu niezależnie. Czasem analiza wydajności i wykorzystania ich, wymaga dokładnego sprawdzenia w jaki sposób silnik wykonuje naszą kwerendę. Pochopne wnioski (dziękuję @GRUM za erratę :)) mogą prowadzić do błędnych wniosków i w konsekwencji poważnych problemów wydajnościowych.

Podzapytania skorelowane

W poprzednim rozdziale tego kursu, opisywałem koncepcję konstrukcji podzapytań niezależnych. Można je było uruchomić w całkowitym oderwaniu od kwerendy nadrzędnej. Drugim typem podzapytań są skorelowane, czyli bezpośrednio powiązane z zapytaniem nadrzędnym. Łącznikiem jest jeden lub więcej atrybutów, przekazywanych z zapytania nadrzędnego.

Z punktu widzenia wydajności, taka konstrukcja zazwyczaj (ale nie zawsze) pociąga za sobą konieczność wykonania takiego podzapytania wielokrotnie. Mogą być one dość kosztowne, ale nie jest to sztywną regułą. Podobnie jak inne narzędzia w sprawnych rękach potrafią być bardzo przydatne i wbrew utartym stereotypom – efektywne. W artykule tym, przedstawiam koncepcję i praktyczne zastosowania podzapytań skorelowanych.

Na wstępie pokażę prosty przykład z zastosowaniem tych konstrukcji w SELECT. Porównam ich wydajność z alternatywnymi sposobami realizującymi ten sam cel. W dalszej części zaprezentuję przypadki, których (bez sięgania do logiki programistycznej, kursorów, CLR) nie da się rozwiązać inaczej niż z zastosowaniem podzapytań skorelowanych. Na koniec przypadek łamiący stereotyp – większa wydajność przetwarzania dzięki podzapytaniom skorelowanym.


Podzapytania skorelowane w praktyce

Podzapytania skorelowane podobnie jak niezależne, mogą być użyte w dowolnym miejscu kwerendy.

Rozważmy zadanie, w którym chcemy wyświetlić informacje o wszystkich Klientach oraz liczbie zleceń każdego z nich.

Zapytanie takie możemy zapisać na wiele sposobów – mniej lub bardziej efektywnie. Wykorzystamy podzapytanie skorelowane aby „dokleić” dodatkową informację o liczbie zleceń i pokazać w praktyce ich sposób działania :

USE Northwind
GO
 
SELECT CustomerID, CompanyName, 
(        -- podzapytanie skorelowane
	SELECT COUNT(OrderID) 
	FROM dbo.Orders as O
	WHERE  o.CustomerID = C.CustomerID    -- faktyczna korelacja 
) as LiczbaZlecen
FROM dbo.Customers as C
ORDER BY LiczbaZlecen desc
CustomerID CompanyName                              LiczbaZlecen
---------- ---------------------------------------- ------------
SAVEA      Save-a-lot Markets                       31
ERNSH      Ernst Handel                             30
QUICK      QUICK-Stop                               28
...
CENTC      Centro comercial Moctezuma               1
FISSA      FISSA Fabrica Inter. Salchichas S.A.     0
PARIS      Paris spécialités                        0

(91 row(s) affected)

Na powyższym przykładzie łatwo sobie wyobrazić, teoretyczny proces przetwarzania takiej kwerendy.

Dla każdego Klienta, czyli rekordu z tabeli dbo.Customers, obliczona zostanie wartość liczby zleceń. Korelacja odbywa się w warunku WHERE podzapytania umieszczonego w SELECT (podświetlona linia). Odwołuję się w tym miejscu do wartości atrybutu C.CustomerID, przekazywanej z zapytania nadrzędnego. Widać to po zastosowanym aliasie C tabeli dbo.Customers.

Zgodnie z regułami logicznego przetwarzania zapytań, dla każdego elementu wchodzącego do SELECT, zostanie przekazana wartość CustomerID do podzapytania, czyli po prostu obliczona wartość liczby zleceń. Podzapytanie w tym miejscu musi zwracać wartość skalarną – tak też się dzieje.

Dla pierwszego Klienta z listy (SAVEA), podzapytanie skorelowane będzie takiej postaci :

SELECT COUNT(OrderID) 
FROM dbo.Orders as O
WHERE o.CustomerID = 'SAVEA'  -- wartość SAVEA jest przekazana z zapytania nadrzędnego

Zerknijmy teraz na statystyki i plan wykonania takiego zapytania. Uruchom to zapytanie raz jeszcze z poleceniem SET STATISTICS IO ON oraz załączając plan wykonania ( Query > Include Actual Execution Plan) :

Podzapytania_skorelowane_SQL_01

Podzapytania_skorelowane_SQL_02

Tu niespodzianka. Podzapytanie skorelowane, obliczające liczbę zleceń zostało wykonane tylko raz. Widać to po liczbie skanów tabeli Orders (1) oraz występującym bloku łączenia tabel (MERGE JOIN). Zapytanie zostało więc zrealizowane w taki sam sposób, jak zwykłe złączenie dwóch tabel.

To samo zadanie mogliśmy rozwiązać za pomocą takiej kwerendy :

SELECT c.CustomerID, c.CompanyName,  COUNT(OrderID) as LiczbaZlecen 
FROM dbo.Customers as C left join dbo.Orders as O
	ON o.CustomerID = C.CustomerID
GROUP BY c.CustomerID, c.CompanyName
ORDER BY LiczbaZlecen DESC

Jeśli przerabiasz ten kurs od początku, to z pewnością nie jesteś zaskoczony tym faktem. W SQL pomiędzy teorią i praktyką przetwarzania zapytań, jest jeszcze optymalizator. Jego rola to automatyczny wybór efektywnej metody dostępu do danych i możliwie najtańszy koszt realizacji zadania.

Optymalizator zapytań, na etapie przetwarzania, potrafi niejawnie przekształcić kwerendę z podzapytaniem skorelowanym w zwykłą która stosuje proste złączenia.

Nie zwalnia nas to jednak z obowiązku świadomego pisania zapytań. Stosując podzapytania skorelowane, musimy przynajmniej w rachunku teoretycznym, zakładać mniej efektywny algorytm działania. Szczególnie w odniesieniu do prostszej metody łączenia tabel (o ile jest to możliwe).

Porównajmy jeszcze raz te dwie kwerendy :

SET STATISTICS TIME ON
 
-- zapytanie skorelowane
SELECT CustomerID, CompanyName, 
(
	SELECT COUNT(OrderID) 
	FROM dbo.Orders as O
	WHERE o.CustomerID = C.CustomerID
 
) as LiczbaZlecen
FROM dbo.Customers as C
 
-- zwykle łączenie tabel z grupowaniem
SELECT c.CustomerID, c.CompanyName,  COUNT(OrderID) as LiczbaZlecen 
FROM  dbo.Customers as C LEFT JOIN dbo.Orders as O
	ON o.CustomerID = C.CustomerID
GROUP BY  c.CustomerID, c.CompanyName

Podzapytania_skorelowane_SQL_03

W obydwu przypadkach plany wykonania, koszty, statystyki odczytów są identyczne.

Skomplikujmy teraz odrobinę nasz scenariusz. Chciałbym pokazać, że podzapytania skorelowane na dłuższą metę, są jednak mniej efektywne niż zwykłe łączenie tabel.

Oprócz informacji o Klientach i całkowitej liczby zleceń, interesował nas będzie dodatkowo numer ostatniego zlecenia.

Zmodyfikujmy zatem oba te zapytania. W skorelowanym dodamy kolejne, obliczające MAX(OrderID) a w łączącym dwie tabele po prostu wyciągniemy ad hoc tą dodatkową informację, którą praktycznie mamy na tacy.

-- zapytanie zawierające podzapytania skorelowane
SELECT CustomerID, CompanyName, 
(      
	SELECT COUNT(OrderID) 
	FROM  dbo.Orders as O
	WHERE o.CustomerID = C.CustomerID
 
) as LiczbaZlecen
, 
(
	SELECT MAX(OrderID) 
	FROM  dbo.Orders as O
	WHERE o.CustomerID = C.CustomerID
 
) as OstatnieZlecenie
FROM  dbo.Customers as C
 
-- zwykle łączenie tabel z grupowaniem
SELECT  c.CustomerID, c.CompanyName,  COUNT(OrderID) as LiczbaZlecen, 
		MAX(OrderID) as OstatnieZlecenie 
FROM  dbo.Customers as C LEFT JOIN dbo.Orders as O
	ON o.CustomerID = C.CustomerID
GROUP BY  c.CustomerID, c.CompanyName

Podzapytania_skorelowane_SQL_04

Podzapytania_skorelowane_SQL_05

Tym razem różnica jest już zauważalna. W przetwarzaniu zapytania skorelowanego, dodane zostało kolejne złączenie i zwiększyło to liczbę odczytów (dodatkowy skan tabeli Orders). W zwykłym łączeniu tabel wszystko po staremu. Widać więc w takim podejściu słabość tych konstrukcji, choć jeszcze pełnego potencjału tragedii nie pokazałem.

Numerowanie rekordów za pomocą podzapytań skorelowanych

Innym przykładem zastosowań podzapytań skorelowanych, które wypadają bardzo słabo w porównaniu z alternatywnymi metodami, jest automatyczna numeracja wierszy.

We wcześniejszych wersjach serwera niż SQL Server 2005, nie było dostępnej funkcji ROW_NUMBER(). Jednym ze sposobów nadania kolejnych numerów rekordom, było użycie podzapytania skorelowanego. Ten przykład w praktyce nie ma już raczej zastosowania. Czasem jeszcze można spotkać SQL Server 2000 na produkcji i tam tego typu przypadki się pojawiają.

Obrazuje to ten najgorszy scenariusz, narzut podzapytań skorelowanych. Rzeczywiste, iteracyjne wykonanie się kwerendy podrzędnej, dla każdego wiersza osobno w praktyce.

SELECT o1.CustomerID, o1.OrderID,
	(
		SELECT count(*) as ile
		FROM dbo.Orders o2
		WHERE o2.CustomerID = o1.CustomerID and o1.OrderID <= o2.OrderID 
	) as RowNum
FROM dbo.Orders o1
ORDER BY o1.CustomerID, RowNum

Podzapytania_skorelowane_SQL_06

Teraz plan i koszty wykonania. Zwróć uwagę na liczbę skanów i logicznych odczytów tabeli dbo.Orders. Dla każdego zlecenia, podzapytanie skorelowane zostanie uruchomione niezależnie.

Ponieważ mamy 830 zleceń, ta podkwerenda również zostanie uruchomiona 830 razy.

Podzapytania_skorelowane_SQL_07

Podzapytania_skorelowane_SQL_08

Dla porównania, kwerenda wykonująca to samo zadanie, czyli numerująca kolejne rekordy z zastosowaniem funkcji ROW_NUMBER() (dostępna od SQL Server 2005).

Porównajmy obydwie kwerendy aby zaobserwować procentowy stosunek ciężaru ich realizacji.

-- podzapytanie skorelowane
SELECT o1.CustomerID, o1.OrderID,
	(
		SELECT COUNT(*) as ile
		FROM dbo.Orders o2
		WHERE o2.CustomerID = o1.CustomerID AND o1.OrderID <= o2.OrderID 
	) as RowNum
FROM dbo.Orders o1
ORDER BY o1.CustomerID, RowNum
 
-- numerowanie z wykorzystaniem ROW_NUMBER()
SELECT o1.CustomerID, o1.OrderID, 
	ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY OrderID ) as RowNum
FROM dbo.Orders o1

Podzapytania_skorelowane_SQL_09

Podzapytania_skorelowane_SQL_10

Te wyniki pewnie obrzydzą Ci dość mocno stosowanie podzapytań skorelowanych. Tym bardziej że już sam ich zapis, debuggowanie jest mniej czytelne niż podzapytań niezależnych. Są to charakterystyczne cechy tych konstrukcji i główny powód dla którego unika się stosowania zapytań skorelowanych w praktyce.

Podkreślam to celowo bo w większości zastosowań, kwerendy można zapisać efektywniej, bez konieczności korelacji w podzapytaniu. Są jednak sytuacje ratujące honor podzapytań skorelowanych.


Agregacja wartości znakowych w grupowaniu

Istnieją scenariusze, w których wręcz nie możemy się obyć bez podzapytań skorelowanych. Często zależy nam na tym, aby rozwiązaniem była pojedyncza kwerendy realizująca określony cel. W sytuacjach gdy nie chcemy, lub nie możemy sięgać po techniki programistyczne, podzapytania skorelowane, bywają jedynym rozwiązaniem.

Bardzo praktycznym przykładem, jest zastosowanie podzapytań skorelowanych do agregacji wartości znakowych (stringów) w grupowaniu.

W T-SQL brakuje wbudowanej funkcji agregacyjnej znaków, podobnej do znanych z Oracle LISTAGG() czy WM_CONCAT(). W SQL Server można takie działania wykonywać w logice programistycznej za pomocą funkcji CLR, kursorów T-SQL czy też w warstwie pośredniej LINQ. Nie zawsze jednak są to akceptowalne i optymalne rozwiązania.

To co chcemy osiągnąć, to w pojedynczym zapytaniu grupującym np. po kolumnie Country, zagregować wartości znakowe kolumny City.

Podzapytania_skorelowane_SQL_11

Do tego celu możemy użyć podzapytań skorelowanych oraz możliwości jakie daje funkcja FOR XML (dość nieszablonowy sposób jej zastosowania).

SELECT C1.Country,
       STUFF((
 
	SELECT ',' + C2.City
	FROM dbo.Customers C2
	WHERE C2.Country = C1.Country
	FOR XML PATH('')
 
         ),1,1,'') as Cities
FROM dbo.Customers C1
WHERE Country LIKE 'S%'
GROUP BY C1.Country

Korelacja odbywa się w SELECT. Wbudowana funkcja STUFF (string, 1, 1, ‘’) obcina pierwszy znak zadanego stringu (usunięcie niepotrzebnego, pierwszego przecinka). A całość agregacji załatwia nam sprytnie FOR XML PATH.

Jest to stosunkowo dobra metoda agregowania wartości znakowych, bo w łatwy sposób za pomocą wbudowanych funkcji języka T-SQL, w jednej kwerendzie możemy osiągnąć cel. Jeśli byśmy chcieli agregować wartości bez duplikatów, w podzapytaniu wystarczy dodać DISTINCT.

Przetwarzanie tego zapytania, wiąże się ze wspominanym wcześniej narzutem charakterystycznym dla podzapytań skorelowanych. Jest to cena jaką płacimy za upakowanie całej logiki w jednym zapytaniu. Dla każdego rekordu osobno, będzie faktycznie uruchamiane podzapytanie. W naszym przypadku trzy rekordy = trzy razy.

Podzapytania_skorelowane_SQL_12


Wydajność zapytań skorelowanych

Z reguły podzapytania skorelowane dają gorsze rezultaty (wydajnościowo) niż zwykłe podzapytania lub inne techniki (funkcja okna OVER). Zdarzają się jednak sytuacje, gdy możemy uzyskać poprawienie wydajności dzięki zapytaniom skorelowanym. Może to być na przykład kwerenda w której chcemy pokazać informacje o ostatnim zamówieniu (najnowszym) dla każdego Klienta.

Do dalszej analizy wymagana jest podstawowa znajomość struktur indeksów klastrowych i nieklastrowych.

Zapytanie skorelowane możemy zapisać np. tak :

SELECT CustomerId, OrderId
FROM dbo.Orders as o1
WHERE  OrderID = (
          SELECT MAX(OrderID) 
          FROM dbo.Orders as O2 
          WHERE CustomerID = o1.CustomerID 
                 )
CustomerId OrderId
---------- -----------
WOLZA      11044
WILMK      11005
WHITC      11066
…
ANTON      10856
ANATR      10926
ALFKI      11011

(89 row(s) affected)

W tym zapytaniu filtrujemy rekordy zleceń, ograniczając się tylko do najświeższych (najwyższy numer) dla każdego z Klientów (CustomerID). To co jest najciekawsze – to plan wykonania tego zapytania – można powiedzieć idealny.

Podzapytania_skorelowane_SQL_13

W procesie przetwarzania jest tylko jedno przeszukanie (SEEK) indeksu nieklastrowego [CustomerID] z tabeli dbo.Orders, które załatwia dostęp do wszystkich potrzebnych danych. Zwracane są z tego indeksu rekordy, z maksymalnym OrderID dla danego Klienta. Dzieje się tak, ponieważ na tabeli dbo.Orders jest utworzony indeks klastrowy składający się właśnie z kolumny OrderID i wartości te, są dostępne na poziomie liści przeszukiwanego indeksu.

Nie trzeba nigdzie więcej szukać danych. Wszystko już mamy. Potrzebowaliśmy tylko CustomerID (to jest wartość klucza nieklastrowego) oraz wartość OrderID, która to z kolei jest kluczem indeksu klastrowego. Takie podejście jest najbardziej efektywne.

Przeanalizujemy inne próby rozwiązania tego zadania, np. z wykorzystaniem funkcji ROW_NUMBER, grupowania i łączenia tabel oraz z zastosowaniem funkcji okna :

-- Query 2 - poprawne ale tylko dla informacji o numerze zlecenia per Klient
-- w ten sposób nie dotrzemy do wszystkich atrybutów tego najnowszego zlecenia.
SELECT CustomerId, MAX(OrderID)
FROM dbo.Orders
GROUP BY CustomerId
 
-- Query 3
SELECT CustomerId, OrderId
FROM ( 
        SELECT OrderId, CustomerID, 
             ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY OrderID DESC) AS MaxOrder
        FROM dbo.Orders ) AS a
WHERE MaxOrder = 1 
 
-- Query 4
SELECT o.CustomerId, o.OrderId  
FROM dbo.Orders o INNER JOIN 
     ( 
        SELECT CustomerID, MAX(OrderID) AS OrderID 
        FROM dbo.Orders 
        GROUP BY CustomerID
      ) AS MaxOrd   ON  o.OrderID = MaxOrd.OrderID
 
-- Query 5 - najgorsze z możliwych 
SELECT DISTINCT CustomerID, 
    MAX(OrderID) OVER(PARTITION BY CustomerID ORDER BY OrderID DESC) as MaxOrder
FROM  dbo.Orders

Podzapytania_skorelowane_SQL_14a
W żadnym z tych przypadków nie osiągniemy takiej efektywności, jakie daje prezentowane jako pierwsze – rozwiązanie z podzapytaniem skorelowanym. Zapytanie numer 2 i 3, pod względem ilości odczytów jest na równi z podzapytaniem skorelowanym.

Podzapytania_skorelowane_SQL_15a

Występują tu jednak takie operacje jak dodatkowe sortowanie czy skan (przeglądanie wszystkich wartości) indeksu, które skutecznie obniżają jego wydajność. Dystans ten będzie się zwiększał wraz z liczebnością elementów w tabeli.

Przykład ten można jeszcze rozwinąć, aby dobitniej pokazać zysk jaki daje w takim scenariuszu podzapytanie skorelowane. Załóżmy że będziemy chcieli wyciągnąć więcej detali niż tylko numer ostatniego zamówienia. Ten dość prosto można było osiągnąć poprzez grupowanie i funkcję MAX(). Teraz interesuje nas jeszcze kraj dostawy ostatniego zamówienia danego Klienta.

Zmodyfikujemy teraz nasze kwerendy. Kwerendę numer 2 od razu musimy skreślić z dalszej analizy, bo bezpośrednio za jej pomocą nie osiągniemy celu. Porównajmy te cztery metody :

-- Query 1
SELECT CustomerId, OrderId, ShipCountry
FROM dbo.Orders as o1
WHERE  OrderID = (
          SELECT MAX(OrderID) 
          FROM dbo.Orders as O2 
          WHERE CustomerID = o1.CustomerID 
                 )
 
-- Query 2
SELECT CustomerId, OrderId, ShipCountry
FROM ( 
        SELECT OrderId, CustomerID, ShipCountry,
             ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY OrderID DESC) AS MaxOrder
        FROM dbo.Orders ) AS a
WHERE MaxOrder = 1 
 
-- Query 3
SELECT o.CustomerId, o.OrderId  , o.ShipCountry
FROM dbo.Orders o INNER JOIN 
     ( 
        SELECT CustomerID, MAX(OrderID) AS OrderID 
        FROM dbo.Orders 
        GROUP BY CustomerID
      ) AS MaxOrd   ON  o.OrderID = MaxOrd.OrderID
 
-- Query 4 - najgorsze z możliwych 
SELECT DISTINCT CustomerID, 
    MAX(OrderID) OVER(PARTITION BY CustomerID ORDER BY OrderID DESC) as MaxOrder,
	ShipCountry
FROM  dbo.Orders

Podzapytania_skorelowane_SQL_16

Podzapytania_skorelowane_SQL_17
Tym razem, pierwsze trzy zapytania wypadają praktycznie identycznie (dwa pierwsze kosztują dokładnie tyle samo). Zauważ, że w pierwszym przypadku straciliśmy zysk jaki dawało przeszukiwanie indeksu nieklastrowego. Wszędzie są obecnie pełne skany – słabo.

Możemy w prosty sposób poprawić tą sytuację, modyfikując nasz indeks nieklastrowy CustomerID, który poprzednio wykorzystywaliśmy w bardzo efektywny sposób (SEEK). Wystarczy dodać dodatkową kolumnę do definicji klucza (ShipCountry) lub zrobić to bardziej oszczędnie – przekształcając go w indeks pokrywający (INCLUDE) :

USE [Northwind]
GO
 
DROP INDEX [CustomerID] ON [dbo].[Orders]
GO
 
CREATE NONCLUSTERED INDEX [CustomerID] ON [dbo].[Orders]
(
	[CustomerID] ASC
) 
INCLUDE ( [ShipCountry] )

Teraz ponownie widać zysk jaki daje podzapytanie skorelowane. Znów jest wykonywane w nim (Query 1) jedynie przeszukiwanie indeksu nieklastrowego. Na poziomie liści znajdują się wszystkie informacje i nie trzeba nigdzie więcej sięgać.

Podzapytania_skorelowane_SQL_18

Oczywiście można dywagować, że to kwestia indeksów, sposobu ich posortowania (w kwerendzie z ROW_NUMBER to główny narzut). Tak czy siak, ten przypadek potwierdza postawioną tezę.


Podsumowanie

Istnieją scenariusze w których wydajność podzapytań skorelowanych może być lepsza, od innych – w teorii znacznie lżejszych rozwiązań. W większości przypadków staramy się unikać ich stosowania ze względu na wysoki koszt, gorszą czytelność i ogólnie większą złożonością kwerend.

Na pewno warto znać te konstrukcje, bo niektóre zadania, możemy rozwiązać właściwie tylko za ich pomocą. Szczególnie gdy jesteśmy ograniczeni do pojedynczego zapytania, bez możliwości sięgania po techniki programistyczne, tabele tymczasowe, kursory czy funkcje.

Funkcje użytkownika

Funkcje użytkownika (User Defined Functions UDF) to obiekty programistyczne, które pozwalają znacząco rozszerzyć możliwości pisania kwerend.

Są one dość często stosowane – przedewszystkim w zapytaniach, dlatego postanowiłem umieścić ich opis w ramach tego kursu. Zakres funkcji w SQL, podobnie jak miejsc w których możemy je użyć jest naprawdę szeroki (procedury składowane, wartości domyślne, ograniczenia sprawdzające etc..). Rozdział ten ogranicza się tylko do funkcji użytkownika tworzonych w T-SQL w aspekcie pisania zapytań.

Obiekty te, podobnie jak w typowych językach programowania, mogą realizować rozmaite zadania. Używamy je do przetwarzania jakiś danych, celem uproszczenia zapisu kwerendy. Są opakowaniem logiki aplikacyjnej w wygodny obiekt i przeniesienie jej do warstwy serwera bazodanowego. Raz utworzone, mogą być stosowane w wielu miejscach.


Korzyści płynące ze stosowania funkcji użytkownika

  • poprawienie czytelności kodu i wygoda ich wielokrotnego użycia w różnych miejscach.
  • mogą zmniejszać ruch sieciowy – liczba danych (przesłanych bajtów) związanych z wywołaniem funkcji jest zazwyczaj znacznie mniejsza niż cała jej definicja, składowana na serwerze.
  • za pomocą funkcji CLR, można realizować zadania niemożliwe do wykonania w czystym T-SQL. Złożone obliczenia efektywniej jest wykonywać np. w obiektowych językach programowania.
  • mają możliwość wywołań rekurencyjnych.

Oczywiście jest też druga strona medalu. Funkcje zazwyczaj są kosztowne, szczególnie gdy wykonywane są dla każdego elementu niezależnie – skalarne (!). Z tego powodu bywają zmorą administratorów i są jednym z pierwszych punktów uwagi w momencie analizy wydajności zapytań. Ponadto dla funkcji tabelarycznych, szacowanie kardynalności zbioru zwykle jest wypaczone. Są traktowane jako black-box i przyjmowana wartość liczebności zbioru wynosi dla nich zawsze 1. Biorąc pod uwagę fakt, że za ich pomocą możemy wykonywać naprawdę skomplikowane działania – koszt ten czasem bywa akceptowalny. Jak widać odpowiedź na pytanie czy stosować funkcje czy nie, jak zwykle brzmi – to zależy ;)


Pisanie zapytań SQL z wykorzystaniem funkcji użytkownika

Funkcje ze swej definicji, mogą przyjmować parametry wejściowe i zwracają zawsze jakiś obiekt. W SQL, będzie to zawsze zbiór (tabela) lub wartość skalarna. Jeśli zaprotestujesz, że przecież wartość skalarna to też zbiór (jednoelementowy , opisany jedną kolumną) to znaczy, że uważnie przerabiałeś ten kurs lub po prostu dobrze czujesz koncepcję zbiorów.

Wyjątkowo w odniesieniu do funkcji, istnieje sztywny podział na takie, które zwracają wartości skalarne lub zbiory. Mamy więc do czynienia z dwoma głównymi rodzajami tych obiektów – odnajdziesz je w kategorii Programmability > Functions , w drzewie obiektów swojej bazy danych :

Funkcje_Uzytkownika_SQL

Ich strukturę, sposób tworzenia i przykłady zastosowań, opisuję szczegółowo w dedykowanych im artykułach :

  • FUNKCJE SKALARNE – zwracające pojedynczą wartość.
  • FUNKCJE TABELARYCZNE – zwracające zbiór. Wśród tych, rozróżniamy jeszcze dwa podtypy :
    • proste (inline table UDF)
    • złożone (multi-statement table valued functions)

Funkcje od strony programistycznej

Warto wiedzieć, że funkcje (zarówno wbudowane jak i użytkownika) możemy dodatkowo podzielić, ze względu na możliwość przewidzenia wyniku.

Deterministyczne to takie, które przyjmując określone parametry wejściowe, po każdym wywołaniu zwracają identyczny wynik. Są więc, można powiedzieć, z góry określone (łac. determinare – określić).
Przykładem takich funkcji jest większość wbudowanych matematycznych czy tekstowych, np. :

SELECT ABS(-2) as AbsVal, Left('Ala ma kota',3) as Lewy3
AbsVal      Lewy3
----------- -----
2           Ala

(1 row(s) affected)

Przy każdym wywołaniu funkcji wartości bezwzględnej ABS(), dla konkretnej wartości argumentu, równego np. -2, zwracany jest ten sam wynik. Podobnie jak w funkcji LEFT.

Z kolei funkcje niedeterministyczne, to takie, które pomimo tych samych wartości parametrów na wejściu, zwracają za każdym razem inny wynik. Przykładem może być funkcja NEWID(), generująca kolejny unikalny identyfikator czy GETDATE(), zwracająca datę i czas systemowy.

Select NEWID() as UniqueIdentifier, GETDATE() as DataCzas
UniqueIdentifier                     DataCzas
------------------------------------ -----------------------
EBF78558-F7E7-4A91-A1DA-17245C9CAA68 2014-03-06 09:31:25.893

(1 row(s) affected)

Co prawda powyższe, przykładowe funkcje nie przyjmują parametrów, ale przecież brak parametru to też konkretny przypadek wywołania.

Determinizm lub jego brak jest o tyle istotny, że w przypadku tych pierwszych, możliwe jest zastosowanie indeksów np. w widokach, których wartości kolumn są wyliczane na podstawie takich funkcji. Ponadto jest szansa, że funkcja deterministyczna zostanie wykonana tylko raz (np. GETDATE()) dla wszystkich rekordów.

Są to aspekty z zakresu projektowania i programowania baz danych i poruszam je w detalach na szkoleniach programistycznych.