SELECT Explanation, Example FROM Pro.Knowledge
FacebookRSS

Przetwarzanie zapytań SQL do wielu tabel

W rozdziale tym znajdziesz opis procesu przetwarzania zapytań SQL, skoncentrowany na klauzuli FROM. Jest to kontynuacja zagadnień, poruszonych w artykule – logiczne przetwarzanie zapytań. Temat ten podzieliłem na dwie części, ze względu na zakres i konieczność omówienia wcześniej zasad łączenia tabel w SQL. Polecam uwadze te dwa artykuły, które prezentują fundamentalne reguły pisania zapytań SQL.

W tej części, znajdziesz informacje o sposobie realizacji zapytań do wielu tabel. Przedstawię wpływ kolejności JOINów na wydajność oraz typowe błędy logiczne.


Zapytania SQL do wielu tabel

Przypomnijmy podstawowe zasady kolejności przetwarzania zapytań SQL. Pierwszym krokiem jest określenie źródeł danych, czyli wykonywanie poleceń umieszczonych we FROM.

W przypadku kwerend odwołujących się do jednego zbioru sprawa jest bardzo prosta. Wynikiem przetwarzania kroku FROM, jest wtedy tabela wirtualna VT1. Zawiera ona wszystkie rekordy ze zbioru źródłowego. Jest tożsama z nią, posiada te same kolumny i rekordy. Zbiór VT1 jest także źródłem kolejnego kroku – przetwarzania WHERE. Jak widać w zapytaniach do jednej tabeli nie ma szczególnej filozofii.

W rzeczywistych środowiskach bazodanowych, kwerendy dotyczą zazwyczaj wielu tabel.

Dla przykładu rozważmy scenariusz zapytania SQL w bazie testowej Northwind. Interesują nas informacje o Klientach z określonego miasta – Madrytu. Chcemy znać detale ich zamówień, złożonych we wrześniu 1996 – nazwy i ilości produktów, które kupili.

Struktura tej bazy danych jest znormalizowana. Informacje które nas interesują, wymagają połączenia 4 tabel.
Zapytania_SQL_do_wielu_tabel_2

Przykładowa kwerenda, realizująca to zadanie, może wyglądać tak :

SELECT  c.CompanyName, o.OrderID, od.Quantity, p.ProductName
FROM   dbo.Customers c  
       LEFT OUTER JOIN dbo.Orders o on c.CustomerID = o.CustomerID 
       INNER JOIN dbo.[Order Details] od on od.OrderID = o.OrderID
       INNER JOIN dbo.Products p on od.ProductID = p.ProductID
WHERE c.City = 'Madrid' AND o.OrderDate BETWEEN '1996-09-01' AND '1996-09-30'

Żeby było ciekawiej (trochę zaczepnie), zastosowałem jako pierwsze połączenie – LEFT OUTER JOIN. Chcę tym samym przy okazji analizy złączeń, pokazać jeden z częstszych błędów logicznych.

W wyniku okazuje się, że jest tylko jeden Klient, który w dodatku złożył tylko jedno zamówienie w podanym przedziale dat.
Zapytania_SQL_do_wielu_tabel_3

Zanim przystąpimy do szczegółowej analizy naszego zapytania, możemy podzielić klauzulę FROM na etapy. Bierzemy pod uwagę ilość występujących tu złączeń i związanych z nimi operacji. Mamy trzy złączenia – będą więc trzy etapy.

Zapiszmy FROM bardziej obrazowo :

FROM 
	-- ETAP 1 – łączenie dwóch pierwszych tabel
          dbo.Customers c 	
             LEFT OUTER JOIN  dbo.Orders o      ON c.CustomerID = o.CustomerID 
 
	-- ETAP 2 – do wyniku ETAPu 1 łączymy tabelę dbo.[Order details]
             INNER JOIN dbo.[Order Details] od  ON od.OrderID = o.OrderID     
 
	-- ETAP 3 – do wyniku ETAPu 2 łączymy tabelę dbo.Products
             INNER JOIN dbo.Products p          ON od.ProductID = p.ProductID

Zasady połączeń, obowiązujące w zapytaniach do wielu tabel SQL, są identyczne jak te opisane dla przykładu łączenia dwóch tabel.

Zaczynamy zawsze od łączenia dwóch pierwszych, stojących zaraz po FROM. Możemy także stosować nawiasy (o tym w następnym przykładzie). Mają one priorytet – czyli łączona jest tabela z wynikiem operacji w nawiasach.

Przy łączeniu trzech i więcej tabel, każda kolejna jest dołączana do wyniku poprzedniego kroku. Algorytm działa iteracyjnie od lewej do prawej, aż do przetworzenia ostatniego zbioru. Prześledźmy zatem dokładnie kolejne etapy przetwarzania FROM naszej kwerendy.


ETAP 1

Łączenie zewnętrzne (LEFT OUTER JOIN) pierwszych dwóch tabel :

SELECT  c.CompanyName, o.OrderID 
FROM   dbo.Customers c  
		LEFT OUTER JOIN dbo.Orders o ON c.CustomerID = o.CustomerID

Krok ten, zostanie przetworzony zgodnie z typowymi regułami logicznego przetwarzania złączeń :

  • Pobierz wszystkie dane z tabeli dbo.Customers
  • Pobierz wszystkie dane z tabeli dbo.Orders
  • Wykonaj złączenie zewnętrzne
    • Najpierw iloczyn kartezjański tych dwóch tabel
    • Następnie filtracja rekordów zgodna z wyrażeniami określonymi w ON
    • Dodanie „odfiltrowanych” rekordów z tabeli stojącej po LEWEJ stronie operatora JOIN (z tabeli dbo.Customers). Wartości atrybutów tabeli dbo.Orders dla tych „niedopasowanych elementów” z kroku poprzedniego uzupełnij NULLami.

Zapytania_SQL_do_wielu_tabel_4

W wyniku otrzymamy wszystkich Klientów (całą zawartość tabeli dbo.Customers) wraz z informacjami o ich zleceniach. Zwróć uwagę na pierwsze dwa rekordy. Są to elementy „dorzucone” w krokach charakterystycznych dla połączeń zewnętrznych – klienci którzy nie złożyli żadnych zleceń.

CompanyName                              OrderID
---------------------------------------- -----------
FISSA Fabrica Inter. Salchichas S.A.     NULL
Paris spécialités                        NULL
Vins et alcools Chevalier                10248
Toms Spezialitäten                       10249
...
Richter Supermarkt                       11075
Bon app'                                 11076
Rattlesnake Canyon Grocery               11077

(832 row(s) affected)

ETAP 2

Zbiór wynikowy poprzedniego etapu (VT1.1), będzie tabelą stojącą po LEWEJ stronie kolejnego złączenia. Tym razem będzie to złączenie wewnętrzne. VT1.1 umieściłem dla lepszego zobrazowania w nawiasie.

SELECT  c.CompanyName, o.OrderID , od.Quantity 
FROM   -- w nawiasie wynik ETAPu 1
       (
           dbo.Customers c  
	 LEFT OUTER JOIN dbo.Orders o on c.CustomerID = o.CustomerID 
       )
           INNER JOIN dbo.[Order Details] od on od.OrderID = o.OrderID

Wykonywane są więc kroki :

  • Pobierz wszystkie dane ze zbioru będącego wynikiem pierwszego złączenia – VT1.1
  • Pobierz wszystkie dane z tabeli dbo.[Order Details].
  • Wykonaj złączenie wewnętrzne tych zbiorów
    • iloczyn kartezjański
    • filtrowanie rekordów

Zapytania_SQL_do_wielu_tabel_5

CompanyName                              OrderID     Quantity
---------------------------------------- ----------- --------
Vins et alcools Chevalier                10248       12
Vins et alcools Chevalier                10248       10
Vins et alcools Chevalier                10248       5
Toms Spezialitäten                       10249       9
...
Rattlesnake Canyon Grocery               11077       2
Rattlesnake Canyon Grocery               11077       4
Rattlesnake Canyon Grocery               11077       2

(2155 row(s) affected)

Zwróć uwagę co się stało z Klientami, którzy nie złożyli żadnych zleceń. W poprzednim kroku, dzięki LEFT JOIN, byli oni uwzględnieni (dorzuceni do wyniku). Teraz, przy kolejnym złączeniu – elementy te zostały odfiltrowane.

W naszym scenariuszu nie miało to znaczenia na wynik końcowy. W końcu chcemy uzyskać informacje tylko o Klientach, którzy złożyli zamówienia. Tak naprawdę pierwszym złączeniem, powinien być INNER JOIN.

Zdarza się, że początkujący użytkownicy SQL, oczekują w takim przypadku, że w wyniku będą również mieli Klientów bez zleceń. Jest to dość często popełniany błąd logiczny na który chciałem zwrócić Twoją uwagę. Wynika on z niewiedzy, w jakiej kolejności wykonywane są złączenia. Być może także z nadinterpretacji zakresu działania LEFT OUTER JOIN, który przecież dotyczy tylko pierwszego złączenia.

Wróćmy do dalszej analizy naszego przykładu.


