SELECT Explanation, Example FROM Pro.Knowledge
FacebookRSS

Zapytania do danych XML w SQL

Zakres zagadnień dotyczących obecności XML’a w SQL Server jest szeroki. Pisanie zapytań do dokumentów XML, to bardzo często wykorzystywana w praktyce umiejętność. Dlatego też, postanowiłem umieścić rozdział wprowadzający do XML w ramach tego kursu.

Na początek, zaprezentuję ogólną koncepcję standardu XML i jego typowe zastosowania w praktyce. Pokażę gotowe recepty, przykłady – w jaki sposób pisać zapytania SQL do dokumentów XML.

W artykule tym omawiam podstawowe zastosowania funkcji OPENXML() oraz wbudowanych metod typu danych XML. Związanych z nimi wyrażeń XPath i elementów języka XQuery w aspekcie przeszukiwania dokumentów XML i przekształcania ich na postać relacyjną.


Czym jest XML

XML (ang. Extensible Markup Language) określany jest często jako „język”, ale podobnie jak SQL, nie jest to język programowania. XML to najbardziej popularny standard (język) opisu danych. Jego komercyjne pojawienie się pod koniec lat 90-tych, spowodowało wręcz eksplozję zastosowań.

Stosuje się go wszędzie tam, gdzie chcemy przekazać jakieś informacje. W ogólności, na XML możemy spojrzeć jak na opakowanie. To nic innego jak metadane opisujące i nadające sens przesyłanym informacjom.

XML to zbiór zasad, które pozwalają definiować dowolne, ale ściśle ustandaryzowane, hierarchiczne dokumenty. XML (czyli standard) posiada wiele rozszerzeń powiązanych z celem do którego został stworzony.

Jedną z głównych zalet XML, to możliwość silnego typizowania dokumentów (schematy XSD). Możemy dokonywać weryfikacji ich poprawności pod względem określonego schematu. Definiować elementy czy atrybuty jako obowiązkowe lub opcjonalne. Narzucać ograniczenia dla wartości – podobnie jak w konstrukcjach CHECK CONSTRAINT (np. zakresy). Standard ten jest bardzo elastyczny i niezależny od platformy.

Wymianę danych pomiędzy systemami najwygodniej realizować za jego pomocą, choć czasem może być postrzegany jako mocno nadmiarowy. Obecnie większość urządzeń udostępnia na przykład swoje dane konfiguracje, czy zbierane informacje w postaci plików XML.


XML w SQL Server

Podstawowe funkcjonalności związane z XML, pojawiły się już w SQL Server 2000 (np. funkcja OPENXML czy FOR XML). W późniejszych edycjach (od wersji 9 – 2005), XML stał się pełnoprawnym, natywnie wspieranym typem danych. Jego implementacja w SQL Server to szereg dedykowanych funkcjonalności takich jak wbudowane metody tworzenia dokumentów XML, specjalne typy indeksów czy mechanizmy przeszukiwania FLWOR (XQuery).

Przetwarzanie obiektów XML, jest wykonywane w oparciu o jego hierarchiczną strukturę drzewa. Jest to znacznie bardziej efektywny sposób niż metody przeszukiwania tekstów.

Natywny typ danych XML, możemy stosować w różnych miejscach np.

  • przy określaniu typu kolumn w definicjach tabel – do składowania dokumentów XML w bazie relacyjnej.
  • w definicji zmiennych – do przetwarzania dokumentów.
  • jako wartości zwracanych przez funkcję lub parametrów procedur składowanych.

Mamy możliwość tworzenia dokumentów XML bezpośrednio z postaci relacyjnej. Służą do tego polecenia FOR XML, rozszerzające składnię języka SQL. Ale nie o nich jest ten artykuł :)

W drugą zaś stronę – z postaci XML do postaci relacyjnej, możemy dokonać przekształceń za pomocą funkcji OPENXML() lub wbudowanych metod typu XML. Wykorzystując wyrażenia XPath i język zapytań XQuery. W artykule tym ograniczam się właśnie do omówienia sposobów, pobierania interesujących nas danych z dokumentów XML.


