SELECT Explanation, Example FROM Pro.Knowledge
FacebookRSS

Konfiguracja Database Mail w SQL Server

Mail_smallWysyłanie powiadomień mailowych z poziomu serwera bazodanowego, jest niezwykle przydatną funkcjonalnością. Typowe zastosowanie to automatyczne przekazywanie informacji o statusie zadań administracyjnych, procesów utrzymaniowych czy ETL (zazwyczaj błędach lub ostrzeżeniach).

Możesz je wykorzystać równie dobrze w typowych aplikacjach klienckich. Informować użytkowników o pojawiających się zdarzeniach np. zleceniach, zmianach statusów czy przekroczeniu określonych wskaźników, bezpośrednio z poziomu SQL Server.

W artykule tym, znajdziesz opis koncepcji Database Mail w SQL Server. Zaprezentuję także przykładową konfigurację za pomocą procedur wbudowanych. Co istotne, Database Mail jest dostępny także w darmowej wersji SQL Server Express, choć bez graficznego wizzarda i widocznej osobnej gałęzi obiektów w Management Studio.


Architektura Database Mail

Konfiguracja, widoki systemowe oraz procedury składowane dotyczące Database Mail, znajdują się są w bazie systemowej MSDB. Działanie oparte jest na usłudze Brokera (Service Broker), czyli wbudowanemu w SQL Server mechanizmowi do przesyłania wiadomości, kolejkowania i obsługi zdarzeń.

Wysyłanie maili, z punktu widzenia użytkownika, sprowadza się do wywołania procedury składowanej sp_send_dbmail, która wrzuca wiadomość do kolejki. Pojawienie się w niej nowego rekordu, wyzwala proces odpowiedzialny za fizyczne wysłanie maila. Tym procesem jest zewnętrzny program DatabaseMail.exe, uruchamiany automatycznie przez proces SQL Server.

Z konfiguracją Database Mail, związane są obiekty kont pocztowych i profili – przedstawione na poniższym schemacie :

Database_Mail_00

Profile – mogą mieć przypisane jedno lub wiele kont pocztowych z których będą wysyłane wiadomości. Jest to proste zwiększenie niezawodności, na wypadek gdy podstawowy serwer (konto) SMTP jest nie dostępny.

W momencie przypisania konta do profilu (procedura sysmail_add_profileaccount_sp), określany jest parametr sequence_number (priorytet) zgodnie z którym, następuje próba wysłania wiadomości. Najpierw przez konto z najniższym sequence numer. W przypadku niepowodzenia, podejmowana jest próba wysłania przez kolejne.

Konta pocztowe, mogą być przypisane do jednego lub wielu profili (współdzielone) np. konto backupowe.

Każdemu użytkownikowi, możemy nadać uprawnienia (proc. sysmail_add_principalprofile_sp
) do wielu profili pocztowych. Jeden z nich, może być określony jako domyślny – wtedy nie trzeba będzie podawać jawnie nazwy profilu w momencie wywoływania procedury wysyłającej maila. Nadanie uprawnień do profilu nie jest jednoznaczne z tym, że już użytkownik będzie mógł wysyłać maile. Musi mieć także uprawnienia do wykonywania procedury msdb.dbo.sp_send_dbmail czyli dostęp do bazy MSDB ze stosownymi uprawnieniami.

Do zarządzania uprawnieniami w tym zakresie, istnieje dedykowana rola w bazie MSDB DatabaseMailUserRole. Najwygodniej jest dopisać do niej każdego użytkownika (grupę), która ma mieć możliwość wysyłania i dostępu do widoków związanych z mailami (np. sysmail_allitems). Widoki te są o tyle bezpieczne, że użytkownik będzie widział tylko te wiadomości, których jest autorem.

Wprowadzona architektura zależności pomiędzy kontami, profilami a użytkownikami daje elastyczność i jest łatwo skalowalna.


Konfiguracja Database Mail w SQL Server

Pierwszym krokiem, jest przygotowanie instancji SQL Server, czyli włączenie obsługi Database Mail.

sp_configure 'show advanced options',1
reconfigure
go
 
sp_configure 'Database Mail XPs',1;
reconfigure
go

Jak już wspominałem, Database Mail bazuje na usłudze Brokera i musi on być również aktywny w bazie MSDB. Jego status możemy sprawdzić za pomocą :

select name ,   is_broker_enabled 
from sys.databases
where name = 'msdb'

Database_Mail_01
W przypadku gdy nie jest aktywny (0), należy go włączyć :

USE master
GO
ALTER DATABASE msdb SET  ENABLE_BROKER WITH NO_WAIT
GO

Teraz, możemy zabrać się za właściwą konfigurację profili i skojarzonych z nimi kont pocztowych. Od razu podpowiem, że większość problemów z wysyłaniem maili, jest związane właśnie z błędami w tym kroku. Należy upewnić się co do parametrów konta SMTP , poprzez które chcemy wysyłać maila.

Na początek dodam dwa nowe konta – na serwerach home.pl oraz Gmail. Konfiguracja portów, konieczność uwierzytelniania SSL są z reguły identyczne dla większości providerów.

Use MSDB
GO
 
EXECUTE msdb.dbo.sysmail_add_account_sp
     @account_name = 'Gmail',
     @email_address = 'mojekonto@gmail.com',
     @display_name = 'Gmail Public', 
     @description = 'Konto Main', 
     @mailserver_name = 'smtp.gmail.com',
     @mailserver_type = 'SMTP',
     @port = 587,
     @username = 'mojekonto@gmail.com',
     @password = 'haslomaslo', 
     @enable_ssl = 1;
 
EXECUTE msdb.dbo.sysmail_add_account_sp
     @account_name = 'SQLPEDIA',
     @email_address = 'adresemail@sqlpedia.pl',
     @display_name = 'backup', 
     @description = 'Konto Main', 
     @mailserver_name = 'domena.home.pl',
     @mailserver_type = 'SMTP',
     @port = 587,
     @username = 'adresemail@sqlpedia.pl',
     @password = 'haslomaslo', 
     @enable_ssl = 1;
 