ETAP 3

Algorytm powtórzony zostanie jeszcze raz, dołączając finalnie do powyższego wyniku ostatnią tabelę – dbo.Products.

SELECT  c.CompanyName, o.OrderID , od.Quantity, p.ProductName
FROM   -- w nawiasie wynik ETAPu 2
       (
          dbo.Customers c  
          LEFT OUTER JOIN dbo.Orders o on c.CustomerID = o.CustomerID 
          INNER JOIN dbo.[Order Details] od on od.OrderID = o.OrderID 
       )
          INNER JOIN dbo.Products p on od.ProductID = p.ProductID

Wykonywane są znów standardowe kroki, jak przy każdym złączeniu dwóch zbiorów :

  • Pobierz wszystkie dane ze zbioru będącego wynikiem etapu 2 (umieszczone w nawiasie) – VT1.2.
  • Pobierz wszystkie dane z tabeli dbo.Products.
  • Wykonaj złączenie wewnętrzne tych zbiorów
    • Iloczn kartezjański
    • Filtrowanie rekordów.

Zapytania_SQL_do_wielu_tabel_6

CompanyName                              OrderID     Quantity ProductName
---------------------------------------- ----------- -------- ------------------------------
Vins et alcools Chevalier                10248       12       Queso Cabrales
Vins et alcools Chevalier                10248       10       Singaporean Hokkien Fried Mee
Vins et alcools Chevalier                10248       5        Mozzarella di Giovanni
Toms Spezialitäten                       10249       9        Tofu
...
Rattlesnake Canyon Grocery               11077       2        Röd Kaviar
Rattlesnake Canyon Grocery               11077       4        Rhönbräu Klosterbier
Rattlesnake Canyon Grocery               11077       2        Original Frankfurter grüne Soße

(2155 row(s) affected)

W tym momencie kończy się przetwarzanie klauzuli FROM. Wszystkie złączenia zostały wykonane. Zbiór wynikowy oznaczyłem jako VT2, aby odróżnić go od prostego przypadku zapytania do jednej tabeli (VT1). Zostaje on przekazany do kolejnego kroku – filtrowania rekordów w WHERE. Proces przetwarzania postępuje dalej zgodnie z opisanym tutaj logicznym porządkiem.


Stosowanie nawiasów w złączeniach tabel i filtracja we FROM

Rozważmy teraz scenariusz, ponownie w bazie Northwind, w którym chcemy uzyskać wiedzę o WSZYSTKICH Klientach z Madrytu, wraz z informacją o produktach, które zamówili w grudniu 1996. Jeśli nie złożyli żadnego zamówienia w tym okresie, to również chcemy ich zobaczyć.

Zapytanie realizujące to zadanie, powinno zwrócić następujący zbiór :

Zapytania_SQL_do_wielu_tabel_7

Jest to szczególny przypadek, w którym filtrowanie rekordów ze względu na datę zamówienia, powinno odbywać się we FROM zamiast w WHERE. Zobaczysz tu także praktyczne zastosowanie nawiasów we FROM, dzięki którym możemy sterować logiką połączeń. Nie będę stosował tu podzapytań, tylko czyste, proste złączenia.

Aby uzyskać informacje o wszystkich Klientach z Madrytu z ich zamówieniami (na razie bez filtrowania daty), można zapisać zapytanie w ten sposób :

SELECT  c.CompanyName, o.OrderID , od.Quantity, p.ProductName
FROM    dbo.Customers c  
        LEFT OUTER JOIN 
        ( 
              dbo.Orders o INNER JOIN dbo.[Order Details] od ON od.OrderID = o.OrderID
                           INNER JOIN dbo.Products p ON od.ProductID = p.ProductID 
        ) ON c.CustomerID = o.CustomerID 
WHERE c.City = 'Madrid'

Uzyskamy teraz faktyczny LEFT OUTER JOIN tabeli dbo.Customers wraz ze wszystkimi szczegółami zamówień.

Pierwsze złączenie będzie połączeniem tabeli dbo.Customers z wynikiem operacji ujętych w nawias. Wykonane zostaną najpierw wszystkie złączenia wewnątrz nawiasu. Najpierw tabeli dbo.Orders z dbo.[Order Details]. Następnie do wyniku dołączona zostanie tabela dbo.Products.

Dopiero na samym końcu, zostanie wykonane połączenie zewnętrzne, lewostronne, tabeli dbo.Customers z efektem działań w nawiasie (wszystkie szczegóły zamówień).

Alternatywnie, moglibyśmy połączenia zapisać bez nawiasów, z wykorzystaniem RIGHT OUTER JOIN :

SELECT  c.CompanyName, o.OrderID , od.Quantity, p.ProductName
FROM   dbo.Orders o 	
          INNER JOIN dbo.[Order Details] od  ON od.OrderID = o.OrderID
          INNER JOIN dbo.Products p          ON od.ProductID = p.ProductID 
          RIGHT OUTER JOIN dbo.Customers c   ON c.CustomerID = o.CustomerID 
WHERE c.City = 'Madrid'

Wynik będzie identyczny. Najpierw zostaną wykonane kolejno wszystkie INNER JOINy. Na samym końcu dołączona zostanie tabela dbo.Cutomers, dorzucając w ostatnim kroku wszystkich Klientów bez zleceń (RIGHT OUTER JOIN).

Teraz zastanów się, gdzie powinniśmy wykonywać filtrację rekordów jeśli chodzi o daty zamówień. Co uzyskamy stosując filtrację w WHERE ?

SELECT  c.CompanyName, o.OrderID, od.Quantity, p.ProductName
FROM   dbo.Customers c  
       LEFT OUTER JOIN  
       ( dbo.Orders o INNER JOIN dbo.[Order Details] od ON od.OrderID = o.OrderID
                      INNER JOIN dbo.Products p ON od.ProductID = p.ProductID 
        ) ON c.CustomerID = o.CustomerID 
WHERE c.City = 'Madrid' 
    and ( o.OrderDate BETWEEN '1996-09-01' AND '1996-09-30' OR o.OrderDate is null)

Zapytania_SQL_do_wielu_tabel_8

Filtracja WHERE jest bezapelacyjna. Raz usunięte rekordy, nie mają prawa się więcej pojawić w zbiorze wynikowym. Nie ma tu mechanizmu „dorzucania” elementów takiego jak w przypadku połączeń zewnętrznych. Dlatego powyższy zapis odsiał nam niestety wszystkich Klientów z Madrytu, którzy złożyli zamówienia, ale w innych datach.

Przypomnę, że chcieliśmy ich także zobaczyć w wyniku końcowym. Straciliśmy więc tutaj wiedzę o firmie „Bólido Comidas preparadas”.

Rozwiązaniem tego problemu jest przeniesienie filtracji ze względu na datę do klauzuli FROM, tak aby dorzucić ewentualnych Klientów z zamówieniami złożonymi poza interesującym nas zakresem dat. Właściwie zapisana kwerenda będzie wyglądała tak :

SELECT  c.CompanyName, o.OrderID , od.Quantity, p.ProductName
FROM   dbo.Customers c  
       LEFT OUTER JOIN 
       ( dbo.Orders o  INNER JOIN dbo.[Order Details] od on od.OrderID = o.OrderID
                       INNER JOIN dbo.Products p on od.ProductID = p.ProductID 
       ) ON c.CustomerID = o.CustomerID 
            and o.OrderDate BETWEEN '1996-09-01' AND '1996-09-30' 
WHERE c.City = 'Madrid'

Dzięki temu zabiegowi, uzyskamy wiedzę o WSZYSTKICH Klientach z Madrytu. Dodatkowo o zamówieniach z określonego przedziału czasowego.


Logiczna a fizyczna kolejność łączenia zbiorów

Fizyczna realizacja połączeń zbiorów przez silnik bazodanowy (plan wykonania) może się różnić od przyjętych w teorii zasad (od lewej do prawej). Jednak wynik zawsze musi być identyczny z rachunkiem teoretycznym.

Zauważ, że czasem warto zajrzeć ciut dalej, np. do filtrowania WHERE aby już na etapie FROM, nie analizować wszystkich rekordów, które i tak zostaną później odfiltrowane. Ponadto fizyczna kolejność wykonywania połączeń, filtracji – też ma znaczenie na wydajność.

Wybór najkrótszej i najbardziej efektywnej metody dostępu do danych, to przede wszystkim zadanie dla optymalizatora zapytań. Najważniejszą zasadą jego działań jest zawsze przewidywalny efekt końcowy. Musi on być w 100% zgodny z opisanymi zasadami teoretycznymi.

Optymalizator potrafi ograniczać i efektywnie łączyć zbiory. Obecne w analizie teoretycznej iloczyny kartezjańskie czy też pobieranie wszystkich wierszy tabeli zazwyczaj nie mają miejsca.
Zapiszmy więc w różny sposób kwerendę realizującą zadanie z przykładu pierwszego :