Przykład dokumentu XML

Jednymi z publicznie dostępnych dokumentów XML, na których zaprezentuję kilka przykładów zapytań, są publikowane przez NBP, aktualne kursy walut. Jest to typowe zastosowanie tego standardu, służące do przekazywania danych pomiędzy różnymi systemami. Pobierać je może każdy i niezależnie od platformy, przetwarzać zgodnie z własnymi potrzebami.

Fragment takiego pliku poniżej :

Odpytywanie_XML_w_SQL_01

Kilka słów na temat samej konstrukcji. Generalnie XML, daje dużą swobodę użytkownikowi w definiowaniu własnych dokumentów. Możemy określać dowolną strukturę za pomocą elementów oraz związanych z nimi atrybutów

<Element Atrybut=”wartość_atrybutu”> wartość_elementu </Element>

Każdy z nich może mieć przypisaną wartość, a ich typ, zakres czy charakter (obowiązkowy / opcjonalny) może być określony schematem.

Poprawny dokument XML musi posiadać korzeń. W naszym przykładzie jest to element nadrzędny o nazwie <tabela_kursow>. XML jest zawsze ściśle określony, stąd wrażliwy jest na małe / wielkie litery (case sensitive) oraz konieczność zamykania znaczników elementów :

 <nazwa_elementu> wartość </nazwa_elementu>

Poza tymi podstawowymi elementami konstrukcji, w typowym dokumencie XML określane są standardy kodowania, przestrzenie nazw (namespaces), czy powiązania do schematów, opisujących jego strukturę. W naszym przykładzie, mamy bardzo prosty dokument i w jego definicji te elementy nie są określone.


Przeszukiwanie pliku XML w SQL

Pierwszym przykładem, będzie odpytywanie pliku XML z poziomu T-SQL za pomocą funkcji OPENXML i prostych wyrażeń XPath. Wykorzystam do tego dokument XML, który możemy pobrać ze strony zasobów NBP – średnie kursy walut opublikowane 14 marca 2014.

Załóżmy, że chcemy przekształcić taki dokument na postać relacyjną – czyli zasilić naszą bazę, aktualnymi kursami walut.

Schemat tabeli, przechowującej kursy walut w postaci relacyjnej, będzie wyglądał tak :

USE TEMPDB
GO
 
CREATE TABLE [dbo].[KursyWalut](
	[Kod_Waluty] [char](3) NOT NULL,
	[Data] [date] NOT NULL,
	[Kurs_Sredni] [smallmoney] NOT NULL,
	[Przelicznik] [smallint] NOT NULL DEFAULT 1,
 
	 CONSTRAINT [PK_KursyWalut] PRIMARY KEY CLUSTERED 
	(
		[Kod_Waluty] ASC,
		[Data] ASC
	)
)

Oczywiście budując aplikację, należałoby zasilać bazę codziennie. Można to zrealizować na kilka sposobów, korzystając z paczek SSIS, własnych funkcji CLR lub pisząc proste skrypty w Visual Basic’u. W naszym przykładzie, zakładam, że mamy dostępny dokument XML na lokalnym serwerze na dysku D.

Załadowanie pliku XML do zmiennej w T-SQL

Pierwszym krokiem będzie wrzucenie pliku XML na serwer i załadowanie go do zmiennej z poziomu T-SQL. Wykorzystamy do tego celu funkcję OPENROWSET().

DECLARE @xmldata XML
 
SELECT @xmldata=BulkColumn
FROM 
OPENROWSET (BULK 'D:\DaneXML\NBP\a051z140314.xml',SINGLE_BLOB) as T1
 
SELECT @xmldata as Dokument

Odpytywanie_XML_w_SQL_02

Cały dokument, został załadowany do zmiennej typu XML. Możemy go wrzucić bezpośrednio do odpowiedniej kolumny w tabeli lub wyciągnąć z niego tylko istotne dla nas informacje.

Obiekty typu XML, wyświetlane są w Management Studio w postaci linku. Możemy podejrzeć taki dokument, po prostu klikając w taki link.
Odpytywanie_XML_w_SQL_03