-- weryfikacja kroku
select * from sysmail_server

Kolejnym krokiem, jest utworzenie profili. Zróbmy dwa – jeden będzie wykorzystywany jako publiczny, drugi prywatny dla procesów utrzymaniowych.

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Public',
@description = 'Dla wszystkich userów' ;
 
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Priv',
@description = 'Tylko admin' ;
 
-- weryfikacja kroku
select * from sysmail_profile

Database_Mail_02
Teraz trzeba powiązać konta pocztowe z profilami. Profil publiczny będzie posiadał jedno konto a prywatny dwa, podstawowe i backupowe.

-- Public
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Public', -- nazwa profilu
@account_name = 'Gmail',  -- nazwa konta
@sequence_number =1 ;
 
-- Priv
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Priv',
@account_name = 'SQLPEDIA',
@sequence_number =1 ;
 
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Priv',
@account_name = 'Gmail',
@sequence_number =2 ;
 
-- weryfikacja kroku
select * from sysmail_profileaccount

Database_Mail_03

Ostatnią rzeczą która trzeba wykonać aby zwykły użytkownik mógł wysyłać maile, jest nadanie stosownych uprawnień. Nadajmy więc uprawnienia do profilu publicznego dla wszystkich (Principal_id = 0 to rola public w bazie MSDB).

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'Public',
@principal_id = 0,
@is_default = 1 ;

Poza uprawnieniami do profilu, zwykły użytkownik musi mieć jeszcze dostęp do bazy MSDB oraz przypisanie do roli DatabaseMailUserRole

USE [msdb]
GO
CREATE USER [UserName] FOR LOGIN [UserName]
GO
USE [msdb]
GO
ALTER ROLE [DatabaseMailUserRole] ADD MEMBER [UserName]
GO

I to już wszystko. Teraz możemy na wysłać maila testowego.

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Public',
@recipients = 'JakisAdresEmail@wp.pl',
@subject = 'SQL Server Databse Mail - Test',
@body = 'Dzieńdobry i dowidzenia ...';

Weryfikacja poprawności działania i typowe problemy

Do weryfikacji poprawności wysłania maili mamy dostępnych kilka widoków systemowych. Szczególnie użyteczne są dwa :

  • msdb.dbo.sysmail_allitems, pokazujący aktualny status i zawartość wiadomości w kolejce.
  • msdb.dbo.sysmail_event_log – tu znajdziemy szczegółowe informacje o błędach, odpowiedziach serwera SMTP.
USE msdb
go
 
SELECT sent_status, mailitem_id, profile_id, recipients,
	subject, body
FROM sysmail_allitems
ORDER BY mailitem_id desc
 
SELECT * FROM sysmail_event_log
ORDER BY log_id  desc

Database_Mail_04
W przypadku pojawienia się problemów z wysyłaniem wiadomości sprawdź przede wszystkim :

  1. Informację o błędach, odpowiedziach serwera w widoku sysmail_event_log.
  2. Wiem, że to banał ale literówki, nieprawidłowa konfiguracja konta to naprawdę najczęstsza przyczyna problemów (bywa że username to pełna nazwa adresu email ;)).
  3. Sprawdź czy usługa brokera jest aktywna.
  4. Sprawdź status kolejki – powinna być STARTED.

    EXECUTE msdb.dbo.sysmail_help_status_sp

    Jeśli nie jest, możesz uruchomić ją za pomocą

    EXECUTE msdb.dbo.sysmail_start_sp

  5. Ustawienia firewall lub co bardziej kłopotliwe – szczególnie w korporacjach, blokowanie portów pocztowych 587. Łatwo to zweryfikować, telnetując się na np. na port 578 serwera SMTP. Jeśli się nie powiedzie, to znaczy że ruch na dany port jest wycinany.
    Database_Mail_06
  6. Szczególne ustawienia serwerów pocztowych SMTP, np. GMAIL, w których podstawowa autentykacja jest traktowana jako niebezpieczna.

    W Gmail, wprowadzono standard Oauth 2.0 i jeśli chcemy korzystać z Basic Authentication musimy jawnie określić, aby z naszego konta mogły korzystać tzw. mniej bezpieczne aplikacje.
    Więcej na ten temat znajdziesz tutaj : https://support.google.com/accounts/answer/6010255?hl=en
    Akceptację aplikacji mniej bezpiecznych, możesz ustawić po zalogowaniu do GMail tutaj : https://www.google.com/settings/security/lesssecureapps

  7. Sprawdź kto jest ownerem bazy systemowej MSDB. Jeśli jakiś „zwykły” user – warto zmienić na SA :)

    select name, suser_sname(owner_sid) as Owner  
    from sys.databases
     
    USE msdb
    GO
     
    EXEC sp_changedbowner [sa]
    GO


Źródła MSDN :
http://technet.microsoft.com/en-us/library/ms175887(v=SQL.105).aspx

Powyższy artykuł dotyczy wersji SQL Server 2008 R2 – 2014

Wyszukiwanie ciągłych zakresów, dziur, problemy wysp w SQL

Weryfikacja ciągłości zakresu, identyfikacja dziur oraz scalanie przedziałów, przydaje się do rozwiązywania różnych zadań. W systemach transakcyjnych, szczególnie mocno obciążonych przez wielu użytkowników, mogą wystąpić sytuacje gdy funkcja IDENTITY nie zapewnia bezwzględnej ciągłości identyfikatorów. Dotyczy to również obiektów wprowadzonych w SQL Server 2012, znanych już od dawna w Oracle – sekwencji.

Artykuł ten, nie będzie jednak o bezpiecznym nadawaniu kolejnych, ciągłych identyfikatorów (polecam triggery INSTEAD OF INSERT). Zaprezentuję tu kilka wskazówek i praktycznych przykładów jak wykrywać nieciągłości, identyfikować dziury oraz scalać zakresy.