-- Pierwszą tabelą do której odwołujemy się jest dbo.Customers
SELECT    c.CompanyName, o.OrderID, o.OrderDate , ProductName
FROM      dbo.Customers c 
	inner join dbo.Orders o on c.CustomerID = o.CustomerID 
	inner join dbo.[Order Details] od on od.OrderID = o.OrderID
	inner join dbo.Products p on od.ProductID = p.ProductID
WHERE c.City = 'Madrid' AND o.OrderDate BETWEEN '1996-09-01' AND '1996-09-30'
ORDER BY CompanyName, OrderDate DESC 
 
-- Pierwszą tabelą do której odwołujemy się jest dbo.Orders
SELECT    c.CompanyName, o.OrderID, o.OrderDate , ProductName
FROM      dbo.Orders o 
	inner join dbo.[Order Details] od on od.OrderID = o.OrderID 
	inner join dbo.Products p on od.ProductID = p.ProductID
	inner join  dbo.Customers c on c.CustomerID = o.CustomerID 
WHERE c.City = 'Madrid' AND o.OrderDate BETWEEN '1996-09-01' AND '1996-09-30'
ORDER BY CompanyName, OrderDate DESC 
 
-- Pierwszą tabelą do której odwołujemy się jest dbo.Products 
SELECT    c.CompanyName, o.OrderID, o.OrderDate , ProductName
FROM      dbo.Products p 
	inner join dbo.[Order Details] od on od.ProductID = p.ProductID
	inner join dbo.Orders o on od.OrderID = o.OrderID
	inner join  dbo.Customers c on c.CustomerID = o.CustomerID 
WHERE c.City = 'Madrid' AND o.OrderDate BETWEEN '1996-09-01' AND '1996-09-30'
ORDER BY CompanyName, OrderDate DESC

Pomimo, że złączenia zapisane są w różnej kolejności, silnik bazodanowy i tak po swojemu zrealizuje plan działania. Wszystkie mają identyczny :

Execution_PLAN_SQL

Pytanie które samo nasuwa się po analizie tego przypadku. Czy jednak kolejność joinów może mieć znaczenie? Jeśli tak to może spróbujmy „zoptymalizować” zapytanie i na siłę ją zmienić.


Kolejność połączeń a wydajność

Kolejność jest istotna. W końcu każde z takich łączeń, może produkować różnej wielkości zbiory pośrednie. Możemy założyć, że im bardziej selektywnie będą wykonywane złączenia od samego początku, tym bardziej efektywnie zostanie przetworzona cała kwerenda.

Sprawdźmy więc jakie plany zapytań, będą generowane dla pokazanych powyżej zapytań z zastosowaniem siłowego wymuszenia kolejności połączeń.

Mam możliwość ingerowania w proces optymalizacji poprzez zastosowanie HINTów. Do wymuszenia kolejności złączeń służy opcja :

          OPTION (FORCE ORDER)

Która wymusza na optymalizatorze określoną kolejność od LEWEJ do PRAWEJ. Wykonaj poniższy skrypt, który obrazuje 4 kwerendy zwracające te same wyniki. Skorzystaj z opcji Include Actual Execution Plan, aby porównać plany wykonania i ocenić procentowy udział obciążeniowy każdego z tych poleceń.

-- Kwerenda 1 – dajmy szanse optymalizatorowi
select c.CompanyName, o.OrderID , o.OrderDate , ProductName
from dbo.Customers c 
	inner join dbo.Orders o on c.CustomerID = o.CustomerID 
	inner join dbo.[Order Details] od on od.OrderID = o.OrderID
	inner join dbo.Products p on od.ProductID = p.ProductID
where c.City = 'Madrid'    and o.OrderDate between '1996-09-01' and  '1996-09-30'
order by CompanyName, OrderDate desc
 
-- Kwerenda 2 z FORCE ORDER - identycznie zapisana jak Kwerenda 1
select c.CompanyName, o.OrderID , o.OrderDate , ProductName
from dbo.Customers c 
	inner join dbo.Orders o on c.CustomerID = o.CustomerID 
	inner join dbo.[Order Details] od on od.OrderID = o.OrderID
	inner join dbo.Products p on od.ProductID = p.ProductID
where c.City = 'Madrid'    and o.OrderDate between '1996-09-01' and  '1996-09-30'
order by CompanyName, OrderDate desc
OPTION (FORCE ORDER)
 
-- Kwerenda 3 z FORCE ORDER
select c.CompanyName, o.OrderID , o.OrderDate , ProductName
from dbo.Orders o 
	inner join dbo.[Order Details] od on od.OrderID = o.OrderID 
	inner join dbo.Products p on od.ProductID = p.ProductID
	inner join  dbo.Customers c on c.CustomerID = o.CustomerID 
where City = 'Madrid'    and OrderDate between '1996-09-01' and  '1996-09-30'
order by CompanyName, OrderDate desc
OPTION (FORCE ORDER)
 
-- Kwerenda 4 z FORCE ORDER
select c.CompanyName, o.OrderID , o.OrderDate , ProductName
from dbo.Products p 
	inner join dbo.[Order Details] od on od.ProductID = p.ProductID
	inner join dbo.Orders o on od.OrderID = o.OrderID
	inner join  dbo.Customers c on c.CustomerID = o.CustomerID 
where City = 'Madrid'    and OrderDate between '1996-09-01' and  '1996-09-30'
order by CompanyName, OrderDate desc
OPTION (FORCE ORDER)

Wydajnościowo, najlepsza okazała się kwerenda pierwsza, w której pozwoliliśmy optymalizatorowi, wybór własnego planu wykonania. Każda inna, wykorzystująca podpowiedź, siłowego narzucania kolejności działań Wyniki mówią same za siebie :
Zapytania_SQL_do_wielu_tabel_9


Stosowanie hintów bywa pomocne ale musi być stosowane świadomie. Rozkłady, optymalne sposoby dostępu do danych mogą się zmieniać. Raz utworzony plan, może po pewnym czasie nie być optymalnym, dlatego raczej odradzałbym stosowanie tego typu wskazówek początkującym użytkownikom SQL.

Generowanie zbioru – wektor dyskretnych i ciągłych wartości

W praktyce pisania zapytań SQL, nieraz spotkałem się z potrzebą wygenerowania automatycznej tabeli. Na przykład osi czasu z uwzględnieniem każdego dnia albo wektora kolejnych liczb z zadanego przedziału.

W artykule tym, zademonstruję jak wygenerować za pomocą CTE, zbiór zawierający inkrementowane elementy z określonego przedziału.

Generowanie wektora danych za pomocą CTE

Zastosowań takiego ciągłego, dyskretnego zbioru jest wiele. Rozważmy taki scenariusz.

Chcę utworzyć raport, zagregowanej sumy wszystkich złożonych zamówień w zadanym przedziale dat. Istotnym dla mnie jest zachowanie skali czasu – tak aby pokazane były na niej wszystkie dni z zadanego okresu. Również te, w których nie spłynęło ani nie zostało zrealizowane żadne zamówienie. Chcę także na tym samym wykresie pokazać informacje o realizacji zamówień.


To co potrzebuję na początek, to tabeli w postaci wektora ze wszystkimi kolejnymi dniami np. od ‘2014-02-01’ do ‘2014-02-14’. Do takiego zbioru (osi czasu) będę odnosił się analizując już szczegółowo daty zamówień.

Można stworzyć taką tabelę za pomocą wielu operacji UNION (niewygodne). Można też ładować w pętli wartości do tabeli tymczasowej. Nie są to jednak „ładne” i uniwersalne sposoby.

Do utworzenia takiego ciągłego zbioru, idealnie nadaje się rekurencyjne CTE. W SQL Server 2012, można za ich pomocą tworzyć nieograniczone (rekursywnie) zbiory. Opisuję w detalach strukturę rekurencyjnych CTE w ramach kursu SQL.

Kwerenda, która wygeneruje interesujący mnie zbiór, będzie wyglądała tak :

WITH Daty AS
(
	-- Rekurencyjne Common Table Expression 
          -- domyślnie max 100 przebiegów rekurencyjnych, czyli będzie działało 
          -- do 2014-05-12 :) potem trzeba użyć MAXRECURSION
 
	SELECT cast('2014-02-01' as SmallDatetime) as Data , 1 as Liczba
 
	UNION ALL
 
	SELECT Data+1, Liczba+1
	FROM Daty a
	WHERE a.Data < getdate()-1
 
)
SELECT * FROM Daty

CTE_wektor_wartosci

Szczególnie fajną rzeczą w CTE, jest możliwość korzystania z nich w różnych strukturach – np. widokach. Tworzenia tabel tymczasowych i technik programistycznych nie wpleciemy w zwykły widok.

Rekurencyjne CTE są domyślnie ograniczone do 100 przebiegów. Możemy jednak sterować ich maksymalną liczbą, stosując opcję MAXRECURSION.

Parametr ten przyjmuje wartości od 0 (nieograniczona ilość przebiegów) do 32767. Tworzenie np. zbioru (wektoru) z 1000 kolejnych liczb, wymaga zastosowania opcji MAXRECURSION. W przeciwnym razie, po wykonaniu setnego (domyślnie) przebiegu rekurencyjnego, otrzymamy błąd :

