SELECT Explanation, Example FROM Pro.Knowledge
FacebookRSS

Funkcje użytkownika

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

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

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


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

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

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


Pisanie zapytań SQL z wykorzystaniem funkcji użytkownika

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

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

Funkcje_Uzytkownika_SQL

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

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

Funkcje od strony programistycznej

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

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

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

(1 row(s) affected)

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

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

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

(1 row(s) affected)

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

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

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

Funkcje, procedury i zmienne systemowe

Oprócz widoków systemowych, za pośrednictwem których, możesz dotrzeć do informacji (metadanych) o środowisku, mamy do dyspozycji szereg innych przydatnych obiektów.

W artykule tym, prezentuję kilka z nich. Podobnie jak widoki systemowe, ich różnorodność daleko wykracza poza zakres tego kursu. Warto mieć świadomość ich istnienia – potraktuj ten rozdział jako wstęp do obiektów, które dają wiedzę na temat środowiska.

Obiekty te, zazwyczaj wykorzystywane są przez administratorów baz danych (DBA DataBase Administrator), ale dla zwykłych użytkowników bywają również pomocne. Wszystkie informacje do których możemy za ich pośrednictwem dotrzeć, dostępne są również poprzez opisane wcześniej widoki systemowe.


Skalarne funkcje systemowe

Systemowe funkcje wbudowane zwracające metadane, stosujemy zazwyczaj w procedurach utrzymaniowych, np. do identyfikacji obiektów, ich parametrów czy wykonywania na nich działań administracyjnych.

Funkcje OBJECT_ID() oraz OBJECT_NAME() pozwalają pójść na skróty w odpytywaniu widoków systemowych.
Zwracają identyfikator obiektu lub jego nazwę. Bywają przydatne, szczególnie jeśli na szybko, chcemy wyciągnąć jakieś dane, a nie znamy identyfikatora danego obiektu i nie chce nam się łączyć dodatkowych widoków.

Dla przykładu – chcemy na szybko wyświetlić informacje o wszystkich indeksach w tabeli dbo.Orders. W skrótowej wersji, moglibyśmy zapisać zapytanie w taki sposób :

USE Northwind
GO
 
SELECT OBJECT_NAME(object_ID) AS TableName, * 
FROM sys.indexes
WHERE OBJECT_NAME(object_ID) = 'Orders'

Metadane_01

Co prawda od razu muszę tutaj przestrzec, że nie do końca jest to sposób idealny. Jak to bywa ze skrótami, czasem mogą nas wyprowadzić w pole.

Zauważ, że wyszukałem wszystkie indeksy dla tabeli Orders, bez podania nazwy schematu. W sytuacji, w której mielibyśmy dwie tabele o tych samych nazwach (tyle że w różnych schematach) wynik byłby mylący, bo zwrócone rekordy dotyczyły by wszystkich indeksów w obu tabelach. Warto o tym pamiętać.

Funkcje DB_ID() oraz DB_NAME(), analogicznie jak w przypadku poprzednio prezentowanych funkcji, zwracają translacje nazwy na identyfikator lub odwrotnie. Samo wywołanie, bez podania parametru, zwróci aktualną nazwę / identyfikator bazy danych do której jesteś podłączony.

SELECT		DB_ID() as ID_bazy, 
		DB_NAME() as Nazwa_bazy, 
		DB_NAME(1) as NazwaBazyId1

Metadane_02

Funkcje sprawdzające, aktualne parametry bazy danych DATABASEPROPERTEX() oraz serwera SERVERPROPERTY(). Wywołując je z odpowiednimi argumentami, możemy otrzymać informacje, np. na temat sposobu porównywania wartości tekstowych (COLLATION).

SELECT DATABASEPROPERTYEX ( 'Northwind', 'Collation' ) as DBCollation,
	   SERVERPROPERTY ('Collation' ) as SrvCollartion

Metadane_03


Zmienne @@GLOBALNE

Zmienne globalne to bardzo podobne do funkcji skalarnych obiekty. Ich nazwa zaczyna się od @@ i jest ich raptem kilkadziesiąt. Poniżej przykład informacji o środowisku zwracanych przez tego typu obiekty :

SELECT	@@SERVICENAME as InstanceName, 
	@@VERSION as SQLServerVersion

Oprócz zmiennych typowo informacyjnych o środowisku, mamy kilka wykorzystywanych w logice programistycznej. Poniżej przykład wykorzystania @@TRANCOUNT, aby określić liczbę otwartych transakcji bieżącego połączenia.

SELECT @@TRANCOUNT as LiczbaOtwartychTransakcji
 
BEGIN TRANSACTION  --nowa transakcja
 
SELECT @@TRANCOUNT as LiczbaOtwartychTransakcji

Metadane_04Metadane_05


Procedury systemowe