Czytanie pliku XML za pomocą OPENXML()

Funkcja OPENXML jest dość wiekowa i dostępna była już w SQL Server 2000. Są z nią bezpośrednio związane dwie specjalne procedury składowane : sp_xml_preparedocument oraz sp_xml_removedocument.

OPENXML pozwala na proste szatkowanie dokumentu XML do postaci relacyjnej. Wyciąganie interesujących danych z XML i np. ładowanie ich do tabeli.

Algorytm pracy z tą funkcją obejmuje 3 kroki :

  1. Przygotowanie dokumentu – czyli załadowanie całego (!) do pamięci CACHE serwera. Służy do tego procedura sp_xml_preparedocument, do której przekazujemy jako parametr, dokument XML. Zwraca ona „uchwyt” do dokumentu – wskaźnik do pamięci CACHE pod którym jest on dostępny.
  2. Właściwe przeszukiwanie dokumentu za pomocą OPENXML, wykorzystując proste wyrażenia XPath do nawigacji po węzłach.
  3. Sprzątanie pamięci CACHE – zwalnianie zasobów za pomocą sp_xml_removedocument. Pamiętajmy o tym, żeby nie pozostawiać śmieci.

Z kolei funkcja OPENXML() przyjmuje 3 (opcjonalnie 4) parametry :

  • Pierwszym jest „uchwyt” (wskaźnik) do dokumentu w pamięci Cache Servera (zwracany przez sp_xml_preparedocument).
  • Drugim, ścieżka (XPath), wskazująca na punkt odniesienia jaki będzie stosowany w pobieraniu danych. Do tego miejsca w strukturze, będziemy mogli odnosić się w bloku WITH() tej funkcji.
  • Trzecim, flaga wskazująca na typ obiektów (elementy, atrybuty czy ich mix), których wartości chcemy zwrócić.
  • Czwartym, opcjonalnym jet definicja przestrzeni nazw

Dodatkowo, z funkcją OPENXML, powiązany jest blok WITH(), w którym możemy nawigować po elementach i atrybutach (z wykorzystaniem XPath), wyciągając z XMLa interesujące nas wartości.

Napiszmy więc skrypt, pobierający dane z pliku XML. Zgodnie z opisanym wcześniej algorytmem, przygotujemy dokument do obróbki, wyciągniemy interesujące nas informacje i wrzucimy je do tabeli dbo.KursyWalut. Na koniec zwolnimy zajęte przez nasz dokument zasoby w pamięci CACHE.

DECLARE @xmldata XML
 
-- 0. Załadowanie pliku do zmiennej
SELECT @xmldata=BulkColumn
FROM 
OPENROWSET (BULK 'D:\DaneXML\NBP\a051z140314.xml',SINGLE_BLOB) as T1
 
-- 1. Przygotowanie dokumentu
DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmldata
 
-- 2. OPENXML - przyjmujemy za punkt odniesienia /tabela_kursow/pozycja
-- interesują nas wartości elementów (flaga = 2) i ładujemy do tabeli dbo.KursyWalut
 
INSERT INTO dbo.KursyWalut
SELECT 
	kod_waluty, Data_publikacji,
	cast ( Replace(kurs_sredni , ',','.') as smallmoney ) as Kurs, przelicznik
FROM
OPENXML(@docHandle, '/tabela_kursow/pozycja', 2)
WITH
(	
	data_publikacji varchar(10) '../data_publikacji',   -- poziom w górę
	kod_waluty	char(3) ,
	kurs_sredni	varchar(100) ,
	przelicznik int
)
 
-- 3. Sprzątanie pamięci 
EXEC sp_xml_removedocument @docHandle
GO
 
SELECT * from dbo.KursyWalut

Fragment zawartości tabeli dbo.KursyWalut, po załadowaniu danych z pliku XML :

Odpytywanie_XML_w_SQL_04

Nawigacja po węzłach za pomocą wyrażeń XPath, jest dość intuicyjna i podobna do określania ścieżki np. w systemie plików czy obiektach HTMLowych. Zresztą standard HTML wywodzi się z tej samej rodziny (SGML).