Niezwykle pomocne okazują się tutaj funkcje szeregujące bazujące na funkcji okna OVER() oraz wspólne wyrażenia tablicowe.


Wykrywanie nieciągłości w zbiorze

Na początek coś bardzo prostego. Trochę podstaw, bez których trudno jest rozwiązywać bardziej skomplikowane przykłady.

Załóżmy, że mamy zbiór elementów ponumerowanych w zakresie od 1-1000, inkrementowanych co 1. Każdy ciągły, jednostajnie rosnący zbiór wartości, powinien zwrócić ten sam wynik dla takiego wyrażenia:

Wartość_elementu  – ( Inkrementacja * Nr_elementu_w_szeregu ) = Const.


Wykorzystując funkcję ROW_NUMBER() i powyższą zasadę, sprawdźmy ciągłość dla poniższego zbioru:

Use tempdb
GO
 
set nocount on 
go
 
IF ( OBJECT_ID('dbo.zakres') is not null)  
drop table dbo.zakres;
 
create table dbo.zakres
( 
	Vlan int
);
 
DECLARE @zmienna int = 1
 
WHILE (@zmienna<=1000)
BEGIN
	insert into  dbo.zakres values (@zmienna)
	set @zmienna = @zmienna + 1
END;
 
-- kwerenda sprawdzająca
with CheckCont as (
	-- dla inkrementowanego o 1
	select distinct T1.VLAN  - (1*(ROW_NUMBER() OVER (ORDER BY  VLAN))) as Diff
	from dbo.zakres t1
)
Select CASE WHEN Count(Diff)> 1 then 'Discontinuous' else 'Continuous' end as Result
from CheckCont

Zakresy_ciagle_01

Jak widać bez niespodzianek – jest ok. Skasujemy teraz kilka losowych rekordów i ponowie zweryfikujemy ciągłość elementów, tą samą kwerendą sprawdzającą.

DELETE FROM dbo.zakres
WHERE VLAN IN (
SELECT TOP 3 VLAN from dbo.zakres ORDER BY NEWID() 
)

Zakresy_ciagle_02


Scalanie zakresów

Scenariusz, z którym ostatnio się spotkałem to scalanie zakresów VLAN w konfiguracji interfejsów urządzeń sieciowych (routerów). Informacje o VLANach są przechowywane w tabeli, w której każdy rekord, odpowiada pojedynczemu VLANowi, przypisanemu do danego portu, określonego urządzenia. To co trzeba wykonać, to agregacja (sklejenie zakresów) do skrótowej postaci przyjaznej dla użytkownika.

Wygenerujmy sobie prostą tabelę, z nieciągłą numeracją w kolumnie VLAN :

IF ( OBJECT_ID('dbo.zakres') is not null)  drop table dbo.zakres;
create table dbo.zakres
( 
	Vlan int
);
 
DECLARE @zmienna int = 1, @los int =0, @cnt int =1
 
WHILE (@cnt<=10)
BEGIN
 
	SET @los = case when   CONVERT(INT, (11) * RAND()) > 8  then 1 else 0 end
 
	insert into dbo.zakres values (@zmienna + @los)
	SELECT @zmienna = @zmienna +1+ @los, @cnt +=1
END;
 
select * from dbo.zakres

Zakresy_ciagle_03

Zadaniem jest wykrycie i scalenie zakresów rekordów o ciągłej numeracji. Stosując się do reguły ciągłości, którą pokazałem w poprzednim przykładzie, sprawdźmy rezultat tej formułki dla kolejnych wierszy. Widać, że wynik może posłużyć jako bezwzględny identyfikator określonego, ciągłego zakresu. W naszym przypadku mamy 4 zakresy, w tym dwa wieloelementowe.

Zakresy_ciagle_04

Kluczowym znów będzie numerowanie rekordów i późniejsze ich grupowanie względem wyliczonego w ten sposób identyfikatora zakresu. Dwa podejścia – stare, sprzed SQL Server 2005 z zapytaniem skorelowanym :

with PoNumerowane as
(
	select VLAN ,  
           (select count(*) from dbo.zakres t2 where t2.VLAN  <= t1.VLAN )  as IdRek
	from dbo.zakres t1
)
select VLAN - IdRek as ZakresID, MIN(VLAN) as VLAN_START, MAX(VLAN) as VLAN_END
from PoNumerowane
group by VLAN - IdRek

Oraz lepsze, zdecydowanie bardziej wydajne, rozwiązanie z użyciem funkcji ROW_NUMBER() :

with Ponumerowane as (
	select VLAN , ROW_NUMBER() OVER(order by VLAN)  as IdRek,
	 VLAN - ROW_NUMBER() OVER(order by VLAN) as IdZakres
	from dbo.zakres t1 
)
select IdZakres, MIN(VLAN) as VLAN_START, MAX(VLAN) as VLAN_END
from Ponumerowane
group by IdZakres

Zakresy_ciagle_05
Takie rozwiązanie można ubrać w funkcję użytkownika i prezentować scalone zakresy liczb w przyjaznej formie, np. dodatkowo agregując je do postaci skalarnej (FOR XML PATH).


Wyszukiwanie brakujących elementów w zbiorze

Na koniec sytuacja, gdy interesuje nas to, czego nie ma w tabeli. Mogą to być braki numerów zamówień, albo dziury w ciągłym zakresie adresacji urządzeń IP, nadające się do ponownego przydzielenia. Identyfikację brakujących elementów, na przykładzie zbioru inkrementowanego o 1, możemy wykonać na przykład tak :

with cte as (
 
	SELECT (select MIN(VLAN) from dbo.zakres) as VLAN, 
(select MAX(VLAN) from dbo.zakres) as MaxVal
	UNION ALL
 
	-- zakładamy inkrementację + 1 
	SELECT VLAN + 1 , MaxVal
	FROM CTE  c 
	where VLAN < MaxVal
)
select c.VLAN as MissingVlan from cte c left join dbo.zakres t on c.VLAN = t.VLAN
where t.VLAN is null
OPTION (MAXRECURSION 0)