Msg 530, Level 16, State 1, Line 3
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Zatem jeśli potrzebujemy większy wektor, na przykład wspomniany 1000-elementowy, to utworzymy go w ten sposób :

with Liczby as 
(
	select  1 as Liczba
	UNION ALL
	select Liczba+1
	from Liczby a where a.Liczba < 1000
 
)
Select * from Liczby
OPTION (MAXRECURSION 0)
Liczba
-----------
1
2
3
. . .
998
999
1000

(1000 row(s) affected)

Wróćmy jednak do naszego scenariusza. Skrypt generujące przykładowe dane, z których korzystam w dalszej części znajdziesz tutaj.

Kwerenda odnosiła się będzie bezpośrednio do utworzonego wektora czasu (pierwsze CTE – Daty). Jest on główną osią na której pokazywać będę zagregowane i narastające sumy zleceń.

with Daty as 
(
	SELECT cast('2014-02-01' as date) as Data 
 
	UNION ALL
 
	SELECT DateAdd(dd,1,a.Data)
	from Daty a
	where a.Data < getdate()-1
 
),
Orders_raport as (
 
	SELECT a.Data,  count(distinct o.Order_id) as Orders_QTY, 
	     SUM(count(distinct o.Order_id)) OVER(order by a.Data)  as Agg_Orders_Qty,
	     count(distinct o2.Order_id) as Comp_Orders_QTY, 
	     SUM(count(distinct o2.Order_id)) OVER(order by a.Data)  as Agg_Comp_Order_qty
	FROM Daty a 
	     left join dbo.Orders o on a.Data =  cast(o.create_date as date) 
                         and cast(o.create_date as date) > '2014-01-31'
	      left join dbo.Orders o2 on a.Data = cast(o2.completion_date as date) 
                         and cast(o2.create_date as date) > '2014-01-31'
	GROUP BY a.Data
 
) 
SELECT *
FROM Orders_raport
ORDER BY  DATA

CTE_wektor_wartosci2

Teraz na podstawie tak przetworzonych danych, łatwo np. w Excelu wygenerować wykres – co należy podkreślić – w ciągłej dziedzinie czasu.

CTE_wektor_wartosci3

Automatyzacja zadań w SQL Server Express

Firma Microsoft, podobnie jak inni producenci najważniejszych, komercyjnych systemów bazodanowych (ORACLE, IBM) udostępnia darmową (również do zastosowań komercyjnych) wersję serwera baz danych – SQL Server, edycja Express.

Jest ona okrojona w stosunku do pełnych edycji, przede wszystkim w zakresie możliwości wykorzystania wysoko wydajnego, skalowalnego środowiska, a także jeśli chodzi o zakres funkcjonalności. Nie mniej jednak, od strony możliwości silnika bazodanowego – darmowa wersja Express, sprawdza się znakomicie w wielu zastosowaniach komercyjnych. Pełne porównanie możliwości poszczególnych edycji SQL Server 2012 znajdziesz tutaj.

Najbardziej dotkliwe ograniczenia, związane są z maksymalnym rozmiarem bazy (w SQL Server Express 2012 jest to max 10 GB/bazę) oraz okrojonymi zasobami hardware, które będą wykorzystane do obsługi procesu serwera (max 1 procesor lub 4 rdzenie oraz max 1GB pamięci RAM). Są one szczególne dotkliwe i nieakceptowalne w większych środowiskach, jednak SQL Server Express jest bardzo dobrym rozwiązaniem do małych, mniej wymagających środowisk. Poza tym nic nie stoi na przeszkodzie, aby w miarę rozwoju firmy, przejść do wersji mocniejszej – płatnej.

Poza ograniczeniami związanymi z zasobami, jednym z ograniczeń funkcjonalnych w porównaniu z płatnymi wersjami, jest brak SQL Server Agent. Jest to usługa odpowiedzialna m.in. za wykonywanie zaplanowanych działań. Obojętnie czy będą to zadania (JOBy) serwisowe, administracyjne czy np. synchronizujące, replikujące dane (import, export) – w SQL Server Express musimy użyć innych metod do automatycznego uruchamiania zaplanowanych procesów.

Automatyzacja zadań w środowisku bazodanowym jest bardzo przydatna i praktycznie w każdym wdrożeniu jest stosowana (chociażby do robienia kopii zapasowych). Jednym ze sposobów na obejście braku SQL Agent w SQL Server Express, jest wykorzystanie narzędzia linii poleceń SQLCMD, uruchamianego za pomocą standardowego harmonogramu zadań w Windows (Windows Scheduler). Do realizacji automatyzacji, można zaprząc również usługę Brokera (Service Broker), jednak nie będę tej metody tutaj opisywał.

Artykuł ten jest poświęcony rozwiązaniu bazującego na połączenia możliwości SQLCMD i Harmonogramu zadań Windows.


SQLCMD czyli dostęp do SQL Server z linii poleceń

SQLCMD to proste narzędzie, za pomocą którego możemy łączyć się z instancją SQL Server i wykonywać dowolne polecenia T-SQL. Jest następcą OSQL, znanego z SQL Server 2000 i powinno być używane zawsze, jeśli chcemy wykonywać jakiekolwiek polecenia T-SQL z linii poleceń (w SQL Server 2012, OSQL nie jest już dostępny).

Połączenie do bazy SQL Server za pomocą SQLCMD

Za pomocą SQLCMD możemy łączyć się z wybranym serwerem i wykonywać polecenia, kwerendy T-SQL w trybie interaktywnym lub automatycznym. Użycie tego toola jest proste, wystarczy wskazać instancję, sposób uwierzytelniania i już możemy połączyć się z SQL Server. Pełen zakres parametrów, które możemy użyć, wyświetli standardowa flaga -? (tu SQLCMD w wersji 10.5 czyli z SQL Server 2008 R2):

C:\>sqlcmd -?

sqlcmd_options

Jeśli instancja SQL Server do której chcesz się połączyć jest nazwana i chcesz użyć zintegrowanego uwierzytelniania Windows, trzeba zastosować flagi wskazujące serwer : –S server_name oraz tryb autentykacji –E (trusted connection). W ten prosty sposób, uzyskasz połączenie z serwerem w ramach autentykacji Windows (aktualnego konta). Będziesz połączony ze „swoją” domyślną bazą danych, właściwą dla konta w którego kontekście się logujesz.

C:\sqlcmd -S MyServer\SQLEXPRESS -E

W trybie interaktywnym, możemy wprowadzać polecenia SQL linia po linii, aby je wykonać użyj komendy GO.

sqlcmd_interactive

Nie będę tu opisywał wszystkich możliwości narzędzia SQLCMD, bo nie jest to przedmiotem tego artykułu, ograniczę się tylko do tego co istotne w kontekście automatyzacji, wykonywania zadań.

Wykonywanie skryptów T-SQL za pomocą SQLCMD

Polecenia T-SQL możemy wykonywać za pomocą SQLCMD w trybie interaktywnym lub nazwijmy to automatycznym (bez interakcji użytkownika). W naszym scenariuszu, interesujące będą dwie opcje.

Opcja QUERY : czyli flaga -Q – umożliwia wykonanie dowolnej kwerendy (Query) za pomocą SQLCMD bez konieczności wchodzenia w tryb interaktywny. Po prostu określona kwerenda lub batch (może to być przecież szereg komend T-SQL) zostanie wykonana na danym serwerze w kontekście konta określonego w parametrach połączenia.

C:\sqlcmd -S MyServer\SQLEXPRESS -E -Q "SELECT getdate()"

sqlcmd_query

Opcja INPUT FILE : flaga – i – pozwala na wykonanie skryptu T-SQL, zapisanego w pliku wejściowym (input file). Może to być dowolny skrypt zapisany w pliku tekstowym, wykonujący określone zadania. Dla prostego przykładu, załóżmy że nasz skrypt będzie wykonywał prostą operację backupu. Zawartość pliku backup_script.sql niech wygląda następująco :

DECLARE @FileName varchar(100),  @BackupName varchar(100)
 
SET @FileName = N'D:\Backups\TEST_FULL_BACKUP_' + 
	Replace(Convert(varchar(10),getdate(),120),'-','_') + '.bak'
 
SET @BackupName = N'Test-Full Database Backup from ' + 
          Convert(varchar(10),getdate(),120)
 
-- skrypt uruchamiany codziennie, unikalna nazwa pliku
PRINT @FileName + char(10) + @BackupName + char(10) + REPLICATE('-',50)
 
BACKUP DATABASE [TEST] TO DISK = @FileName 
	WITH FORMAT, 
	NAME = @BackupName

Dwa słowa komentarza. Skrypt ten będzie tworzył pełen backup bazy danych TEST, zapisywał go do pliku o nazwie zawierającej bieżącą datę. Taka forma może być przydatna, jeśli zadanie to będzie wykonywane np. raz dziennie i chcemy, żeby każdy backup był w osobnym pliku (stąd konieczność zapewnienia unikalnej nazwy).
Wywołanie teraz SQLCMD z parametrem –i (input file) będzie następujące :