Na koniec bardzo użyteczne źródło informacji dla każdego DBA dotyczące różnych aspektów baz, serwera czy aktywności użytkowników. Procedury systemowe to nic innego jak kompilacja widoków, mająca na celu w zwięzły i przystępny sposób, prezentować najważniejsze informacje.

Zamiast pisać kwerendy łączące wiele widoków systemowych, możemy skorzystać z istniejących procedur, wywołując je po prostu po nazwie.

Poniżej kilka dwa przykłady wyświetlania informacje o bazie Northwind :

exec sp_helpdb 'Northwind';
 
exec sp_spaceused;

Metadane_06
Jak widać forma – typowo dla administratorów. Każda z powyższych procedur zwraca osobne rekordsety. Procedura sp_spaceused, może być wywołana również dla konkretnego obiektu (tabeli) w bazie danych. Zwróci nam informacje o liczbie rekordów i miejscu alokowanym przez dany obiekt.

exec sp_spaceused 'dbo.Orders'

Metadane_07

Jedną z fajniejszych rzeczy, jest możliwość modyfikacji lub podglądu zawartości istniejących, wbudowanych procedur systemowych. W ten sposób możesz odkryć strukturę widoków systemowych. Definicje procedur, funkcji systemowych znajdziesz w bazie master.

Szczegółowe omówienie tych struktur, prezentuję na kursach z programowania i administracji SQL Server na które zapraszam.

Logiczne przetwarzanie zapytań SQL

Naukę http://www.dreamstime.com/stock-image-handshake-abstract-cogwheels-blackboard-green-blackboard-hand-image44667161języka SQL w zakresie pisania zapytań, powinieneś rozpocząć właśnie od tego rozdziału. Zrozumienie procesu przetwarzania kwerendy jest niezbędne, aby w pełni świadomie tworzyć polecenia SQL i móc wykorzystać ich możliwości.

W artykule tym, znajdziesz informacje o kolejności kroków przetwarzania zapytań.

Analizując ten proces, nie skupiaj się proszę na szczegółach i możliwościach konkretnych poleceń. Chciałbym abyś uchwycił tu ogólne zasady, w jaki sposób wykonywane są kwerendy. Temat ten, z uwagi na zakres materiału podzieliłem na dwie części. W tej, omawiam pełen proces na prostym przykładzie zapytania do pojedynczej tabeli.

Druga część wymaga znajomości zasad łączenia tabel i umieściłem ją zaraz po rozdziale opisującym te zagadnienia. Wyjaśniam w niej aspekty kolejności łączenia tabel.


Kolejność kroków przetwarzania zapytań

Wykonywanie każdej kwerendy przez silnik bazodanowy odbywa się krokowo, w ściśle określonej kolejności. Reguły te są spójne dla wszystkich relacyjnych baz danych.

W konstrukcji zapytań możemy wyszczególnić 6 głównych bloków logicznych.

(Step 5)   SELECT   -- określanie kształtu wyniku, selekcja pionowa (kolumn)
(Step 1)   FROM     -- określenie źródła (źródeł) i relacji między nimi
(Step 2)   WHERE    -- filtracja rekordów
(Step 3)   GROUP BY -- grupowanie rekordów
(Step 4)   HAVING   -- filtrowanie grup
(Step 6)   ORDER BY -- sortowanie wyniku

W ramach każdego z nich, możemy umieszczać całkiem złożone konstrukcje. Podzapytania, zapytania skorelowane, dodatkowe polecenia, takie jak operator DISTINCT czy TOP w SELECT. Poszczególne klauzule i zakres ich możliwości omawiam szczegółowo w kolejnych rozdziałach tego kursu.

Najprostsze polecenie SQL może składać się z samego SELECTa np. :

SELECT 'Hello World!'

Takie „zapytania” rzadko kiedy nas interesują i tak naprawdę trudno nawet nazwać je zapytaniami. Zazwyczaj chcemy pobierać dane z jakiegoś źródła np. tabeli czy widoku. Do ich określenia, służy klauzula FROM. Ustalmy więc, że kwerenda to konstrukcja składająca się przynajmniej z bloków SELECT oraz FROM.

Poza nimi, wszystkie pozostałe są opcjonalne. Warto jednak zauważyć, że stosowanie HAVING bez GROUP BY również za bardzo nie będzie miało sensu. Z punktu widzenia samej konstrukcji jest jednak możliwe.

Wynikiem przetwarzania każdego z kroków jest tabela wirtualna (VT) , będąca jednocześnie obiektem wejściowym kolejnego etapu. Do tabel wirtualnych, czyli produktów pośrednich kroków przetwarzania, nie mamy dostępu z zewnątrz. Są one logicznymi strukturami, które rozpatrujemy tylko w kontekście analizy teoretycznej.