Przeszukiwanie XML za pomocą natywnych metod

Zaprezentowana funkcja OPENXML, jest bardzo uboga w porównaniu z pełną implementacją typu danych XML.

Typ XML, posiada pięć wbudowanych metod, które dają szerokie możliwości jego przeszukiwania i modyfikacji. Wszystkie bazują na języku zapytań XQuery i związanych z nim wyrażeniach FLWOR. Działanie pierwszych trzech, które stosuje się typowo do wyciągania informacji z dokumentów XML zaprezentuję na przykładach.

Typ danych XML posiada następujące, natywne metody :

  • value() – zwraca pojedynczą wartość, wynik zapytania XQuery w postaci skalarnego typu danych.
  • query() – zwraca fragment (wycinek) dokumentu XML.
  • nodes() – szatkuje dokument XML na wiele mniejszych, podrzędnych.
  • exists() – przeszukuje dokument XML pod kątem istnienia w nim określonych elementów. Zazwyczaj stosowana w budowaniu warunków, wyrażeń logicznych w WHERE.
  • modify() – służy do aktualizacji dokumentu XML.

Zapytanie do dokumentu XML, pokazane w poprzednim przykładzie za pomocą funkcji OPENXML(), możemy zrealizować wykorzystując znacznie szybsze (!), natywne metody value() oraz nodes(). Ich przewagę widać na pierwszy rzut oka. Są czytelniejsze i bardziej wygodne. Prawdziwą swoją moc, pokażą gdy zastosujesz dodatkowo, specjalne typy indeksów XML.

Alternatywne zapytanie do dokumentu XML, pobierające dane za pomocą metod wbudowanych w typ XML, będzie wyglądało tak :

DECLARE @xmldata XML
 
SELECT @xmldata=BulkColumn
FROM 
OPENROWSET (BULK 'D:\DaneXML\NBP\a051z140314.xml',SINGLE_BLOB) as T1
 
 
-- Zastosoowanie XQuery
-- meotda nodes() dzieli 
 
SELECT  
	Tabliczka.element.value('kod_waluty[1]','char(3)') as kod_waluty,
	Tabliczka.element.value('../data_publikacji[1]','varchar(10)') as data_publikacji,
	Cast( Replace(Tabliczka.element.value('kurs_sredni[1]','varchar(10)'),',','.') as smallmoney)  as kurs_sredni,
	Tabliczka.element.value('przelicznik[1]','int') as przelicznik
      -- , Tabliczka.element.value('.././@typ[1]','varchar(10)') as Typ
 
FROM 
	@xmldata.nodes ('/tabela_kursow/pozycja') as Tabliczka(element)
 
-- metoda query() zwraca fragment dokumentu XML
SELECT  @xmldata.query('/tabela_kursow/pozycja[kod_waluty="USD"]/kurs_sredni') as Query
 
-- metoda value() – wartość skalarną
 SELECT  @xmldata.value('(/tabela_kursow/pozycja[kod_waluty="USD"]/kurs_sredni)[1]',
                                                              'varchar(10)') as Value

Odpytywanie_XML_w_SQL_05

Działanie metody nodes() jest podobne do funkcji tabelarycznych, dlatego możemy ją stosować we FROM. Poszatkowała nam ona dokument XML, na zbiór mniejszych elementów. Żeby lepiej sobie to wyobrazić, każdy z nich mógłby wyglądać mniej więcej tak :

    <nazwa_waluty>dolar amerykański</nazwa_waluty>
    <przelicznik>1</przelicznik>
    <kod_waluty>USD</kod_waluty>
    <kurs_sredni>3,0481</kurs_sredni>

Upraszcza to sposób przeszukiwania i odwoływania do fragmentów dokumentu XML.

Co bardzo ważne – tablica elementów (Tabliczka) powstałych na wskutek działania nodes(), nie stanowi osobnych dokumentów. Każdy z nich jest przetwarzany w kontekście dokumentu głównego. Lepszą interpretacją działania nodes() jest utworzenie wektora węzłów (wskaźników) wewnątrz dokumentu głównego.