C:\sqlcmd -S MyServer\SQLEXPRESS -E -i D:\temp\backup_script.sql

sqlcmd_input_file

Zatem wiemy już w jaki sposób działa SQLCMD i jak możemy wywołać dowolne skrypty T-SQL z linii poleceń. Jedyne co nam pozostało do realizacji celu automatyzacji zadań w SQL Server Express to wyzwalanie skryptów automatyczne. Do tego celu idealnie nadaje się Harmonogram Zadań Windows.


Planowanie zadań w Harmonogramie zadań (Windows Scheduler)

Windows Scheduler to usługa pozwalająca na zdefiniowanie zadań w ramach serwera / stacji roboczej, pracującej pod kontrolą systemu operacyjnego Windows. Zdefiniowanie nowego zadania jest bardzo proste. Sprowadza się w zasadzie do wskazania programu lub skryptu, który będzie uruchamiany zgodnie z określonym harmonogramem. Możemy także określić konto (login) w kontekście którego dane zadanie zostanie wykonane.

W naszym scenariuszu, będziemy chcieli wywołać skrypt backupu bazy TEST, za pomocą już znanej składni SQLCMD oraz przygotowanego wcześniej skryptu T-SQL (plik backup_script.sql). Ponadto, będziemy chcieli dokonać kompresji (zip) tegoż i na koniec zrobimy mały porządek. Skasujemy wszystkie starsze niż 14 dni kopie zapasowe oraz inne, niespakowane zipem pliki w katalogu backupów.

Na początek utwórzmy skrypt (batch) o nazwie backup_bazy_TEST.bat. Skrypt ten będzie odpalany automatycznie codziennie. Zawartość pliku :

sqlcmd –S SQLEXPRESS -E –iD:\temp\backup_script.sql
set dt=%DATE%
set dt = %dt:-=_%
zip d:\backs\TEST_FULL_BACKUP_%dt:-=_%. zip d:\backs\TEST_FULL_BACKUP_%dt:-=_%.bak
Forfiles -p D:\backs\ -s -m TEST_FULL_BACKUP*.zip -d -7 -c "cmd /c del /q @path"
del D:\backs\*.bak

To co najważniejsze, dzieje się tak naprawdę w pierwszej linii– uruchomienie skryptu backupu bazy. Następnie przypisanie do zmiennej dt informacji o bieżącej dacie, aby móc odpowiednio obsłużyć (spakować), utworzony plik backupu. Na koniec stosuję narzędzie FORFILES do skasowania wszystkich plików backupu starszych niż 14 dni. Oczywiście można użyć kompresje już w trakcie jego tworzenia, potraktuj ten przykład czysto edukacyjnie – chodziło mi o pokazanie szeregu działań w zadaniu.

Mamy więc plik skryptu, teraz trzeba zaplanować zadanie w ramach którego będzie on uruchamiany.

Poniżej kilka kluczowych screenów.

  • Dodanie nowego zadania w harmonogramie zadań
    Harmonogram_zadan_Plan_backupu_bazy_01
  • Określenie akcji (jaki program lub skrypt ma być uruchomiony)
    Harmonogram_zadan_Plan_backupu_bazy_02
  • Określenie harmonogramu (Wyzwalacze)
    Harmonogram_zadan_Plan_backupu_bazy_03

Podsumowanie

Pomimo swoich ograniczeń funkcjonalnych, w SQL Server Express jesteśmy w stanie wykonywać dowolne zadania automatycznie. Pokazany w tym przykładzie skrypt backupu, mógłby być w prosty sposób rozszerzony o pełne logowanie samego procesu, czy backup zakończył się powodzeniem, ile trwał, wysyłać powiadomienia mailowe w przypadku wystąpienia błędu.

BULK INSERT – import danych z pliku do bazy SQL Server

Polecenie BULK INSERT jest rozszerzeniem języka T-SQL, funkcjonalnym odpowiednikiem narzędzia bcp z parametrem in. Za jego pomocą możemy wykonać szybki, masowy import danych z pliku tekstowego do istniejącej tabeli w bazie danych – z poziomu skryptu T-SQL. Idealnie nadaje się więc do automatyzacji zadań, definiowanych np. w JOBach, procedurach składowanych, czy skryptach adhoc, mających na celu import danych z plików tekstowych do bazy.

Artykuł ten ma na celu pokazanie najczęściej stosowanych parametrów, praktyczne przykłady użycia BULK INSERT.


Import surowej zawartości pliku tekstowego do tabeli

W najprostszym zastosowaniu, możemy zaimportować dowolny plik tekstowy do istniejącej tabeli w bazie SQL Server. Bardziej skróconej wersji zastosowania BULK INSERT nie ma.

create table dbo.test
(
   Full_Row varchar(1000)
)
 
 
BULK INSERT dbo.test
	FROM   'C:\temp\plik.txt'

Jest to najkrótsza i najskromniejsza komenda BULK INSERT. W tym przypadku, zastosowane będą domyślne znaczniki końca wartości kolumny (atrybutu) tzw. FIELDTERMINATOR którym będzie symbol /t czyli tabulator. Drugim istotnym symbolem – końca wiersza – ROWTERMINATOR będzie domyślnie złączenie /n/r, next row + carriage return. Ponieważ ładujemy wszystko do tabeli tymczasowej, która posiada tylko jedną kolumnę (Full_Row), domyślny znacznik FIELDTERMINATOR, nie będzie zastosowany. Każdy wiersz czytanego pliku, będzie ładowany do nowego wiersza tej tabelki.

Powyższa składnia jest o tyle przyjemna, że można w ten sposób wrzucić do tabeli tymczasowej zawartość dowolnego pliku tekstowego w zasadzie bez rozróżniania jego struktury, biorąc pod uwagę tylko koniec wiersza.


Import strukturyzowanej zawartości pliku tekstowego do tabeli

W typowej sytuacji importu, przechowujemy dane zorganizowane w określony sposób. Plik tekstowy reprezntuje, zazwyczaj tabelaryczny zbiór elementów, opisany za pomocą atrybutów (kolumn), których wartości rozdzielone są znakiem specjalnym (FIELDTERMINATOR).

Importując dane, musimy zadbać aby liczba kolumn, typy danych, były zgodne pomiędzy tabelą docelową a zawartością pliku. W przypadku niedopasowania typu danych oraz braku możliwości wykonania niejawnej konwersji typu danych, otrzymamy komunikat :

Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 3 (kolumna).

Skupmy się jednak na składni BULK INSERT. Żeby jawnie określić znaki terminujące wartości kolumn oraz wiersza (elementu), stosujemy parametry FIELDTERMINATOR oraz ROWTERMINATOR. Określane są one w bloku WITH.

Zawartość pliku który będziemy chcieli zaimportować niech wygląda tak :
BULK_INSERT_01
Jak widać, są w nim dwa wiersze, składające się z dwóch oraz trzech wartości. W takim razie do dzieła :

use TempDB
go
 
IF OBJECT_ID('dbo.test') is not null drop table dbo.test
 
create table dbo.test
(
	Field1 varchar(1000),
	Field2 varchar(1000)	
)
 
-- tutaj zaczyna się import z pliku C:\temp\test.txt do tabeli dbo.test
BULK INSERT dbo.test
	FROM   'C:\temp\test.txt'
	WITH
	(
		FIELDTERMINATOR = ',',
		ROWTERMINATOR = '\n', 
		CODEPAGE =  'ACP' -- | 'OEM' | 'RAW' | 'code_page'  
	)
 
select * from dbo.test

BULK_INSERT_02

W powyższym przykładzie zastosowałem parametr opcjonalny CODEPAGE, który określa stronę kodową znaków. ACP czyli kodowanie ANSI. W zależności od zawartości importowanego pliku, możemy zastosować inną, odpowiednią dla naszego środowiska stronę kodową (code_page).

Zauważ, że liczba atrybutów (kolumn) w pliku jest różna. W tej sytuacji nastąpi próba dopasowania struktury pliku do tabeli docelowej. W wierszu drugim, wartość czwarty,piąty została potraktowana jako jedna (druga kolumna).

Gdy importowane dane zawierają różne liczby kolumn oraz operacja niejawnego dopasowania jest możliwa – zostanie to wykonane. Należy jednak uważać na tego typu wyjątki, operacje importu/eksportu powinny być ściśle określone aby uniknąć pomyłek. Najlepiej w tym celu stosować dodatkowego parametru określającego plik formatu FORMATFILE (fmt). Tworzymy go np. za pomocą bcp i zawiera on informację o strukturze przechowywanych danych.

W przypadku braku możliwości dopasowania, lub jeśli np n-ty wiersz importowanych danych będzie zawierał mniej kolumn niż oczekuje tego struktura tabeli docelowej – otrzymasz następujacy, niewiele mówiący komunikat o błędzie.

Msg 4832, Level 16, State 1, Line 13
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 13
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 13
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Pozostałe przydatne opcje BULK INSERT

FIRSTROW – od którego wiersza z pliku importować (wartość integer).

LASTROW – który wiersz ma być tym ostatnim (wartość integer).