Trzeba podkreślić, że faktyczny sposób realizacji zapytań, jest wykonywany za pomocą efektywnych i zoptymalizowanych przez silnik bazodanowy metod. W efekcie końcowym, pozwalają one na osiągnięcie dokładnie tego samego wyniku co w analizie teoretycznej. Podkreślam to, bo szczególnie opis iloczynów kartezjańskich czy konieczność czytania całej zawartości tabel może słusznie budzić w Tobie niedowierzanie czy wewnętrzny sprzeciw :)

W analizie teoretycznej tak właśnie się dzieje, aby osiągnąć określony wynik. W praktyce, silnik bazodanowy zna skróty, które doprowadzają dokładnie do tego samego celu – znacznie szybciej.


Analiza przetwarzania kwerendy na przykładzie

Cały proces najlepiej prześledzić na przykładzie. Na początek coś prostego – zapytanie pobierające dane tylko z jednej tabeli dbo.Pracownicy. Zawartość tego zbioru jest następująca :

IdPrac Nazwisko        Placa                 Zespol     Dzial
------ --------------- --------------------- ---------- ----------
1      Kasprzak        3000,00               DBA        IT
2      Norwid          2200,00               DBA        IT
3      Walewska        4000,00               Security   IT
4      Nowak           1300,00               Detal      Sprzedaz
5      Piotrowska      2300,00               Hurt       Sprzedaz
6      Lewandowski     3300,00               DBA        IT
7      Tusk            1100,00               HelpDesk   IT
8      Podemski        9500,00               Hurt       Sprzedaz
9      Gmoch           1750,00               Security   IT
10     Walendziak      1750,00               NULL       NULL

(10 row(s) affected)

W naszym scenariuszu, będziemy chcieli wyświetlić informacje o liczbie pracowników, pracujących w zespołach wieloosobowych (więcej niż jeden pracownik) w ramach Działu IT. Końcowy wynik posortujemy rosnąco po liczbie pracowników.

Kwerenda realizująca to zadanie będzie wyglądała następująco :

SELECT Zespol , COUNT( IdPrac ) AS LiczbaPracowników
FROM dbo.Pracownicy 
WHERE Dzial = 'IT'
GROUP BY Zespol
HAVING COUNT ( IdPrac ) > 1 
ORDER BY LiczbaPracowników

Zapytanie to wykorzystuje wszystkie bloki logiczne z jakich może składać się dowolna kwerenda. Na razie celowo nie omawiam logiki łączenia tabel (pobieramy dane tylko z jednego zbioru), aby lepiej przedstawić generalne zasady.

Krok 1 : FROM – określenie źródła (źródeł) i relacji między nimi

Na początku trzeba określić skąd będziemy czerpać dane. Dlatego pierwszym krokiem jest klauzula FROM. W naszym przykładzie zbiorem wejściowym jest jedna tabela – dbo.Pracownicy. Wynikiem przetwarzania pierwszego kroku będzie zatem tabela wirtualna VT1, zawierającą całą (tak, CAŁĄ !) zawartość tego zbioru.

W tym momencie powinna zapalić się u Ciebie lampka ostrzegawcza. Jak to? Czy faktycznie tak jest, że jak odpytuję tabelę zawierającą 100 milionów rekordów to wszystkie te elementy są czytane i przekazywane do jakiejś VT1? Teoretycznie, zgodnie z zasadami logicznego przetwarzania – tak właśnie się dzieje.

Tylko teoretycznie. Jak już wspominałem, w praktyce silnik serwera zna efektywne sposoby dostępu do danych. Z pewnością jeśli nie są potrzebne wszystkie dane tabeli w kolejnych krokach, to nie będzie do nich sięgał.

Zawartość tabeli VT1 będzie więc wyglądała identycznie jak tabeli dbo.Pracownicy :

SELECT Zespol , COUNT( IdPrac ) AS LiczbaPracowników
FROM dbo.Pracownicy                                    -- wynik VT1WHERE Dzial = 'IT'
GROUP BY Zespol
HAVING COUNT ( IdPrac ) > 1 
ORDER BY LiczbaPracowników
VT1

VT1

Krok 2 : WHERE – filtrowanie rekordów

Krok ten, działa na wyniku poprzedniego. Czyli w naszym wypadku będzie operował na tabeli VT1. Może to się wydawać oczywiste, ale świadomie tu podkreślę, że filtrować (wyszukiwać) możemy rekordy tylko takie, które są obecne w tabeli VT1.

W WHERE, dla każdego wiersza wyznaczany jest wynik logiczny zastosowanych wyrażeń (warunków). W naszym przykładzie stawiamy tylko jeden warunek. Interesują nas elementy zbioru VT1, dla których wartość kolumny Dział jest równa ciągowi znaków ‚IT’.