W ramach węzłów stworzonych przez tą funkcję, możemy sięgać do dowolnych elementów np. wyżej w hierarchii – wartości data_publikacji czy atrybutu typ (jest on zakomentowany w tym przykładzie).

Dwie kolejne kwerendy to proste przykłady zastosowania metod query() oraz value() do przeszukiwania elementów o określonej wartości.


Przeszukiwanie dokumentów XML z określoną przestrzenią nazw

Większość plików XML ma określoną przestrzeń nazw. Obydwie zaprezentowane w tym artykule metody pobierania danych z dokumentów XML, obsługują oczywiście przestrzenie nazw.

Korzystając z natywnych metod, możemy je definiować na różne sposoby. Każde z poniższych zapytań, zwróci poprawny rezultat :

USE AdventureWorks2008
GO
 
-- sposób 1
SELECT  
 hj.Resume.value(
 'declare namespace MyNS="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
 (/MyNS:Resume/MyNS:Name/MyNS:Name.First)[1]' ,'nvarchar(100)')  as FirstName,
 hj.Resume.value(
 'declare namespace MyNS="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
 (/MyNS:Resume/MyNS:Name/MyNS:Name.Last)[1]' ,'nvarchar(100)')  as LastName
FROM [HumanResources].[JobCandidate] hj;
 
-- sposób 2
WITH XMLNAMESPACES (N'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume' as MyNS)
SELECT
 hj.Resume.value(
 '(/MyNS:Resume/MyNS:Name/MyNS:Name.First)[1]' ,'nvarchar(100)')  as FirstName,
 hj.Resume.value(
 '(/MyNS:Resume/MyNS:Name/MyNS:Name.Last)[1]' ,'nvarchar(100)') as LastName
FROM [HumanResources].[JobCandidate] hj;
 
-- sposób 3
WITH XMLNAMESPACES (DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume' )
SELECT hj.Resume.value(
 '(/Resume/Name/Name.First)[1]' ,'nvarchar(100)')  as FirstName,
  hj.Resume.value(
 '(/Resume/Name/Name.Last)[1]' ,'nvarchar(100)') as LastName
FROM [HumanResources].[JobCandidate] hj

Odpytywanie_XML_w_SQL_07

Z kolei definiowanie przestrzeni nazw w przykładzie wykorzystującym funkcję OPENXML, sprowadza się do przekazania tej informacji w momencie przygotowania dokumentu, czyli wywołania procedury sp_xml_preparedocument. Przyjmuje ona opcjonalnie dodatkowy parametr.

Ponieważ funkcja OPENXML(), jest ściśle skojarzona z procedurami składowanymi, zastosowanie jej w celu osiągnięcia podobnego rezultatu (czytania wielu obiektów XML), wymaga działania na kursorach.

Widać więc, że nie jest to wygodna ani efektywna metoda, przeszukiwania dokumentów XML składowanych w tabeli. Być może wydawać się prostsza lub dla niektórych, (historycznie) bardziej bliska. Polecam jednak metody natywne, bo są bardziej uniwersalne i z pewnością szybsze.

Przetworzenie pierwszego dokumentu XML z określoną przestrzenią nazw za pomocą OPENXML, może wyglądać tak :

DECLARE @idoc int, @rootxmlns varchar(100)
DECLARE @doc xml 
 
SELECT TOP 1 @doc=Resume
FROM [HumanResources].[JobCandidate] hj
 
SET @rootxmlns = '<root xmlns:MyNS="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume" />'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc, @rootxmlns
 
SELECT    *
FROM       OPENXML (@idoc, '/MyNS:Resume/MyNS:Name ',1)
WITH
(	
	FirstName nvarchar(100)  './MyNS:Name.First',
	LastName nvarchar(100) './MyNS:Name.Last'
)
 
EXEC sp_xml_removedocument @idoc
GO

Odpytywanie_XML_w_SQL_08


Podsumowanie

Na temat języka zapytań XQuery oraz samego XML’a można napisać wiele artykułów. Z założenia, rozdział ten miał być zwięzłym wprowadzeniem i prezentować gotowe rozwiązania kwerend do dokumentów XML. Dlatego ograniczyłem się tylko do kilku podstawowych przykładów z wykorzystaniem funkcji OPENXML oraz metod typu danych XML – nodes(), value() oraz query(). Jeśli szukasz szerszej wiedzy na ten temat XML – zapraszam na moje kursy.

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.

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.

Import danych z Excela do bazy SQL Server za pomocą T-SQL

Czytanie plików Excel za pomocą T-SQL jest bardzo użyteczne, ale potrafi dostarczyć też problemów, szczególnie na etapie przygotowania środowiska. Sam spędziłem parę ładnych godzin na debugowaniu błędu związanego z czytaniem pliku (chodziło o dostęp do katalogów temp), dlatego postanowiłem zebrać wszystko co ważne w jednym miejscu. O ile sama składnia OPENROWSET jest dość łatwa, o tyle potencjalne problemy związane z ustawieniami środowiskowymi czy właściwościami interfejsów OLE DB mogą pożreć nam sporo czasu na ich rozwiązanie.
Znajdziesz tutaj najważniejsze informacje, które mogą się przydać do czytania plików Excel z poziomu T-SQL a także opis najczęściej pojawiających się błędów i ich rozwiązań.


Funkcja OPENROWSET

Do czytania plików Excel z poziomu T-SQL, idealnie nadaje się funkcja OPENROWSET. Szczególnie wtedy gdy chcemy szybko wrzucić zawartość jakiegoś Excela do bazy aby wykonać na informacjach w nim zawartych jakichkolwiek działań lub zależy nam po prostu aby wykonać import za pomocą skryptu T-SQL.
Jest to funkcja tabelaryczna, zwracająca w wyniku zbiór wierszy, który możemy traktować jak zwykłą tabelę. OPENROWSET podobnie jak inne obiekty tabelaryczne (widoki, zmienne tabelaryczne, tabele tymczasowe etc.) może być użyty tylko w klauzuli FROM zapytania.
Aby móc korzystać z OPERNROWSET potrzebujemy włączonej możliwości wykonywania kwerend rozproszonych. Jest to jedno z ‘advanced optinos’ naszego serwera – warunek konieczny do dalszych przykładów :

-- włączenie na serwerze obsługi kwerened rozproszonych
exec sp_configure 'show advanced options',1
reconfigure
 
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

Teraz w zależności od wersji w jakiej został zapisany plik Excela treść naszego zapytania może wyglądać następująco :

-- Przykład z excel zapisanym w formacie zgodnym z OFFICE 1997-2003 .xls 
-- stary sterownik OLEDB 4.0. Na wymarciu :) sugeruję stosowanie ACE.
 
select * FROM OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\EXCEL1997-2003_TO_READ.xls', 
'select * from [Arkusz1$]');
 
-- ten sam plik zapisany w trybie zgodności 1997-2003 xls czyli OFFICE 2003 
-- nowy uniwersalny provider ACE OLE DB. Odwolanie do Excel 8.0
 
SELECT * FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0', 
'Excel 8.0;Database=D:\EXCEL1997-2003_TO_READ.xls', 
'SELECT  * FROM [Arkusz1$] ');
 