KEEPNULLS – w trakcie importu możesz się spotkać z sytuacją, że będziesz importował dane zawierające null w kolumnach, na których jest zdefiniowana wartość domyslna (DEFAULT VALUE). W większości przypadków operacja eksportu i importu, powinna być symetryczna. Dlatego jeśli chcesz być pewien, że importowane dane będą dokładnie odzwierciedlone w tabeli docelowej (nie zostanie zamieniony NULL na DEFAULT VALUE) – skorzystaj z tej opcji.

KEEPIDENTITY – zachowuje oryginalne wartości IDENTITY w tabeli do której importujemy, nawet jeśli dane dla tej kolumny są określone. Właściwość wyłączania mechanizmów integralności wewnętrznej danych (constraints, foreign_keys) jest charakterystyczna dla importu masowego.

Poniższy przykład obrazuje zachowanie KEEPIDENTITY.
BULK_INSERT_03

use tempdb
go
 
IF OBJECT_ID('dbo.test') is not null drop table dbo.test
 
create table dbo.test
(
    -- na kolumnie id jest IDENTITY, powinno więc ono nadawać
    -- kolejne numery wierszy poczynając od 1
	id int IDENTITY(1,1),
	Field varchar(1000)
 
)
GO
 
BULK INSERT dbo.test
	FROM   'C:\temp\test2.txt'
	WITH
	(
		FIELDTERMINATOR = ',',
		ROWTERMINATOR = '\n',
		KEEPIDENTITY
	)
-- 3 razy zostanie wykonany BULK INSERT - GO 3 
GO 3 
 
select * from dbo.test

BULK_INSERT_04
Jak widać, pomimo istnienia IDENTITY na kolumnie Id – numeracja jest zgodna z danymi importowanymi z pliku.

FIRE_TRIGGERS – domyślnie w operacji BULK INSERT wszystkie triggery związane z operacją INSERT na danej tabeli są wyłączone. Operacja BULK INSERT jest importem masowym. W domyśle stosowany do zasilenia bazy maksymalnie szybko, dużymi ilościami danych. Często zależy nam na tym, aby wszelkiego rodzaju spowalniacze były domyślnie wyłączone – właśnie z powodu apsketu wydajnościowego.

Jeśli jednak od działania triggerów zależy spójność danych i powinny zostać wywołane, trzeba jawnie określić ich włączenie tym parametrem.

Pamiętać trzeba, że w SQL Server, triggery wywoływane są w kontekście całego batcha. Jeśli cały import jest wykonywany w jednym logicznym kroku (batch’u), triggery będą wywołane raz dla wszystkich wierszy. Jeśli import podzielony zostanie na n-batch’y (opcje BATCHSIZE i KILOBYTES_PER_BATCH), triggery wykonają się n razy na n partiach ładowanych wierszach.

Poniżej mały przykład pokazujący, że bez FIRE_TRIGGER w trakcie importu BULK INSERT, zdefiniowane triggery, na tabeli do której importujemy, nie działają.

use tempdb
go
 
IF OBJECT_ID('dbo.test') is not null drop table dbo.test
IF OBJECT_ID('dbo.test_arch') is not null drop table dbo.test_arch
 
create table dbo.test
(
	id int identity(1,1),
	Field1 varchar(1000) 	
)
GO
 
create table dbo.test_arch
(
	id int identity(1,1),
	Field_Arch varchar(1000)
)
GO
 
-- na tabeli dbo.test tworzymy trigger, który w ciemno kopiuje
-- wszystko co jest insertowane do dbo.test do tabeli dbo.test_arch
CREATE TRIGGER arch
	on dbo.test
AFTER INSERT
AS
BEGIN
 	insert into dbo.test_arch
	select 'new_' + Field1 from inserted
END
GO
 
BULK INSERT dbo.test
	FROM   'c:\temp\test2.txt'
	WITH
	(
		FIELDTERMINATOR = ',',
		ROWTERMINATOR = '\n'
	)
 
select * from dbo.test
select * from dbo.test_arch

BULK_INSERT_05

Tym razem BULK INSERT z opcją FIRE_TRIGGER

BULK INSERT dbo.test
	FROM   'c:\temp\test2.txt'
	WITH
	(
		FIELDTERMINATOR = ',',
		ROWTERMINATOR = '\n',
		FIRE_TRIGGERS		
	)
 
select * from dbo.test
select * from dbo.test_arch

BULK_INSERT_06

CHECK_CONSTRAINTS – podobnie jak triggery, domyślnie wszelkie ograniczenia sprawdzające, w tym również klucze obce, są ignorowane w trakcie wykonywania importu masowego. Wynika to z definicji – import masowy ma być wykonywany maksymalnie szybko, dane powinny być wcześniej zweryfikowane.

Po imporcie wszelkie ograniczenia na tabeli są oznaczane flagą not-trusted. Może skutkować to poważnymi konsekwencjami, nie tylko z punktu widzenia zachowania spójności danych, ale także degradacją wydajności wykonywanych zapytań. Po imporcie masowym, wszelkie ograniczenia (constraints oraz foreign_keys) powinny zostać zweryfikowane i oznaczone jako trusted.

Jeśli chcesz szybko znaleźć informacje o niezaufanych ograniczeniach wykonaj po imporcie następujące zapytanie :

SELECT OBJECT_NAME(parent_object_id) AS table_name, name, is_disabled  , is_not_trusted
FROM sys.check_constraints 
Where is_not_trusted = 1 or is_disabled = 1
UNION ALL
SELECT OBJECT_NAME(parent_object_id) AS table_name, name, is_disabled  , is_not_trusted
FROM sys.foreign_keys
Where is_not_trusted = 1 or is_disabled = 1
ORDER BY table_name

Da ono odpowiedź o wszystkich niezaufanych lub wyłączonych ograniczeniach. Żeby teraz na nowo aktywować/sprawdzić i oznaczyć jako zaufane, należy wykonać polecenie z double check.

-- sprawdzi i oznaczy jako zaufane (o ile nie będzie w kolumnach nieprawidłowych wartości)
-- wszystkie ograniczenia na tabeli dbo.tabela
ALTER TABLE dbo.tabela WITH CHECK CHECK CONSTRAINT all

Jeśli chcesz wykonać aktualizację na wszystkich tabelach w bazie, możesz do tego celu zastosowwać nieudokumentowną, ale użyteczną procedurkę sp_msforeachtable :

exec sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'

KILOBYTES_PER_BATCH oraz BATCHSIZE – określają podział importu na n części, wykonywanych w osobnych transakcjach. Paczki (batche) mogą być określone przez liczbę rekordów (BATCHSIZE) lub wielkość bezwzględną wyrażoną w KB.

TABLOCK – zakłada blokadę na całej tabeli do której importujemy dane. Przyspiesza to czas wykonania importu.

ORDER – określa sposób sortowania przed importem, aby również przyspieszyć jego wykonanie. Ma to szczególne znaczenie, gdy importujemy do tabeli z indeksem klastrowym, wtedy dodając posortowane elementy zgodnie z jego definicją, operacje przebudowy i utrzymania indeksu w trakcie importu są zoptymalizowane.

FORMATFILE – określa ścieżkę do pliku przechowującego informacje o metadanych importowanej struktury. Plik formatu możesz utworzyć za pomocą narzędzia bcp, jako część eksportu. Przydaje się w szczególności, gdy np. wykesportowane dane mają zamienione kolejności kolumn w stosunku do struktury tabeli do której je importujemy lub poprostu ich liczba się różni.

Łączenie tabel – pobieranie danych z wielu źródeł

W klauzuli FROM, określamy przede wszystkim źródła (zbiory) z których chcemy pobierać dane. Możliwości w zakresie pobierania danych z jednego zbioru, opisuję w artykule dot. źródeł danych stosowanych we FROM.

W zapytaniu SQL możemy odwoływać się do jednego lub wielu zbiorów. Jeśli chcemy wybierać z przynajmniej dwóch, powinniśmy określić sposób ich połączenia oraz warunki.

W artykule tym, znajdziesz opis pełnego zakresu możliwości FROM – czyli wszystkie sposoby łączenia tabel, zbiorów oraz określania warunków złączeń. Pamiętaj, że mogą to być tabele, widoki, wspólne wyrażenia tablicowe (CTE), zmienne i funkcje tabelaryczne czy podzapytania. Dla uproszczenia, będę je nazywał wymiennie – tabele / zbiory – mając na myśli wszystkie obiekty tabelaryczne, do których możemy odwoływać się we FROM.


Łączenie tabel SQL – zasady ogólne

Dla przypomnienia, FROM jest pierwszym krokiem przetwarzania zapytania. Każdy kolejny, bazuje na pośredniej, wynikowej tabeli wirtualnej, poprzedniego. Zbiór otrzymany po przetworzeniu całego kroku (np. FROM) jest wejściem, następnego (WHERE).

Niezależnie od wybranego typu złączenia, w wyniku przetwarzania FROM, otrzymujemy zawsze zbiór elementów (virtual table VT), opisany za pomocą wszystkich kolumn tabel wejściowych. Nie ma znaczenia czy łączysz dwie czy więcej tabel połączeniem wewnętrznym, zewnętrznym. Elementy (rekordy, wiersze) tabeli wynikowej, będą określone zawsze przez wszystkie atrybuty (kolumny) łączonych zbiorów.