SELECT Zespol , COUNT( IdPrac ) AS LiczbaPracowników
FROM dbo.Pracownicy 
WHERE Dzial = 'IT'                                     -- wynik VT2GROUP BY Zespol
HAVING COUNT ( IdPrac ) > 1 
ORDER BY LiczbaPracowników

Do tabeli wynikowej VT2 – czyli rezultatu przetwarzania kroku WHERE, zostaną wstawione tylko takie rekordy, dla których wynik postawionych warunków (wyrażeń) będzie TRUE.

Przyjrzyjmy się zatem, jakie wyniki logiczne naszego wyrażenia, przyjmą kolejne rekordy.

Wynik logiczny wyrażeń w WHERE

Wynik logiczny wyrażeń określonych w WHERE

Relacyjne systemy baz danych, w tym także SQL Server, bazują na logice trójwartościowej. Jak sama nazwa wskazuje, wynik porównań może przyjmować trzy wartości – TRUE, FALSE lub UNKNOWN. TRUE oraz FALSE nie trzeba tłumaczyć – albo jest coś równe czemuś, albo nie.

Wartość UNKNKOWN, czyli nieznana została wyznaczona dla ostatniego (10) rekordu. Jest ona bezpośrednio związana z koncepcją wartości nieokreślonych – NULL. Jakiekolwiek porównanie, czy operacje z NULLem, skutkują zawsze wartością nieznaną – UNKNOWN. Szczegółowe wyjaśnienie tego zagadnienia, znajdziesz w artykule poświęconym NULL.

Podsumowując, do kolejnego kroku, zostanie przekazana tabela VT2. Zawierać będzie tylko 6 rekordów – te dla których wynik wyrażeń wynosi TRUE.

VT2 - wynik filtrowania WHERE

VT2 – wynik filtrowania WHERE

Krok 3 – GROUP BY – grupowanie rekordów

Podobnie jak filtrowanie, GROUP BY jest blokiem opcjonalnym. W tym kroku tworzone są grupy rekordów. Definicję grupy tworzą kolumny (atrybuty) grupujące, wyszczególnione w GROUP BY.

W naszym prostym przykładzie, potrzebujemy znać informacje o liczbie pracowników w ramach zespołów. Grupować będziemy po kolumnie Zespol.

SELECT Zespol , COUNT( IdPrac ) AS LiczbaPracowników
FROM dbo.Pracownicy 
WHERE Dzial = 'IT'
GROUP BY Zespol                                        -- wynik VT3HAVING COUNT ( IdPrac ) > 1 
ORDER BY LiczbaPracowników

Każdy element tabeli wejściowej, czyli wynik pochodzący z kroku WHERE – VT2, może znaleźć się tylko w jednej grupie. Utworzonych zostanie tyle grup, ile jest unikalnych wartości w kombinacjach wszystkich kolumn grupujących. W naszym przypadku utworzone zostaną 3 grupy rekordów, reprezentujące zespoły DBA, HelpDesk oraz Security :

GROUP BY - tworzenie grup rekordów

GROUP BY – tworzenie grup rekordów

Ten krok przetwarzania jest szczególny, gdyż jego istnienie wprowadza bardzo ważne konsekwencje we wszystkich kolejnych krokach. Każda grupa, reprezentowana będzie przez jeden rekord tabeli wynikowej VT3, określony wartościami kolumn grupujących.

Sednem działania tego kroku, jest utworzone dwóch sekcji danych. Sekcji grupującej – opisanej przez kolumny, według których grupujemy oraz sekcji danych surowych. Ta z kolei, zawiera wszystkie pozostałe atrybuty (kolumny) nie występujące w klauzuli GROUP BY.

GROUP BY - sekcja grupująca i danych surowych

GROUP BY – sekcja grupująca i danych surowych

Od tego momentu, bezpośrednio odwoływać się można tylko do kolumn sekcji grupującej. To one tworzą elementy tabeli VT3 – są po prostu ich atrybutami.

Tabela VT3 zawierać będzie 3 elementy (rekordy), opisane atrybutem Zespol. Z każdym z tych rekordów będzie skojarzona sekcja danych surowych, tworzona przez wszystkie pozostałe kolumny. Do kolumn sekcji danych surowych, możemy odwoływać się w kolejnych krokach, tylko poprzez funkcje agregujące – np. COUNT().

Jest to całkiem logiczne. Zastanów się, jaką wartość kolumny Nazwisko, silnik bazodanowy miałby zwrócić w reprezentacji relacyjnej dla rekordu grupy DBA. Pierwsze? Ostatnie? A może losowe? Nic z tych rzeczy. SQL bazuje na matematycznej teorii zbiorów i nie ma w niej miejsca na przypadek. Wszystko musi odbywać się wedle ściśle określonych zasad.