-- plik zapisany w OFFICE 2010 xlsx nowy uniwersalny provider ACE OLE DB
-- istotne jest odwołanie do Excel 12.0;
 
SELECT * FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',                      -- provider_name
'Excel 12.0;Database=D:\EXCEL2010_TO_READ.xlsx', -- provider_string
'SELECT * from [Arkusz1$]')                      -- query lub sam [Arkusz1$], wtedy bez '

Powyższe zapytania działają, jeśli środowisko w którym pracujemy jest odpowiednio skonfigurowane. W dalszej części tego artykułu, znajdziesz wszystkie najważniejsze aspekty związane z konfiguracją serwera, składnią OPENROWSET oraz najczęstszymi problemami z jakimi możesz się zetknąć przy próbie czytania z Excela.

Składnia OPENROWSET

W pokazanych przykładach, funkcja OPENROWSET przyjmuje 3 parametry :
OPENROWSET( provider_name, provider_string, query).

Parametr ‘provider_name

Tu podajemy nic innego jak nazwę (friendly name) interfejsu OLE DB. W przykładach użyłem dwóch interfejsów OLE DB za pomocą których możliwe jest uzyskanie dostępu do plików Excel. Pierwszy to „stary” JET.OLEDB.4.0 który czyta Excela tylko do wersji 2003 (xls) i którego nie polecam stosować (czasem jednak nie mamy wyboru, więc stąd ten przykład).
Drugi, sugerowany to uniwersalny Microsoft.ACE.OLEDB.12.0 dla plików zapisanych w wersjach 1997-2010. Jeśli nie jesteś pewien czy masz go zainstalowanego – sprawdź sterowniki np. w ODBC (biblioteka ACEODBC.DLL).
Reading_excel_odbc_providers
W moim przypadku mam dostępny Microsoft.ACE.OLEDB.12.0 w wersji 14.
Jeśli nie masz jej dostępnej – można go pobrać i zainstalować np. stąd : Microsoft Access Database Engine 2010 Redistributable.
UWAGA ! Jeśli zainstalowałeś ten interfejs, konieczne jest nadanie dla niego stosowanych uprawnień – opisane jest to poniżej – w paragrafie Problemy.