Przykładowo, zbiór wynikowy (VT) operacji łączenia trzech tabel złączeniem wewnętrznym INNER JOIN, będzie opisany przez wszystkie kolumny, trzech tabel wejściowych.
JOIN_02
Inną sprawą jest to, czy będziemy chcieli wszystkie z nich zwracać w kwerendzie. Pewnie nie, ale to określamy dopiero w SELECT. Mamy, więc wyjaśnioną pierwszą kwestię – strukturę zbioru wynikowego tabeli pośredniej.

Po wybraniu tabel, musimy określić jeszcze typ złączenia oraz ich warunki. Tutaj stosujemy w praktyce wiedzę na temat relacyjności bazy – czyli sposobów powiązań tabel między sobą.

Łączenie wielu zbiorów (trzech i więcej) sprowadza się do wielokrotnego wykonania operacji łączenia dwóch tabel
. W kolejnym rozdziale tego kursu, opisuję szczegóły przetwarzania zapytań do wielu tabel.

Zaprezentuję teraz po kolei wszystkie możliwe typy złączeń dwóch tabel, na przykładzie prostego scenariusza.

Istnieje firma X, której część pracowników (tabela EMP) posiada samochód służbowy (tabela CAR). W firmie samochody służbowe mogą być używane przez różne osoby, ale tylko jedna jest bezpośrednio przypisana i jest w pełni za niego odpowiedzialna.
Ponieważ firma dynamicznie się rozwija, część samochodów stoi na placu – nie przypisana jeszcze do nikogo. Obowiązkiem każdego pracownika, z wyjątkiem BOSS’a, jest prowadzenie comiesięcznego rozliczania użytkowanego pojazdu (tabela HIST)

BaseStructure
Kod źródłowy struktur na których prezentuję poszczególne przykłady, do pobrania tutaj.


INNER JOIN – łączenie wewnętrzne

W wyniku złączenia wewnętrznego (INNER JOIN) otrzymujemy tabelę wynikową (VT), składającą się ze wszystkich kolumn tabel wejściowych.

Tabela wynikowa zawierać będzie tylko takie elementy, dla których warunki złączenia wewnętrznego będą spełnione (w logice trójwartościowej, wynik musi być TRUE).
Klauzula FROM wraz z warunkami określonymi w ON jest pierwszym miejscem filtrowania rekordów. Wszystkie elementy dla których wynik nie będzie spełniony (FALSE oraz UNKNOWN), zostaną odrzucone.
INNER_JOIN

Logika łączenia tabel INNER JOIN

Zrozumienie zasad działania złączeń wewnętrzych jest kluczowe. Jest to część wspólna wszystkich typów złączeń INNER oraz OUTER JOIN.

W pierwszym kroku wykonywany jest iloczyn kartezjański obu tabel. Jest to połączenie każdego elementu zbioru A ze wszystkimi zbioru B.

Może to być dla Ciebie trudno do zaakceptowania :), ale uspokoję Cię – logiczne przetwarzanie zapytania a fizyczna jego realizacja to zupełnie co innego. Silnik relacyjny świetnie sobie z tym radzi. Nie oznacza to, że za każdym razem odwołując się do tabeli czytane są wszystkie jej rekordy. Jednak patrząc przez pryzmat zasad panujących w SQL, kroków logicznych – zakładamy, że tak właśnie jest.

Wiem że jeśli choć raz zdarzyło Ci się napisać lub spotkać z iloczynem kartezjańskim, tym trudniej będzie Ci ten fakt zaakceptować. Pod koniec tego akapitu – udowodnię tą zasadę za pomocą prostego przykładu, które napewno Cię przekona.

Po wyznaczeniu iloczynu kartezjańskiego, dla każdego wiersza obliczany jest wynik warunku (lub warunków) określonych w ON. Tu spotykamy się z logiką trójwartościową. Wynik może być spełniony = TRUE, niespełniony = FALSE lub nieznany czyli UNKNOWN (np. porównanie z NULL – więcej na ten temat w artykule o NULL).

Ostatnim krokiem jest odrzucenie wszystkich wierszy niespełniających warunków w ON. W zbiorze wynikowym zostają tylko te elementy, dla których wynik = TRUE.

Całość procesu łączenia wewnętrznego obrazuje poniższy diagram :
INNER_JOIN_01

Załóżmy więc, że w firmie X potrzebny jest raport z informacjami o pracownikach, którzy mają przypisany samochód służbowy.

SELECT * 
FROM dbo.EMP as e INNER JOIN dbo.CAR as c ON e.IdPrac=c.IdPrac

INNER_JOIN_02

INNER_JOIN_03

INNER JOIN jest złączeniem symetrycznym i nie ma specjalnego znaczenia czy łączymy tabelę A z B czy odwrotnie. Podobnie z warunkami w ON. Dla porządku, sensownie jest jednak zachować kolejność atrybutów w ON po tej samej stronie co określenie tabel źródłowych wobec operatora JOIN.

Poniższe warunki łączenia są równoważne :

FROM dbo.EMP e INNER JOIN dbo.CAR c ON e.IdPrac=c.IdPrac
 
FROM dbo.EMP e INNER JOIN dbo.CAR c ON c.IdPrac=e.IdPrac
 
FROM dbo.CAR c INNER JOIN dbo.EMP e ON e.IdPrac=c.IdPrac
 
FROM dbo.CAR c INNER JOIN dbo.EMP e ON c.IdPrac=e.IdPrac

Potencjalne problemy i błędy

To w jaki sposób zapiszemy warunki złączeń, podobnie jak w WHERE może mieć wpływ na wydajność (stosowanie funkcji etc.).

Generalnie INNER JOIN raczej nie stwarza kłopotów. Przyjrzyjmy się jednak tak zapisanemu warunkowi złączenia :

SELECT * 
FROM dbo.EMP e INNER JOIN dbo.CAR c ON e.IdPrac = 1

INNER_JOIN_04

Ponieważ tylko jeden wiersz tabeli dbo.EMP spełnia warunek określony w ON (IdPrac=1), zostaną zwrócone wszystkie wiersze będące wynikiem iloczynu kartzjańskiego tego wiersza z całym zbiorem rekordów tabeli dbo.CAR.

Dowodzi to, że faktycznie wykonywany jest iloczyn kartezjański w logicznym przetwarzaniu złączeń. Najpierw A x B, potem dopiero filtrowanie. Możemy pójść dalej i zapisać warunek w tym przkłądzie jako ON 1=1 – wtedy pełen iloczyn kartezjański gwarantowany bo nic nie zostanie odfiltrowane.


OUTER JOIN – łączenie zewnętrzne

Realizacja dowolnych połączeń zewnętrznych jest wykonywana, w pewnym zakresie, dokładnie tak samo jak wewnętrzne. Trzy pierwsze kroki logicznego przetwarzania są identyczne.

  1. Najpierw wykonywany jest iloczyn kartezjański tabeli A oraz tabeli B (łączymy każdy z każdym).
  2. Dla każdego wiersza, określany jest wynik warunków połączeń (zdefiniowane w ON) – TRUE, FALSE lub UNKNOWN.
  3. Następnie usunięcie wszystkich elementów z pośredniego zbioru wynikowego, dla których wynik połączenia (z p.2) jest różny od TRUE/

W połączeniach wewnętrznych to było wszystko. W zewnętrznych dodany jest jeszcze jeden krok.

W zależności od typu – LEFT, RIGHT lub FULL JOIN, wykonywane jest dopełnienie zbioru, o wszystkie elementy tabeli występującej po LEWEJ, PRAWEJ lub OBYDWU operatora JOIN, dla których wynik warunków nie był spełniony (FALSE lub UNKNOWN).

Brzmi to może trochę zawile, ale jest naprawdę proste i jeśli wiesz już jak działa połączenie wewnętrzne – tutaj dojdzie tylko ten jeden, dodatkowy krok. Zerknij na poniższe przykłady i z pewnością wszystko stanie się jasne.

LEFT OUTER JOIN – połączenie lewostronne otwarte

Postępujemy identycznie jak w INNER JOIN. Na koniec uzupełniamy zbiór wynikowy (INNER JOIN to tylko element C) o wszystkie elementy tabeli stojącej po LEWEJ stronie operatora JOIN (będą to rekordy A oraz B).

Ponieważ wiersze dopełniające muszą być również opisane, przez wszystkie kolumny łączonych tabel.- wartości atrybutów w tym przypadku TabeliB (po prawej stronie JOINa) będą nieznane czyli będą po prostu NULLami.

LEFT_JOIN_01

W naszym scenariuszu, niech będzie to zapytanie wyciągające dane o wszystkich pracownikach pracujących w Firmie oraz informacja czy dany pracownik ma przypisany samochód służbowy.

SELECT e.Imie, e.Nazwisko, e.Stanowisko , c.Marka 
-- LEFT JOIN oraz LEFT OUTER JOIN oznaczają dokładnie to samo
FROM dbo.EMP e LEFT JOIN dbo.CAR c ON e.IdPrac=c.IdPrac