W wyniku działania GROUP BY, otrzymamy tabelę VT3 zawierającą 3 rekordy. Każdy z nich, reprezentuje grupę. Z każdą z grup, skojarzona jest sekcja surowa. Celem lepszego zobrazowania, poniżej przedstawiam zawartość tabeli VT3. Dodatkowe trzy kolumny, pokazują wynik działania funkcji agregujących.

COUNT(idPrac) – zlicza wystąpienia wartości idPrac, wszystkich rekordów w ramach sekcji danych surowych. MAX i MIN zwracają największą i najmniejszą wartość w kolumnie Nazwisko.

SELECT Zespol , COUNT( IdPrac ) as COUNT_Raw_Data_Records, 
	MIN( Nazwisko ) MIN_Nazwisko, MAX( Nazwisko ) MAX_Nazwisko 
FROM dbo.Pracownicy
WHERE Dzial='IT'
GROUP BY Zespol
VT3 + dodatkowe kolumny - wynik funkcji agregujących

VT3 + dodatkowe kolumny – wynik funkcji agregujących

Krok 4 – HAVING – filtrowanie grup

HAVING jest bezpośrednio związany z operacjami na grupach. Jest więc logicznym, że ten krok przetwarzania nastąpi po etapie tworzenia grup czyli po GROUP BY. Ogólne zasady filtrowania są identyczne jak w WHERE. Do tabeli wynikowej VT4, trafią tylko takie rekordy (grupy), dla których wynik wyrażeń logicznych będzie TRUE.

Budując warunki filtracji grup rekordów, trzeba pamiętać o konsekwencji grupowania. Bezpośrednio odwoływać możemy się tylko do kolumn sekcji grupującej, do pozostałych za pośrednictwem funkcji agregujących.

W naszym scenariuszu, interesuje nas liczba elementów w sekcji danych surowych w obrębie grupy. Czyli z ilu pracowników składa się każda grupa. Wynik logiczny określony w HAVING, spełniają dwa rekordy i tylko te dwie grupy znajdą się w tabeli wynikowej VT4.

SELECT Zespol , COUNT( IdPrac ) AS LiczbaPracowników
FROM dbo.Pracownicy 
WHERE Dzial = 'IT'
GROUP BY Zespol
HAVING COUNT ( IdPrac ) > 1                            -- wynik VT4ORDER BY LiczbaPracowników
HAVING - filtrowanie grup

HAVING – filtrowanie grup

Krok 5 – SELECT – selekcja pionowa i kształtowanie wyniku

SELECT odpowiada za ostateczny kształt zbioru wynikowego czyli prezentowanie wyników. Możemy dokonać wyboru interesujących nas kolumn (selekcja pionowa), przekształceń (np. dodać do siebie wartości dwóch kolumn) czy wywoływać funkcje skalarne.

Pełne możliwości tego kroku opisuje w rozdziale poświęconym SELECT. Warto wspomnieć o dwóch dodatkowych operatorach, które mogą się w SELECT pojawić. DISTINCT – czyli usuwanie duplikatów oraz TOP – ograniczenie wyników.

Z punktu widzenia kolejności wykonywanych kroków, najpierw wykonywane są wszelkie działania mające na celu ostateczne określenie wartości kolumn (np. złączenia stringów czy obliczenia matematyczne). Następnie, jeśli jest stosowane polecenie DISTINCT – usuwane są wszystkie duplikaty. Polecenie TOP – wykonywane jest na samym końcu przetwarzania kwerendy, czyli po sortowaniu.

Wynik działania naszej kwerendy – kroku SELECT – czyli zawartość VT5 będzie następująca :

SELECT Zespol , COUNT( IdPrac ) AS LiczbaPracowników   -- wynik VT5FROM dbo.Pracownicy 
WHERE Dzial = 'IT'
GROUP BY Zespol
HAVING COUNT ( IdPrac ) > 1 
ORDER BY LiczbaPracowników
VT5 - wynik SELECT

VT5 – wynik SELECT

Jeśli nadajesz tutaj aliasy (alternatywne nazwy kolumn), widoczne one będą tylko w ostatnim kroku – ORDER BY. Przecież te nazwy, to nic innego jak atrybuty tabeli VT5 – czyli produktu przetwarzania SELECT. Taki zapis będzie więc nieprawidłowy :

-- alias LiczbaPracownikow, znany będzie dopiero w kroku
-- następujących po SELECT czyli w ORDER BY
SELECT z.Nazwa as Zespol , COUNT(*) as LiczbaPracownikow,  LiczbaPracownikow + 1
......

Krok 6 : ORDER BY – sortowanie wyniku

Sortujemy oczywiście efekt kroku SELECT, czyli tabelę VT5. Tylko tutaj w kwerendzie, możemy odwoływać się do zastosowanych w SELECT aliasów nazw kolumn.