Parametr ‘provider_string

W provider_string, definiujemy zbiór (kolekcję) parametrów tzw. extended properties. Obowiązkowe są dwa – wersja pliku oraz ścieżka. Wersje plików Excel czytane przez Microsoft.ACE.OLEDB.12.0 :

Typ pilkuExtended Properties

Excel 97-2003 Workbook (.xls) “Excel 8.0”
Excel 2007-2010 Workbook (.xlsx) “Excel 12.0 Xml”
Excel 2007-2010 Macro-enabled workbook (.xlsm) “Excel 12.0 Macro”
Excel 2007-2010 Non-XML binary workbook (.xlsb) “Excel 12.0”

Do celów demonstracyjnych najważniejszych parametrów, użyję pliku excel (zapisany w MS Office 2010 jako xlsx). Zawartość pliku demo :
excel_file_example1
Wykonanie kwerendy czytającej tego excela z minimalną wymaganą liczbą parametrów :

SELECT * FROM OPENROWSET(
-- provider_name
'Microsoft.ACE.OLEDB.12.0', 
-- provider_string 
'Excel 12.0;Database=D:\EXCEL2010_TO_READ.xlsx', 
-- query
'SELECT  * FROM [Arkusz1$]');

Reading_excel_01
Jak widać wynik powyżej różni się od faktycznej zawartości pliku – jest to zależne od ustawień parametrów opcjonalnych .

Najważniejsze z opcjonalnych extended properties to :
HDR yes/no – (default = yes) czy ma czytać nazwy kolumn z pierwszego wiersza. Jeśli ustawimy na no – nazwy kolumn będą utworzone jako F1,F2 etc.
IMEX – ImportMixedTypes – interfejsy OLE DB, które wykorzystujemy, mają właściwość „zgadywania” typu danych w kolumnie. Właściwość IMEX odnosi się tylko do kolumn zawierających mieszane typy danych i stara się automatycznie określić typ na podstawie pierwszych N wartości (wartość N określa parametr MaxScanRows). Jeśli na tej podstawie zostanie wybrany np. typ integer, to wszystkie wartości nie pasujące do tego typu, zostaną zamienione na NULL, co zazwyczaj nie jest oczekiwanym zjawiskiem :).

W naszym przykładzie, w kolumnie KolC mamy 2 wartości tekstowe i 3 daty, to w wyniku zapytania otrzymamy kolumnę typu datetime a wszystko co nie pasuje do tego typu zostanie zamienione na NULL (tak jak miało to miejsce w przykładzie powyżej).
W zdecydowanej większości przypadków, gdy chcemy sami określać typ i czyścić dane – ustawiamy parametr IMEX=1, wtedy każda kolumna będzie miała narzucony typ danych jako text :