Zakresy_ciagle_06

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.

Zapytania rozproszone i zewnętrzne źródła danych

W SQL Server mamy dostępnych kilka metod, dających nam dostęp do danych zewnętrznych.
W artykule tym, przedstawiam możliwości funkcji OPENQUERY, za pomocą której możemy tworzyć zapytania „Ad Hoc” (spontanicznie) do innych zasobów.

Zaprezentuję także metody wykorzystujące obiekty serwerów powiązanych (Linked Servers). Te z kolei stosujemy, aby utworzyć „stałe” powiązanie z innymi zasobami i w wygodny sposób, regularnie sięgać do zewnętrznych źródeł.


Zapytania rozproszone (Distributed Queries)

Komunikacja w środowiskach rozproszonych odbywa się zawsze w oparciu o interfejs pośredniczący. Standardem są sterowniki ODBC (Open DataBase Connectivity), za pomocą których możemy łączyć się z innymi bazami danych. Każdy producent serwera bazodanowego, udostępnia zazwyczaj w pakiecie klienckim wszystkie wymagane komponenty. Jeśli masz potrzebę łączenia się np. z bazą MySQL czy Oracle, powinieneś zainstalować właściwe dla danego dostawcy biblioteki.

Microsoft, od lat rozwijał równolegle własną koncepcje warstwy pośredniczącej – tzw. dostawców OLE DB (Object Linking and Embedding Database). Biblioteki te, rozszerzają możliwości ODBC i dotyczą połączeń nie tylko do serwerów bazodanowych ale także dostępu do zasobów plikowych (Excel, Access czy nawet zwykłych, płaskich plików tekstowych).

Jeśli chodzi o komunikację z SQL Server, w wersjach 2005-2012, to właśnie dostęp za pośrednictwem Native Client OLE DB był sugerowaną (szybszą, o większych możliwościach) metodą dostępu do danych. Sterowniki Native Client, zawierają w sobie bibliotekę OLE DB oraz ODBC. Dla wielu z pewnością dużym zaskoczeniem, był nagły zwrot w kierunku standardu ODBC w SQL Server 2014. Native Client 11 OLE DB z SQL Server 2012 – jest ostatnim w tej architekturze sterownikiem (więcej na ten temat, możesz przeczytać nas stronach Microsoft dot. kierunku rozwoju oraz oficjalnego powrotu do standardu ODBC)

Komunikacja, pomiędzy klientami, instancją SQL Server (do wersji 11) i środowiskami rozproszonymi odbywa się za pośrednictwem OLE DB.
SQL_SERVER_DISTRIBUTED_QUERIES


Funkcja OPENROWSET

Za pomocą funkcji OPENROWSET, możemy tworzyć zapytania rozproszone (Distributed Queries), odpytujące praktycznie dowolne zbiory zewnętrzne. Jedynym ograniczeniem jest dostępność na serwerze właściwych bibliotek OLE DB lub sterowników ODBC. Funkcja OPENROWSET umożliwia nie tylko odpytywanie zasobów, ale również pozwala na wykonywanie za jej pośrednictwem poleceń typu DML (Insert, Update, Delete). Ponieważ jest to funkcja tabelaryczna, stosować możemy ją analogicznie jak zwykłą tabelę (np. łącząc ją z innymi zbiorami).

Wymagania początkowe – konfiguracja serwera

Warunkiem koniecznym na wykorzystanie tej funkcji, jest włączenie na poziomie serwera, możliwości pisania zapytania rozproszonych (Ad Hoc Distributed Queries). To zadanie wykonamy za pomocą instrukcji :

exec sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
 
exec sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE WITH OVERRIDE;
GO

Od tego momentu, możemy tworzyć zapytania „Ad hoc”, korzystające z OPENROWSET. W dalszej części zaprezentuję typowe przykłady zastosowań.

OPENROWSET w praktyce

Odpytywanie źródeł zewnętrznych za pomocą tej funkcji, wymaga podania kilku parametrów. Nie będę omawiać tu wszystkich możliwych opcji, zwrócę uwagę tylko na najważniejsze i najczęściej wykorzystywane w praktyce.

Typowe użycie funkcji OPENROWSET – odpytywanie innego SQL Servera :

SELECT *
FROM OPENROWSET(
         -- Provider_name
	'SQLNCLI',  				
 
         -- Connection String
	'SERVER=ServerZdalny\Instancja; TRUSTED_CONNECTION=yes;',
 
         -- Query
         'SELECT * FROM Northwind.dbo.Customers'
	) AS tabela

Pierwszym parametrem jest określenie dostawcy OLE DB (provider_name) za pośrednictwem którego wykonywane będzie połączenie.

Regułą jest stosowanie dedykowanego dostawcy dla określonego źródła. Jeśli nie jest on dostępny, możesz skorzystać z uniwersalnej biblioteki MSDASQL i połączenia via ODBC.

Listę dostawców OLE DB których masz zainstalowane na swoim serwerze, odnajdziesz nawigując w drzewie hierarchii obiektów w Management Studio :

OLE_DB_Providers

lub za pośrednictwem specjalnej procedury składowanej :

EXEC xp_enum_oledb_providers

OLE_DB_Providers2

W zależności z jakiego oprogramowania korzystasz, ta lista może się różnić.

Warto tutaj wspomnieć, że można utworzyć połączenia ze zdalnymi serwerami na różne sposoby. Już choćby z tej listy widać, że do SQL Server, można się odwołać za pomocą przynajmniej trzech dostawców – SQLOLEDB (ten jest stosowany domyślnie), SQLNCLI oraz uniwersalnego MSDASQL, wykorzystującego pośrednio połączenia ODBC. Wybór dostawcy, powinien być podyktowany konkretnymi potrzebami programistycznymi (więcej na temat różnic znajdziesz np. tutaj ).