SELECT Zespol , COUNT( IdPrac ) AS LiczbaPracowników
FROM dbo.Pracownicy 
WHERE Dzial = 'IT'
GROUP BY Zespol
HAVING COUNT ( IdPrac ) > 1 
ORDER BY LiczbaPracowników

W tym kroku otrzymujemy finalny rezultat przetwarzania kwerendy – posortowany w określony sposób zbiór elementów (domyślnie rosnąco ASCending).

Finalny rezultat kwerendy

Finalny rezultat kwerendy

Wyjątkiem jest stosowanie w SELECT polecenia TOP – ograniczające ilość zwracanych rekordów. Wtedy, wykonywana jest dodatkowa filtracja ze względu na istnienie tego polecenia.

Jednym z ciekawszych możliwości ORDER BY, jest wprowadzona w dialekt T-SQL (SQL Server 2012) możliwość stronnicowania wyników – opisuję to w artykule poświęconym ORDER BY.


Podsumowanie

W SQL Server dostępnych jest szereg rozszerzeń funkcjonalnych pisania kwerend. Na przykład elementy składniowe stosowane do tworzenia i przeszukiwania dokumentów XML (FOR XML, XQuery, OPENXML) czy tabel przestawnych (PIVOT, UNPIVOT).

Nie stanowią one jednak fundamentu logiki przetwarzania zapytań.

Opisany powyżej proces, ma zastosowanie do wszystkich typowych zapytań. Oczywiście można je mocno skomplikować, stosując np. zapytania skorelowane czy podzapytania. Jednak ich przetwarzanie i tak odbywa się zgodnie z opisanym powyżej sposobem.

Kolejnym krokiem w poznawaniu SQL, jest wiedza na temat wymienionych tu bloków funkcjonalnych oraz zasad łączenia tabel. Opisuję te zagadnienia w kolejnych rozdziałach tego kursu.

Widoki systemowe w SQL Server

Jednym z najbardziej użytecznych źródeł informacji dotyczących struktur i właściwości obiektów (metadane) stworzonych w bazach SQL Server są widoki systemowe. Przeciętni użytkownicy rzadko kiedy korzystają z ich możliwości. Być może jest to związane z liczbą widoków, różnymi typami i koniecznością poznania ich struktur. A może po prostu nie mają świadomości ich istnienia lub nie są im do niczego potrzebne?

Ilość widoków, z których możemy czerpać wiedzę jest z każdą wersją SQL Server większa. W końcu przybywa samych funkcjonalności a więc i wiedzy na temat potencjalnych obiektów i struktur.

Pełną mapę dostępnych widoków systemowych, relacji między nimi dla SQL Server 2008 R2 możesz znaleźć tutaj.

W artykule tym, znajdziesz ogólne informacje na temat typów widoków systemowych. Zaprezentuje, też kilka przykładów, które mogą okazać się pomocne w oswojeniu tego niezmiernie wartościowego źródła wiedzy, nie tylko dla programistów czy administratorów.


Rodzaje widoków systemowych w SQL Server

W środowisku SQL Server, możemy korzystać z kilku typów widoków systemowych. Jak to zazwyczaj bywa, jest wiele dróg do osiągnięcia tego samego celu.

Ogólne widoki systemowe

To podstawowe struktury, dzięki którym mamy bezpośredni dostęp do wiedzy na temat wszystkich obiektów w bazach danych / serwerze (metadanych). Zawierają się w schemacie sys. Co ważne, są one bardzo dobrze udokumentowane w BOL / MSDN. Ich liczba jest dość duża, dostęp do nich znajdziesz w każdej bazie, nawigująć np w Management Studio do Views > System Views. Fragment (akurat widoków ze INFORMATION_SCHEMA) poniżej :
system_views_00
W zależności od poziomu uprawnień, możesz mieć dostęp do cześć z nich lub nawet tylko do pewnego zakresu danych w ich ramach.

Jednym z przykładowych zastosowań jest poznawanie struktury bazy danych. Ten proces, może być dość uciążliwy, szczególnie wtedy, gdy nie mamy dostępu do dokumentacji czy diagramów relacji.

Zdarzają się sytuacje, w których potencjalnie jesteśmy w stanie odgadnąć lub wręcz znamy nazwę jakiejś kolumny, ale nie wiemy w jakiej tabeli może być ona przechowywana. Tego typu przypadków jest wiele (poznawanie relacji między tabelami bez kluczy / z kluczami etc).

Wyobraźmy sobie, bazę zawierającą setki tabel. Chcemy znaleźć tabele w których przechowywane są informacje np. o cenach. Informacje o strukturze każdego obiektu typu tabela, możemy wyciągnąć z dwóch widoków systemowych – sys.columns oraz sys.tables.

USE NorthWind
Go
-- jak znaleźć nazwę tabeli, zawierającą określoną nazwę kolumny
select t.name as TabName, c.name as ColName
from sys.columns c 
	inner join sys.tables t on c.object_id=t.object_id