SELECT * FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0', 
-- provider_string z IMEX
'Excel 12.0;IMEX=1;Database=D:\EXCEL2010_TO_READ.xlsx', 
'SELECT  * FROM [Arkusz1$]');

Reading_excel_02

Parametr ‘query

W tym parametrze określamy zakres danych jakie chcemy wyciągać. Odpytywany plik Excel, może posiadać kilka arkuszy – do konkretnego odwołujemy się po nazwie + $. Aby określić zakres czytanych komórek można to zrobić np. tak Arkusz1$A1:C3 – w wyniku czego otrzymamy tylko dwa wiersze (plus header). Query może przyjmować podstawowe komendy obsługiwane przez danego providera np. grupowanie danych lub być poprostu nazwą arkusza [Arkusz1$] (bez apostrofów).

SELECT * FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0', 
'Excel 12.0;IMEX=1;Database=D:\EXCEL2010_TO_READ.xlsx', 
-- query jako zapytanie
'SELECT kolC, MAX(KolA) as maxi FROM [Arkusz1$A1:C3] group by KolC'

excel_file_example2
W wyniku dostaniemy :
Reading_excel_03
Warto zwrócić uwagę na fakt konwersji na właściwy typ danych w KolC (datetime), nawet przy ustawionym IMEX=1, ponieważ wartości pobierane z tej kolumny są spójne –same daty (bierzemy pod uwagę tylko dwa pierwsze wiersze z danymi A1:C3).

Problemy

Niestety jak to w życiu bywa, wszystko jest ok., ale nie działa. Akurat w przypadku czytania Excela z poziomu SQL zdarza się to nadzwyczaj często bo istnieje wiele uwarunkowań środowiskowych, które mogą nam skutecznie przeszkodzić w zabawie.
Najczęstsze przyczyny problemów z czytaniem excela za pomocą OPENROWSET:

  • Jeśli korzystamy z OLE DB ACE – konieczne przydzielenie odpowiednich uprawnień dla tego dostawcy czyli po instalacji konieczne jest wykonanie :
    USE [master]  
    GO  
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1  
    GO  
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1  
    GO
  • Plik do którego się odwołujemy musi być zamknięty aby móc go czytać z poziomu T-SQL przez OPENROWSET . Niestety SQL Server potrzebuje pliku na wyłączność. Jeśli jest już otwarty, otrzymamy komunikat :
    Msg 7399, Level 16, State 1, Line 2
    The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
    Msg 7303, Level 16, State 1, Line 2
    Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
  • Konto na którym działa usługa SQL Server musi mieć dostęp do zasobów które chcesz czytać, wynika to z konieczności obsługi różnych metod uwierzytelniania (Windows lub SQL Authentication).
  • Problem związany z SQL Server x86 – użytkownik, który wykonuje kwerendę musi mieć dostęp do katalogów tymczasowych używanych przez SQL Server. Jest to podyktowane tym, że tworzona jest kopia tymczasowa pliku do którego się odwołujemy w OPENROWSET w katalogu temp. Potrzebny dostęp r/w do katalogów wskazywanych przez zmienne %TEMP% oraz %TMP% konta na którym działa SQL Server.
    Microsoft sugeruje w tym temacie dwa rozwiązania, które sprowadzają się do tego samego – nadanie uprawnień do aktualnie używanych katalogów TEMP dla użytkowników lub utworzenie nowego, dedykowanego katalogu tymczasowego np. c:\TEMP i ustawienie zmiennych środowiskowych dla konta na którym działa SQL Server, aby %TEMP% i %TMP% wskazywały właśnie na ten nowy zasób.
    W obu przypadkach nadajemy uprawnienia R/W dla wszystkich zainteresowanych użytkowników do tego katalogu oraz full rights dla konta SQL Server.
  • Inne spotykane problemy to kompatybilność sterowników – sprawdź czy zainstalowana wersja OLE DB jest odpowiednia dla twojego serwera x86 / x64.

Niestety obsługa błędów w OLE DB ACE została potraktowana bardzo ogólnie i w większości przypadków tu opisywanych, treść błędu będzie podobna – unknown error.