Drugim parametrem jest connection string, czyli charakterystyczny dla danego dostawcy zbiór opcji, które określają warunki połączenia. W przypadku SQL Server, będzie to na pewno nazwa sieciowa serwera / instancji, tryb uwierzytelniania, czy wybór konkretnej bazy danych.

Trzecim parametrem jest nasza zdalna kwerenda. Tutaj od razu bardzo ważna uwaga – pamiętaj o tym, ze cały ten zbiór, zwracany przez to zdalne zapytanie, będzie transportowany do serwera z którego je wywołujesz. Może to być przyczyną problemów m.in. sieciowych, trwać długo czyli również przekładać się bezpośrednio na czas trwania zapytania, blokad etc… Zapytania rozproszone, powinny zwracać zawsze minimalny, konieczny zbiór danych. Chyba również z tego powodu, domyślnie możliwość ich wykonywania jest wyłączona.

Zapytania do bazy ORACLE

Jak to bywa, możemy zrealizować tego typu zapytanie ad hoc na kilka sposób. Poniżej parę przykładów – dla każdego coś miłego. Pierwsze trzy zakładają istnienie wpisu TNS w pliku {ORA_HOME}\Network\Admin\tnsnames.ora. W ostatnim przykładzie, jawnie podaje wszystkich parametry komunikacyjne do serwera zdalnego.

-- z providerem OLEDB Oraclowym + wpis w {ORA_HOME}\Network\Admin\tnsnames.ora
SELECT * 
FROM OPENROWSET(
	'OraOLEDB.Oracle',
	'NaszOracleSrv';'username';'paswword', 
	'SELECT ''works fine'' as Kol1 FROM Dual' )
 
-- z providerem MSowym do Oracle 
Select * 
FROM OPENROWSET(
	'MSDAORA',
	'NaszOracleSrv';'username';'paswword', 
	'SELECT ''works fine'' as Kol1 FROM Dual' )
 
-- a tu inaczej - wszystko w Connection Stringu + połączenie via ODBC z wpisem w {ORA_HOME}\Network\Admin\tnsnames.ora
SELECT * 
FROM OPENROWSET(
	'MSDASQL', 
	'DRIVER={Microsoft ODBC for Oracle}; UID=username; PWD=password; SERVER=NaszOracleSrv;', 
	'SELECT ''works fine'' as Kol1 FROM Dual' 
	) as Tabela
 
-- a tu inaczej - wszystko w Connection Stringu + połączenie via ODBC
SELECT * 
FROM OPENROWSET(
	'MSDASQL', 
	'DRIVER={Microsoft ODBC for Oracle}; UID=username; PWD=password; SERVER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.1.1)(PORT=1521)))(CONNECT_DATA=(SID=NaszOracleSID)));', 
	'SELECT ''works fine'' as Kol1 FROM Dual' 
	) as Tabela

Zapytania do baz MySQL i innych z użyciem MSDASQL + ODBC

Jeśli nie mamy dostępnego dedykowanego dostawcy OLE DB, możemy łączyć się ze zdalnym serwerem za pomocą MSDASQL i połączenia wykorzystującego sterowniki ODBC. Listę dostępnych driverów, zainstalowanych na maszynie, sprawdzisz w panelu administracyjnym ODBC :

ODBC_Drivers

W przypadku odpytania bazy MySQL z poziomu SQL Server, potrzebne będą sterowniki ODBC (jak widać powyżej, mam takie zainstalowane). Sama kwerenda jest bardzo podobna do poprzedniej. Zastosowałem inny sterownik ODBC (MySQL) i dlatego connection string, którego struktura jest określana przez producenta, różni się w stosunku do poprzedniej, gdy odpytywałem bazę Oracle.

  SELECT  * 
  FROM OPENROWSET(
	'MSDASQL',
       'Driver={MySQL ODBC 5.2 ANSI Driver}; 
  Server=MyServerMySQL; Database=MojaBaza; USER=Username; PASSWORD=alamakota',
	'SELECT * from tabelka limit 10' )

Za pomocą OPENROWSET, możemy odpytywać także zawartości plików. Mogą to być zarówno pliki tekstowe, strukturyzowane (CSV), dokumenty Excel czy „bazy danych” Access. Dość wygodnie, za pomocą OPENQUERY przeczytamy również całe pliki XML. Pobieranie danych z Excela oraz plików tekstowych, opisuje szczegółowo w kolejnych rozdziałach tego kursu.

Poniżej kilka prostych, ale praktycznych przykładów :

-- Czytanie zwykłego pliku txt
SELECT F1 as FileContent 
FROM OPENROWSET(
	'Microsoft.ACE.OLEDB.12.0', 
    'Text;Database=D:\data\;HDR=NO', 
    'SELECT *  FROM test.txt')
 
-- załadowanie pliku XML (można go przypisać do zmiennej
SELECT CONVERT(xml, BulkColumn ) as XmlDocs
FROM 
OPENROWSET (BULK 'D:\DaneXML\NBP\a051z140314.xml',SINGLE_BLOB) as T1

OPENQUERY_1


Serwery powiązane – Linked Servers

Do tej pory, korzystaliśmy z tzw. zapytań Ad hoc – spontanicznych kwerend, do pobrania określonych danych z zewnętrznego źródła. W środowiskach produkcyjnych, istnieją serwery, które regularnie odpytujemy. Pisanie za każdym razem, długich connection stringów, jest niewygodne.

Za pomocą obiektów serwerów powiązanych, możemy zrealizować integrację danych z różnych środowisk w wygodniejszy sposób.

Na wstępie warto jeszcze raz podkreślić, że zapytania rozproszone, niezależnie od metody (OPENROWSET, Linked Server) powinny być stosowane z rozwagą, bo mogą być przyczyną problemów wydajnościowych (sieć, czasy blokad etc.) w środowiskach transakcyjnych.

Świadomi tego :), możemy zabrać się za tworzenie obiektów serwerów powiązanych. Jeśli jesteś zwykłym użytkownikiem bazy danych, potrzebujesz do tego uprawnień serwerowych ALTER ANY LINKED SERVER.