where c.name like '%price%'

System_Views_01
Sposób ten można jeszcze uprościć, uzyskując identyczny efekt z wykorzystaniem funkcji systemowej OBJECT_NAME.

select  OBJECT_NAME(c.object_id) as TabName,c.name as ColName 
from sys.columns c 
where c.name like '%price%'

Widoki schematu informacyjnego (INFORMATION_SCHEMA)

Jest to ustandaryzowany (ISO) podzbiór bardzo ogólnych widoków systemowych, znajdujących się w schemacie INFORMATION_SCHEMA. Za ich pośrednictwem mamy dostęp do wycinka danych, do których moglibyśmy dotrzeć przez ogólne widoki systemowe. Ilość i zakres danych do których możemy dotrzeć za ich pomocą, jest jednak znacznie uboższa.

Głównym powodem ich istnienia jest próba ułatwienia pracy administratorów baz danych i aplikacji korzystających z różnych systemów. W zamyśle mają ułatwiać odnalezienie się w nieznanym środowisku. Jednak nie jest to sugerowany sposób korzystania z metadanych. Lepiej użyć standardowych widoków systemowych . Są one znacznie bogatsze jeśli chodzi o ilość informacji na temat struktur. Ponadto zapewniają bezpośredni dostęp do metadanych (najbardziej efektywny).

Mały przykład – bardzo ogólne informacje na temat wybranych tabel w bazie Northwind

select * from INFORMATION_SCHEMA.TABLES
where TABLE_NAME like 'C%'

INFORMATION_SCHEMA
Zauważ, że ten widok, to tak naprawdę pochodna, podzbiór danych z podstawowych widoków systemowych : sys.tables oraz sys.views. Praktycznie ten sam rezultat możesz otrzymać w ten sposób :

select DB_NAME() as TABLE_CATALOG,
	SCHEMA_NAME() as TABLE_SCHEMA,
	NAME as TABLE_NAME, 
	type_desc as TABLE_TYPE
from sys.tables
where NAME like 'C%'
UNION
select DB_NAME() as TABLE_CATALOG,
	SCHEMA_NAME() as TABLE_SCHEMA,
	NAME as TABLE_NAME, 
	type_desc as TABLE_TYPE 
from sys.views
where NAME like 'C%'

System_Views_02
Oczywiście to tylko przykład, mający na celu demonstrację, że do tego samego rozwiązania można dotrzeć na różne sposoby.

Widoki zgodności (Compatibility Views)

Są to widoki znane z wersji SQL Server 2000. Dostępne w kolejnych wersjach SQL Server, głównie aby ułatwić migracje / kompatybilność wstecz. Ich stosowanie w nowych, komercyjnych systemach nie jest zalecane. Nie zmienia to faktu, że dla wielu są wciąż cenne i z niektórymi (również mi) trudno jest się rozstać (np. sys.sysprocesses). Łatwo je rozpoznać po nazwie. Są umieszczone w schemacie sys, a ich nazwa również zaczyna się od sys. Kilka przykładów, pokrewnych widoków „prawie” (robi różnicę) analogicznych. Fragmenty wyników zapytań z widoków :

--old one, SQL Server 2000 - compatibility view
select * from sys.sysdatabases

System_Views_03

select * from sys.databases

System_Views_04
Inne przykładowe, analogiczne widoki systemowe vs widoki zgodności :

select * from sys.sysobjects --old
select * from sys.objects
 
select * from sys.syscomments --old
select * from sys.sql_modules
select * from sys.check_constraints

Korzystanie z nich inaczej niż tylko jako źródło wiedzy AdHoc (pojedyncze zapytania) mija się z celem bo mają być one w przyszłości usunięte.
Pełną informację o relacjach pomiędzy widokami zgodności a obecnymi widokami systemowymi znajdziesz tutaj.

Dynamiczne widoki DMV (Dynamic Management Views)

Wprowadzone w SQL Server 2005, są nieocenionym źródłem informacji o procesach, licznikach, wiedzy związanej z wydajnością, stanem bazy, obiektów i samej instancji SQL Servera. Dzięki nim możemy znaleźć wąskie gardła systemu, wykryć nieprawidłowości procesów, statystyki i generalnie dowiedzieć się wszystkiego o stanie SQL Server.

Pogrupowane są ze względu na obiekty i zastosowania którego dotyczą.
Znajdziesz na przykład szereg widoków dynamicznych związanych z operacjami IO (grupa sys.dm_io), systemie operacyjnym SQLOS (grupa sys.dm_os) czy z wykonywaniem zapytań \ zadań (sys.dm_exec). Wiele z nich występuje w postaci funkcji tabelarycznych.

Widoki dynamiczne to także świetne źródło wiedzy na temat statystyk, czy stanu obiektów w bazie np. fragmentacji indeksów.