LEFT_JOIN_02

RIGHT OUTER JOIN – łączenie zewnętrzne prawostronne

Ta sama historia co z LEFT JOIN tylko w drugą stronę :). Łączone są najpierw wewnętrznie dwa zbiory (INNER JOIN), na koniec dodawane są wszystkie niedopasowane elementy tabeli po PRAWEJ stronie operatora JOIN (elementy D oraz E). Ponieważ wartości kolumn tabeli po lewej stronie są nieznane, będą NULLami.

RIGHT_JOIN_01

W naszym scenariuszu może to być pytanie o szczegóły wszystkich samochodów służbowych, wraz z dodatkową informacją o osobie przypisanej

select  c.Marka, c.NrRej, c.Rocznik, e.Imie + ' ' + e.Nazwisko as Pracownik
from dbo.EMP e RIGHT JOIN dbo.CAR c on e.IdPrac=c.IdPrac

RIGHT_JOIN_02

Jak łatwo zauważyć, złączenia zewnętrzne LEFT i RIGHT nie są symetryczne. Wynik zależy od pozycji tabel względem operatora JOIN. Nie ma znaczenia zapis warunków (czyli to co jest po ON). Powyższe zapytanie, moglibyśmy zapisać równie dobrze jako połączenie LEFT JOIN i wynik będzie identyczny.

-- zamieniłem tylko kolejność tabel CAR i EMP oraz użyłem LEFT JOIN
SELECT  c.Marka, c.NrRej, c.Rocznik, e.Imie + ' ' + e.Nazwisko as Pracownik
FROM    dbo.CAR c LEFT JOIN dbo.EMP e on e.IdPrac=c.IdPrac

FULL OUTER JOIN – pełne złączenie zewnętrzne

Jeśli wiesz już jak działają INNER, LEFT, RIGHT – to wiesz także jak działa FULL JOIN ! Dopełnieniem zbioru wynikowego są wszystkie elementy obydwu łączonych zbiorów. Podobnie jak poprzednio, wartości nieznanych nie wymyślimy. Elementy dopełniające zbioru A (stojącego po lewej stronie operatora JOIN), będą miały uzupełnione wartości atrybutów tabeli B NULLami. Analogicznie będzie z dopełnieniem drugiego zbioru.

FULL_JOIN_01
W naszym przykładzie będzie to zapytanie zwracające informacje o pełnej relacji – wszystkich pracownikach i samochodach, zgodnie z ich przypisaniem.

SELECT e.Imie, e.Nazwisko, e.Stanowisko , c.Marka 
-- FULL JOIN to skrót od FULL OUTER JOIN 
FROM dbo.EMP e FULL JOIN dbo.CAR c ON e.IdPrac=c.IdPrac

FULL_JOIN_02

CROSS JOIN – iloczyn kartezjański

Raczej rzadko stosowane połączenie zbiorów. Jego sposób działania jest banalny – łączy każdy wiersz tabeli A z każdym wierszem tabeli B. Jako jedyne nie ma możliwości utworzenia warunków połączenia w ON bo z założenia ma połączyć wszystko ze wszystkim.

Skutek łączenia dwóch tabel zawierających po 100 rekordów – to tabela z 10000 wierszami, opisanych za pomocą wszystkich kolumn. Więc jeśli chcesz przetestować wytrzymałość DBA, możesz śmiało spróbować połączyć kilka średniej wielkości tabel :)

Złączenie typu CROSS JOIN jest realizowane również wtedy, gdy wyszczególnimy tabele we FROM, separując je tylko przecinkiem.

SELECT *
FROM dbo.EMP, dbo.CAR

Dlatego powinniśmy unikać stosowania warunków połączeń w WHERE (bardzo stary sposób, niezgodny z ANSI SQL:92).

SELF JOIN – połączenie tabeli z samą sobą

Wszystkie do tej pory prezentowane przykłady, zakładały łączenie dwóch różnych zbiorów. Język SQL jest elastyczny i jeśli coś jest zbiorem, może być użyte we FROM wiele razy.

Połączenia typu SELF JOIN to zawsze jedno z już poznanych – INNER, CROSS lub OUTER JOIN, w T-SQL nie stosuje się zapisu SELF JOIN. W strukturze tabeli dbo.EMP firmy X mamy zdefiniowaną referencję pomiedzy kolumnami IdManager i IdPrac.

select IdPrac, Imie, Nazwisko, Stanowisko, IdManager 
from EMP

SELF_JOIN_00
Wyświetlmy podstawowe dane dla wszystkich pracowników wraz z informacją o bezpośrednim przełożonym.
Ponieważ jest jedna osoba (BOSS), która nie ma przełożonego, musimy w tym zadaniu zastosować połączenie zewnętrzne. Aby móc połączyć dwie te same tabele, koniecznie musimy zastosować aliasy (e1 i e2).

SELECT e1.Imie + ' ' + e1.Nazwisko as Pracownik, e1.Stanowisko, 
	e2.Imie + ' ' + e2.Nazwisko as Manager, e2.Stanowisko as ManStanowisko
FROM dbo.EMP e1 LEFT OUTER JOIN dbo.EMP e2 on e1.IdManager=e2.IdPrac

SELF_JOIN_01

Ten przykład bazuje na istniejącej relacji, kluczu obcym tabeli dbo.EMP do samej siebie, jednak wcale nie musi ta referencja być jawnie i permanentnie określona.

Wszystkie do tej pory prezentowane przykłady, łączyły tabele w naturalny sposób ich powiązań. Po kolumnach będącymi jednocześnie kluczami obcymi/podstawowymi tabel.

Ogólną zasadą łączenia jest możliwość jej realizacji po dowolnych kolumnach. Musi być spełniony tylko jeden warunek – kompatybilność typów danych łączonych atrybutów. To jak zapiszemy warunek i czy będzie miał sens, zależy tylko od nas – język SQL nie ogranicza tu naszej wyobraźni.

Dodatkowo, na wartościach atrybutów po których łączymy, możemy wykonywać dowolne operacje. Przetwarzać je za pomocą funkcji skalarnych, wykonywać działania arytmetyczne, łączenia stringów etc…

Na koniec bardziej „zaawansowany” przykład, łączący różne techniki pisania zapytań prezentowane w tym rozdziale kursu. Będzie obrazował połączenie wewnętrzne typu SELF JOIN tabeli dbo.HIST w której przechowywane są informacje o przebiegu samochodów w postaci „logu”.

Scenariusz jest taki, że co miesiąc, każdy z pracowników, musi wpisać stan licznika swojego samochodu służbowego. Zapytanie ma za zadanie wyświetlenie raportu o przebiegach miesięcznych, każdego samochodu za okres wakacyjny. W tym celu posłużę się technikami wspólnych wyrażeń tablicowych, łączeniem wewnętrznym oraz funkcją ROW_NUMBER(). Zauważ, że wykonuję operacje arytmetyczne na atrybutach łączących (dodaje 1, aby uzyskać przesunięcie odczytów) oraz łączę kilka warunków w klauzuli ON.

WITH LogTab as (
   -- mozesz uruchomić testowo tylko zawartość CTE, 
   -- żeby sprawdzić co zwraca i co będzie sednem (tabela LogTab) której potem używam
   SELECT  * , ROW_NUMBER() OVER(partition by NrRej order by DtPomiaru) as IdUniqueRange  
   FROM  dbo.HIST
   where DtPomiaru between '2012-06-01' and '2012-08-31'
 
)
 
SELECT  l1.NrRej, YEAR(l1.DtPomiaru) as Rok,MONTH(l1.DtPomiaru) as Miesiac, 
	l1.Przebieg as PrzebiegStart,l2.Przebieg as PrzebiegEND,
        l1.Przebieg - l2.Przebieg as Delta
FROM  LogTab l1 INNER JOIN LogTab l2 
	   ON l1.IdUniqueRange = l2.IdUniqueRange+1 and l1.NrRej = l2.NrRej
ORDER BY l1.NrRej, l1.DtPomiaru

SELF_JOIN_02


Zgodność składni łączenia tabel ze standardami ANSI SQL

W praktyce można spotkać różne możliwości określania warunków złączeń.

Przedstawione w tym artykule (INNER, LEFT OUTER, CROSS, warunek ON) są zgodne ze standardem ANSI SQL:92 i powinny być stosowane w produkcyjnych bazach. Czysto informacyjnie, istnieją inne, starsze metody zapisu, które jednak nie powinny być już stosowane.

Łączenie wewnętrzne z warunkiem zamiast w ON – w WHERE.

-- równoważnik INNER JOIN - warunek połączenia dopiero w filtrowaniu w WHERE 
-- UNIKAĆ !!! bo łatwo o pomyłkę i cartesian product gotowy :)
SELECT e.Imie, e.Nazwisko, e.Stanowisko , ISNULL(c.Marka , '-') as Samochod
FROM dbo.EMP e , dbo.CAR c 
WHERE e.IdPrac = c.IdPrac