Serwery powiązane, możesz tworzyć w T-SQL (służy do tego procedura sp_addlinkedserver) lub za pomocą wizarda w Management Studio.

Linked_server_01

Całość konfiguracji, ogranicza się zazwyczaj do określenia dostawcy OLE DB, nazw serwera, ewentualnie połączenia ODBC oraz ustawień związanych z bezpieczeństwem. W przypadku SQL Server, wystarczy podać nazwę sieciową instancji – zostanie utworzony serwer powiązany, domyślnie z uwierzytelnianiem zintegrowanym, bazujący na natywnym kliencie SQLNCLI OLE DB.

Pisanie zapytań z wykorzystaniem Linked Servers

Odwoływanie się w kwerendach rozproszonych do obiektów serwerów powiązanych, jest możliwe na dwa sposoby.
Za pomocą funkcji tabelarycznej OPENQUERY(), możemy wysyłać zapytanie do dowolnego obiektu Linked Server.

SELECT * 
FROM OPENQUERY(SerwerPowiazany,'SELECT * FROM TABELA')

W tej sytuacji, zostanie wykonane zapytanie na serwerze zdalnym w dokładnie takiej postaci, jak parameter query funkcji OPENQUERY. Czyli zostanie pobrana CAŁA zawartość odpytywanej tabeli i przesłana przez sieć do klienta. Porównaj takie dwa zapytania z filtracją rekordów :

-- źle
SELECT * 
FROM OPENQUERY(SerwerPowiazany,'SELECT * FROM TABELA')
WHERE KolID = 12345
 
-- dobrze :)
SELECT * 
FROM OPENQUERY(SerwerPowiazany,'SELECT * FROM TABELA WHERE KolID = 12345 ')

Zwrócą to samo, ale o ile lżejsze jest zapytanie drugie, z filtracją po stronie zdalnego serwera….
Funkcja OPENQUERY, pozwala również na wysyłanie poleceń typu DML.

Drugim sposobem, jest odwoływanie się w poleceniach SQL do obiektów (tabel, widoków czy procedur), po w pełni kwalifikowanej, czteroczłonowej nazwie. W ten sposób, możemy wywoływać także procedury składowane na zdalnym serwerze.

           <SERVER>.<BAZA_DANYCH>.<SCHEMAT>.<OBIEKT>

Zarówno pierwszy jak i drugi sposób, pozwala na pobieranie danych z zewnętrznych źródeł podobnie jak ze zwykłych tabel. Można łączyć je w zapytaniach, odwołując się jednocześnie do różnych serwerów (UWAGA na wydajność !!!).

SELECT c.*, o.OrderID  
FROM Server1.Northwind.dbo.Customers c 
		inner join Server2.Northwind.dbo.Orders o on c.CustomerID = o.CustomerID

Metadane związane z Linked Servers

Z obiektami Linked Servers, związanych jest kilka przydatnych widoków i procedur składowanych, które warto poznać. Listę dostępnych obiektów Linked Servers, loginów z nimi związanych, możesz obejrzeć poprzez widoki systemowe w bazie msdb (sys.servers, sys.linked_logins oraz sys.remote_logins).

Mamy także dostępnych kilka specjalnych procedur składowanych. Z bardziej praktycznych są sp_catalogs oraz sp_tables_ex, które pozwalają na podejrzenie wszystkich baz danych / tabel, dostępnych po przez dany obiekt (o ile dostawca OLE DB/ODB oferuje takie funkcjonalności). Poniżej przykład wywołania sp_catalogs do zdalnego SQL Servera o nazwie RFN.

  -- bazy danych
  sp_catalogs 'RFN'

Linked_server_02

Za pośrednictwem serwerów powiązanych, podobnie jak funkcji OPENROWSET, możemy odnosić się również do plików. Jest wiele dróg na import danych z plików. Jedno z nich to rozwiązanie oparte o Linked Servers. Poniżej przykład utworzenia obiektu „serwera”, będącego katalogiem z plikami. Do tego celu wykorzystam dostawcę Jet.OLEDB.4.0.

EXEC sp_addlinkedserver  PlikiWymiany, 
	'Jet 4.0', 
         'Microsoft.Jet.OLEDB.4.0',
         'D:\Dane\',
         NULL,
         'Text';

W katalogu D:\Dane\ – są umieszczone dwa pliki tekstowe :

LinkedServer_TXT_FILE_01

Na koniec zastosowanie procedury składowanej sp_tables_ex oraz odczytanie zawartości pierwszego z plików.

EXEC sp_tables_ex PlikiWymiany;
GO
 