USE Northwind
GO
 
-- Informacje o stopniu fragmentacji indeksu w tabeli dbo.Orders
 
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'dbo.Orders'),
     NULL, NULL, NULL) AS a
    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO

DMV_fragmentation

Pozostałe użyteczne funkcje skalarne SQL

W SQL Server, oprócz opisanych w poprzednich artykułach, funkcjach daty i czasu, tekstowych czy matematycznych, istnieje wiele innych kategorii. Związane mogą być one z rozszerzeniami analitycznymi SQL (funkcje analityczne w SQL), bezpieczeństwem czy wyciąganiem informacji o strukturach i środowisku (metadane).

Opisywanie ich wszystkich w ramach kursu SQL dla początkujących mija się z celem. Chciałbym zwrócić Twoją uwagę na kilka dodatkowych, służących weryfikacji typu danych.

Dzięki nim możesz dokonywać podstawowego sprawdzenia typu danych, np. podczas importu z zewnętrznych źródeł (plików, innych baz danych).


Pierwszą z nich jest funkcja ISDATE ( atrybut ) – zwraca true (1) – jeśli wartość jest datą lub istnieje możliwość niejawnej konwersji na datę. Jej działanie obrazuje poniższy przykład.

-- tylko dwa ostatnie przypadki Exp5 i 6 nie są datami
-- choć obie mogą nimi być np. Exp 5 YYYYDDMM
 
SELECT ISDATE( getdate() ) as Exp1,
       ISDATE( '2013-01-02' ) as Exp2,
       ISDATE( '20130102' ) as Exp3,
       ISDATE( '2013/01/02' ) as Exp4,
       ISDATE( '20131402' ) as Exp5,
       ISDATE( '2013 01 02' ) as Exp6

FN_ISDATE
Używać jej możesz zarówno w SELECT jak i w warunkach WHERE (żeby wyfiltrować tylko takie rekordy, które mają wpisaną datę a nie jakieś inne ciągi znakowe).


Drugą funkcją sprawdzania typu danych jest ISNUMERIC ( atrybut ) – działa analogicznie jak poprzednia. Zzwraca wartość true, jeśli badany atrybut (stała, zmienna, wartość danej kolumny), jest liczbą lub może zostać niejawnie przekonwertowana na liczbę.

-- jeśli jest możliwa niejawna konwersja na dowolny typ liczbowy
-- ISNUMERIC zwróci wartość true
 
SELECT ISNUMERIC('133') as Exp1,
       ISNUMERIC('133 ') as Exp2,
       ISNUMERIC('133.1') as Exp3,
       '133' + 123 as Exp4,
       ' 133 ' + 123.32 as Exp5,
       ' 133.1' - 132.1 as Exp6

FN_ISNUMERIC_01
Zauważ, że niektóre z tych stringów, nie do końca są poprawnymi liczbami (spacje). Jeśli jest to możliwe, silnik bazy danych wykona na nich niejawną konwersję typu na liczbowy, dlatego możliwe jest wykonanie na nich operacji matematycznych bez użycia CAST / CONVERT.

Z tą funkcją wiążą się jednak pewne zagrożenia. Jej działanie jest rozszerzone także na wartości „monetarne”. Akceptuje pewne znaki, których później nie można niejawnie przekonwertować np. na postać float, decimal czy int. Zwraca true również dla wartości zawierających takie symbole jak , . $ E + – etc..

SELECT ISNUMERIC ( '-,') as Result

FN_ISNUMERIC_02
Skoro jest to liczba, to dlaczego nie można zrobić tak :

SELECT '-,' + 10
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '-,' to data type int.

Z drugiej strony, możemy najpierw przekonwertować naszą „problematyczną” liczbę na wartość typu money / smallmoney a dopiero następnie wykonywać na niej operacje matematyczne. To zadziała.

SELECT CAST('-,' as smallmoney) + 10 as Result

FN_ISNUMERIC_03


Na koniec, jeszcze jedna funkcja DATALENGTH ( atrybut ), która zwraca informację o faktycznej liczbie bajtów, ile zajmuje dana wartość.

Możemy przekonać się, jaka jest różnica pomiędzy ciągiem znaków zapisanych standardowo na 8 bitach (1bajt na każdy znak) versus kodowanie w UNICODE (stąd przedrostek nvarchar, nchar oraz N przed stringami) na dwóch bajtach, dla każdego znaku. Zauważ, że funkcja LEN() zwraca informację o liczbie znaków (nie bajtów).

SELECT  DATALENGTH(  'Ala ma kota' ) , 
	DATALENGTH( N'Ala ma kota' ) as DataLen,
	LEN (  'Ala ma kota' ) as ASCIILen, 
	LEN ( N'Ala ma kota' ) as UNICODELen

FN_DATALENGTH