SELECT * 
FROM PlikiWymiany...[test#txt];

LinkedServer_TXT_FILE_02


Podsumowanie

Opisane powyżej metody pracy w środowisku rozproszonym są z pewnością bardzo praktyczne i często stosowane. Korzystając z nich, pamiętaj o aspektach wydajnościowych. Pobieranie danych (czasem całych tabel) poprzez sieć ze zdalnych serwerów, z pewnością nie jest najlepszym pomysłem.

Podzapytania w SQL

Język SQL, pomimo ściśle ustalonego szyku bloków logicznych (SELECT, FROM, WHERE…. ) jest dość elastyczny. W rozdziałach dotyczących elementów składniowych zapytań, do znudzenia podkreślałem fundament na którym zbudowane są relacyjne bazy danych i SQL. Jest to matematyczna teoria zbiorów. Podzapytania idealnie obrazują te zasady w praktyce i są często stosowanymi konstrukcjami.

Podzapytania, jak sama nazwa wskazuje, są częścią podrzędną innego zapytania. Możemy podzielić je na dwie kategorie ze względu na powiązanie z kwerendą nadrzędną :

  • niezależne – funkcjonować mogą w całkowicie oderwanym kontekście. Można je uruchomić jako osobne kwerendy – o nich właśnie jest ten artykuł.
  • skorelowane – są bezpośrednio powiązane z zapytaniem nadrzędnym. Opisuję je w kolejnym rozdziale tego kursu.

Podzapytania niezależne

Przypomnijmy podstawowe zasady, które intuicyjnie nakierowują na właściwie tory jeśli chodzi o temat podzapytań. Jeśli przerabiasz ten kurs od początku, reguły te powinny być dla Ciebie już oczywiste.

Każde zapytanie SQL to operacja na zbiorze lub zbiorach elementów. Tabele i widoki do których zazwyczaj odwołujemy się w kwerendach to tylko przykłady zbiorów. Wynikiem działania dowolnej kwerendy jest także zbiór.

Rozważmy na przykład zapytanie do zbioru elementów (tabeli) dbo.Customers. Wybierzemy tylko takie elementy (rekordy), dla których wartość atrybutu (kolumny) City, jest równa ciągowi znaków ‚London’.

USE Northwind
GO
 
SELECT CompanyName, City, Country 
FROM dbo.Customers
WHERE City = 'London'

Podzapytania_SQL_01

To co chciałem na tym banalnym przykładzie podkreślić, to fakt że zapytanie, odwołuje się do zbioru (lub zbiorów) i zwraca jeden zbiór. Skoro więc zwraca zbiór, to możemy ten zbiór także odpytywać jak zwykłą tabelę. Będzie to pierwszy przykład z wykorzystaniem typowego podzapytania niezależnego we FROM :

SELECT *
FROM 
(
	-- wstępna, selekcja elementów i atrybutów zbioru dbo.Customers
	-- może tu być dowolna skomplikowana kwerenda.
	SELECT CompanyName, City, Country 	FROM dbo.Customers	where City = 'London' 
) AS MojePodzapytanie
WHERE CompanyName like '[A-C]%'

Podzapytania_SQL_02

Zauważ, że w każdej chwili możesz to podzapytanie uruchomić zaznaczając tylko jego zakres. Jest ono niezależne w stosunku do zapytania zewnętrznego. Wykonane zostanie raz, w trakcie całego procesu logicznego przetwarzania tej kwerendy.

Każdy zbiór do którego odnosimy się we FROM musi być nazwany i w pełni określony. Stąd konieczność stosowania aliasów oraz unikalnych nazw kolumn w ramach podzapytań.

Miejsca w których możemy stosować podzapytania

Podzapytania możemy stosować praktycznie w dowolnym bloku logicznym kwerendy. Jedynym ograniczeniem jest rodzaj zwracanego zbioru. Musi pasować do miejsca w którym chcemy go użyć. Na przykład we FROM, może to być dowolny zbiór (jednoelementowy, wieloelementowy itd), z kolei w SELECT musi to być wartość skalarna czyli zbiór jednoelementowy opisany jednym atrybutem.

W dalszych przykładach, będę wykorzystywał bazę testową AdventureWorks2008, aby zaprezentować typowe zastosowania podzapytań w różnych miejscach kwerendy.

Pobierzemy informacje o zleceniach z czerwca 2014, z rejonu Wielkiej Brytanii (CountryRegionCode = ‚GB’), dla których wartość (TotalDue), przekroczyła średnią liczoną dla wszystkich zleceń.

Zacznijmy od kwerendy, która zwróci nam informacje o średniej wartości dla wszystkich zamówień.

USE AdventureWorks2008
GO
 
SELECT AVG(TotalDue) as AVG_TotalDue
FROM [Sales].[SalesOrderHeader]

Podzapytania_SQL_03

Zwracany zbiór jest szczególny. Jednoelementowy, opisany jednym atrybutem (jedną kolumną) – czyli to zwykła wartość skalarna.

Taki zbiór możemy umieścić w każdym miejscu kwerendy – jako podzapytanie. Najczęściej będziemy go stosować w warunkach WHERE lub w SELECT. Może być też stosowany w innych miejscach gdzie tworzymy wyrażenia, filtracji grup w HAVING czy warunki złączeń w ON.

Wykorzystajmy teraz te informacje, aby odfiltrować rekordy w WHERE i dodatkowo wyświetlić ją w SELECT jako wartość dodatkowej kolumny.

SELECT SalesOrderID, OrderDate, TotalDue, st.Name AS TerritoryName,   
	(  
               -- podzapytanie w SELECT – średnia dla wszystkich zleceń
	     SELECT AVG(TotalDue)  	     FROM [Sales].[SalesOrderHeader]  	) AS AVG_TotalDue
FROM [Sales].[SalesOrderHeader] soh 
     inner join [Sales].[SalesTerritory] st ON soh.TerritoryID = st.TerritoryID
WHERE st.CountryRegionCode = 'GB' and OrderDate between '2004-06-01' and '2004-06-30'  
      and  TotalDue >= 
          (
             -- podzapytanie w filtracji w WHERE
             SELECT AVG(TotalDue) AS  AVG_TotalDue             FROM [Sales].[SalesOrderHeader]            )

Podzapytania_SQL_04

Warto podkreślić, że jeśli podzapytanie nie zwróciłoby tu żadnego rekordu, to wynikiem w kwerendzie zewnętrznej, będzie jeden element opisany NULLami. Trzeba mieć to na uwadze bo jeśli zdarzyłaby się taka sytuacja w podzapytaniu w WHERE – to otrzymamy pusty zbiór. Żaden z rekordów nie spełni przecież warunku TotalDue >= NULL. Każde porównanie z NULL to wartość nieznana, więc każdy rekord będzie odfiltrowany.

Usystematyzujmy dotychczasowe informacje. W SELECT może znaleźć się tylko takie podzapytanie, które zwraca wartość skalarną. We FROM możemy wykorzystać każde podzapytanie, definiujące jakikolwiek zbiór. Tworzenie warunków połączeń w ON , wyrażeń w WHERE oraz filtracji grup w HAVING, dopuszcza różne zbiorów w zależności od zastosowanych operatorów. Standardowo będą to operatory porównujące wartości skalarne ( =, <, >, <>, itd.) – wtedy tylko takie podzapytania, które zwracają skalar.

Są też specjalne operatory działające na zbiorach np. IN, ANY (SOME) , ALL. Operatory te, działają na wektorze wartości. Wektor to zbiór elementów opisanych jednym atrybutem (czyli wartości skalarnych). Zatem w tych przypadkach, podzapytania mogą zwracać wektor.

Pozostał jeszcze jeden specjalny operator – EXISTS / NOT EXISTS, który możemy stosować np. w WHERE. Za jego pomocą sprawdzamy tylko czy zbiór podzapytania jest pusty czy nie. W tym przypadku nie ma znaczenia jakiego typu są to elementy. Jeśli są, to zwracana jest wartość TRUE, jeśli nie – FALSE.


Podzapytania z operatorami IN, ANY (SOME), ALL

Weźmy za przykład kwerendę, która da nam informacje o wszystkich zleceniach, dla trzech najlepszych (pod względem generowania obrotów firmy) Klientów.

Najpierw skupmy się na podzapytaniu, które powinno zwrócić nam wektor 3-elementowy z identyfikatorami najlepszych Klientów. Trzech najdroższych nam Klientów otrzymamy za pomocą takiego zapytania :

SELECT TOP 3 CustomerID , SUM(TotalDue) as TotalSales
FROM  [Sales].[SalesOrderHeader] soh 
GROUP BY CustomerID
ORDER BY TotalSales DESC

Podzapytania_SQL_05

To jeszcze nie jest wektor, ale już coś. Jeśli spróbujemy teraz zbudować kwerendę w oparciu o takie podzapytanie, filtrując w WHERE z wykorzystaniem operatora IN :

SELECT SalesOrderID, OrderDate, TotalDue, CustomerID from [Sales].[SalesOrderHeader] soh 
WHERE  CustomerID in (
 
	SELECT TOP 3 CustomerID , SUM(TotalDue) as TotalSales	FROM  [Sales].[SalesOrderHeader] soh 	GROUP BY CustomerID	ORDER BY TotalSales DESC)

Otrzymamy komunikat o błędzie, ponieważ podzapytanie generuje niepoprawny zbiór (nie jest to wektor – posiada dwie kolumny).

Msg 116, Level 16, State 1, Line 9
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

No to zróbmy z niego wektor – podzapytanie z podzapytania :

SELECT SalesOrderID, OrderDate, CustomerID 
FROM [Sales].[SalesOrderHeader] soh 
WHERE CustomerID IN (
         -- podzapytanie z podzapytania w WHERE
	SELECT CustomerID	FROM (		SELECT TOP 3 CustomerID , SUM(TotalDue) as TotalSales		FROM  [Sales].[SalesOrderHeader] soh 		GROUP BY CustomerID		ORDER BY TotalSales DESC 	) AS WektorIdentyfikatorowKlientow)

W powyższym przykładzie, widzimy inną ważną właściwość podzapytań – możliwość zagnieżdżania ich w sobie. Możemy tworzyć podzapytania z podzapytań do 32 poziomów.
Oczywiście można by było powyższą kwerendę zapisać inaczej, z zastosowaniem podzapytania we FROM.

SELECT SalesOrderID, OrderDate, soh.CustomerID 
FROM [Sales].[SalesOrderHeader] soh INNER JOIN (
          -- podzapytanie we FROM
	SELECT TOP 3 CustomerID , SUM(TotalDue) as TotalSales	FROM  [Sales].[SalesOrderHeader] soh 	GROUP BY CustomerID	ORDER BY TotalSales DESC  
) a on soh.CustomerID = a.CustomerID

Wynik i nawet plan wykonania w tej sytuacji będzie identyczny. Często filtracja w jak najwcześniejszym kroku procesu przetwarzania kwerendy przynosi lepsze rezultaty, choć nie zawsze.

Rozważmy bardziej skomplikowany przykład. Chcemy wyświetlić trzy najdroższe zamówienia dla trzech naszych najlepszych Klientów. Zapytanie to zapiszemy z zastosowaniem funkcji szeregującej RANK oraz funkcji okna OVER.

SELECT * FROM
 (
 
	SELECT SalesOrderID, TotalDue, 
              RANK() OVER(Partition by soh.CustomerID order by TotalDue desc) as Majority, 
              soh.CustomerID
	FROM [Sales].[SalesOrderHeader] soh inner join 
	( 
			SELECT TOP 3 CustomerID , SUM(TotalDue) as TotalSales
			FROM  [Sales].[SalesOrderHeader] soh 
			GROUP BY CustomerID
			ORDER BY TotalSales DESC
	) b on soh.CustomerID = b.CustomerID
) a 
WHERE Majority  <= 3

Podzapytania_SQL_06

Inny sposób na osiągnięcie tego samego celu, z filtracją Klientów w WHERE.

SELECT * 
FROM
 (
	-- wychwycenie tylko najdroższych zamówień per Klient
	SELECT SalesOrderID, TotalDue, 
               RANK() OVER(Partition by CustomerID order by TotalDue desc) as Majority,
               CustomerID
	FROM [Sales].[SalesOrderHeader] soh 
) a 
WHERE Majority <= 3 AND CustomerID IN (
 
	-- zrobienie wektora
	SELECT CustomerID
	FROM (
		-- wychwycenie 3 najdroższych nam Klientów
		SELECT TOP 3 CustomerID , SUM(TotalDue) as TotalSales
		FROM  [Sales].[SalesOrderHeader] soh 
		GROUP BY CustomerID
		order by TotalSales desc 
	) a 
)

To zapytanie generuje jednak bardziej kosztowny plan wykonania (w porównaniu do poprzedniego 31% : 69%).

Podzapytania_SQL_07


W SQL jest wiele sposobów na osiągnięcie tego samego rezultatu. Zagadnienia związane z wydajnością, poruszam w ostatnim rozdziale tego kursu.