www.CentrumXP.pl
Kurs SQL
(MS SQL Server 2005)
Oryginalnie zamieszczony w serwisie
CentrumXP
y
ródło oryginału:
http://www.centrumxp.pl/dotNet/21,1,kategoria,Kurs_SQL.aspx
Autor: Grzegorz Chuchra
Opublikowano: 30 listopada 2005
Autor: Grzegorz Chuchra Strona 1
www.CentrumXP.pl
Spis treści:
01. SQL Server 2005 Express - Wstęp do kursu........................................................... 3
02. Instalacja. Opis elementów wchodzących w skład SQL Server 2005. ................... 6
03. SQL Server - instalacja krok po kroku................................................................... 8
04. Pierwszy kontakt. Czyli czym jest baza danych i jak z niej korzystać. ............... 18
05. Wstęp do relacyjnych baz danych. Sposób organizacji danych danych.............. 22
06. Klucze główne i obce............................................................................................... 27
07. Normalizacja baz danych....................................................................................... 30
08. Co to jest typ danych? Jakie typy danych dostarcza nam SQL Server 2005. ..... 34
09. Z teorii do praktyki czyli "Data Definition Language"....................................... 38
10. Operacje na danych "Data Manipulation Language". ........................................ 45
11. Filtracja danych...................................................................................................... 52
12. Funkcje agregujące ................................................................................................ 56
13. A
ączenie tabel ......................................................................................................... 61
14. Praca z podzapytańami, tabelami tymczasowymi i zmiennymi tabelarycznymi 64
15. Nowości w "Data Control Language", czyli parę słów o schematach................. 68
16. Transakcje .............................................................................................................. 71
17. Nowości w SQL Server 2005.................................................................................. 76
18. Procedury składowalne i funkcje bazodanowe..................................................... 80
19. XML i XQuery........................................................................................................ 85
20. Użycie .NET CLR w SQL Server 2005.................................................................. 90
Autor: Grzegorz Chuchra Strona 2
www.CentrumXP.pl
01. SQL Server 2005 Express - Wstęp do
kursu
Czytając ten artykuł zapewne zastanawiacie się, czy ten kurs wniesie coś nowego do waszego
bazodanowego życia. Zarówno jeśli jesteście początkującymi programistami, jak i tymi bardziej
zaawansowanymi, znajdziecie tu coś dla siebie. Pierwsza część kursu skupia się na
podstawach relacyjnych baz danych, przygotowując solidne podłoże pod póz
niejsze ćwiczenia
praktyczne w MS SQL Server 2005. W drugiej części kursu zostaną omówione bardziej
zaawansowane aspekty oraz nowości i możliwości, jakie oferuje SQL Server w wersji 2005.
Zatem sprawdz
cie, w jaki sposób najszybciej stać się posiadaczem Microsoft SQL Server
2005 i zapraszam do lektury.
Jak zdobyć Microsoft SQL Server 2005?
Na stronie http://www.microsoft.com/poland/sql/default.mspx jest udostępniona w pełni
darmowa wersja którą możemy wykorzystać za równo do celów naukowych jak i
komercyjnych. Firma Microsoft udostępnia produkt na którym możemy bez problemu postawić
nasze małe systemy bazodanowe. Dodatkowym atutem jest dodanie całkiem niezłego
narzędzia służącego do zarządzania serwerem Express Manager. W podobny sposób
możemy zdobyć produkty Visual Studio .NET do tworzenia aplikacji okienkowych i stron
internetowych. Tak jak w przypadku serwera baz danych należą one do wersji Express. Jeśli
ktoś planuje tworzyć systemy bazujące na stronach internetowych polecam kursy dotyczące
ASP.NET oraz samego języka C#. Które również są udostępnione w serwisie.
Co wchodzi w skład kursu?
Kurs składa się z artykułów, z których każdy, jako oddzielna całość, udziela miej lub bardziej
wyczerpujących informacji na temat Microsoft SQL Server 2005 i baz danych. Kolejne artykuły
prowadzą krok po kroku przez instalację MS SQL Server 2005 oraz wyjaśniają, co kryje się
Autor: Grzegorz Chuchra Strona 3
www.CentrumXP.pl
pod poszczególnymi opcjami instalacji, która jak nietrudno się domyślić, jest dosyć
skomplikowana i łatwo, nie znając podstaw, utrudnić sobie życie dokonując nieprawidłowej
instalacji.
Kolejny artykuł pomoże w zapoznaniu ze środowiskiem SQL Server 2005  z jego
podstawowym narzędziem, z którego będziemy korzystać, czyli z Microsoft SQL Server
Management Studio. Na przykładzie podstawowych operacji SELECT, UPDATE i DELETE
poznamy, w jaki sposób możemy wykonywać zapytania na bazie danych.
Następne artykuły zawierać będą podstawowe informacje o zasadach rządzących relacyjnymi
bazami danych. A
ykniemy troszkę historii, poznamy podstawową terminologię (tabele, encje,
relacje), strukturę baz danych oraz podstawowe cechy tabel. Następnie zastanowimy się nad
tym, w czym leży sedno słowa  relacyjny w modelu baz danych i poznamy zasady
funkcjonowania kluczy głównych, kluczy obcych i jaki z nimi związek mają klucze złożone.
Nauczymy się zasad tworzenia kluczy i sprawdzania ich poprawności. W stworzeniu dobrego
projektu bazy danych pomoże nam artykuł o normalizacji. Z niego dowiemy się, jaki jest sens
eliminowania z bazy danych nadmiarowości danych. Przechodząc po kolei pierwszą, drugą i
kolejne postaci normalne bazy danych, na przykładach i z lekką tylko dozą matematycznych
zasad nauczymy się tworzenia dobrych pod tym względem baz danych. Teoria, którą w tych
kursach przyswoimy będzie potrzebna do zrozumienia kolejnych artykułów.
Następne artykuły skupią się już na praktycznym zastosowaniu dotychczas przyswojonej
wiedzy w projektach. Poznamy podstawowe typy danych w MS SQL Server 2005  typy
tekstowe, liczbowe, typy daty i czasu, binarne, walutowe, specjalne oraz nowość w tej wersji
SQL Server  typ XML  specjalnie do przechowywania danych w formacie XML.
W kolejnych artykułach przyjrzymy się Data Definition Language (DDL). Nauczymy się
podstawowych operacji: tworzenia, modyfikowania i usuwania bazy danych (CREATE, ALTER,
DROP) oraz w jaki sposób zaimplementować mechanizm kluczy głównych i obcych w nowo
tworzonej bazie zarówno z poziomu skryptu jak i z poziomu Management Studio.
Następnie poznamy instrukcje wchodzące w skład Data Manipulation Language(DML).
Sprawdzimy, w jaki sposób wykonywać zapytania SELECT, INSERT, UPDATE i DELETE oraz
jak w szybki sposób generować zapytania za pomocą designera. Poznamy też podstawy
stosowania klauzuli WHERE. Sama instrukcja zostanie dokładniej omówiona w osobnym
artykule. Poznamy w nim operatory porównania dostępne w Transach-SQL, podstawowe
zasady konwersji (np. daty), znaki autouzupełnienia oraz sposoby, w jakich można łączyć
kombinacje dotychczas poznanych funkcji.
Autor: Grzegorz Chuchra Strona 4
www.CentrumXP.pl
W kolejnych artykułach poznamy funkcje agregujące  potężne narzędzie pracy bazy danych,
bez którego nie może się obejść żaden system raportowania oparty o bazy danych. Poznamy,
jak obliczać średnie, minima, maksima na podstawie zadanych warunków. Poznamy COUNT
oraz klauzulę GROUP BY, ORDER BY oraz HAVING. Cały artykuł będzie poświęcony
złączeniom, czyli poleceniom typu JOIN: INNER JOIN (złączenie wewnętrzne), CROSS JOIN
(iloczyn kartezjański), OUTER JOIN (złączenie zewnętrzne) oraz temu, jak z nich korzystać, by
efektywnie wybierać dane ze skorelowanych ze sobą tabel.
Następny artykuł będzie poświęcony podzapytaniom, czyli selektom w selekcie 
autonomicznym zapytaniom, które zagnieżdżone w innym zapytaniu, zwracają do niego dane,
z których następnie korzysta zapytanie nadrzędne. Nauczymy się również korzystać z tabel
tymczasowych oraz poznamy sposób definicji zmiennych w MS SQL Server 2005.
Cały artykuł zostanie również poświęcony mechanizmowi transakcji, czyli sposobie
zabezpieczania danych w bazie danych przed utratą spójności. Poznamy własności transakcji,
i sposoby wyłapywania błędów podczas jej trwania. Następnie poznamy możliwości i
zastosowanie funkcji pozycjonujących (RANK, DENSE_RANK, ROW_NUMBER oraz NTILE).
Sprawdzimy również działanie wyrażeń tablicowych (CTE) oraz skorzystamy z rekurencyjnego
sposobu wywoływania zapytań.
Omówimy sobie również sposób implementacji XML w SQL Server 2005, metody importu
danych w formacie XML oraz eksportu danych z bazy danych do pliku XML. To jedna z
nowości w SQL Server 2005. Efektywne korzystanie z danych w formacie XML jest teraz
bardzo dobrze wspierane w naszym serwerze.
Na koniec zaprezentuję, w jaki sposób wygląda integracja SQL Server 2005 z MS Visual
Studio 2005, prezentując funkcje bazodanowe na przykładzie funkcji do formatowania daty
DateFormater. Napiszemy również wspólnie własną funkcję agregującą  sumującą i poznamy
atrybuty agregatów, co pomoże w tworzeniu własnych, bardziej rozbudowanych funkcji.
Podsumowanie
Skoro dowiedzieliśmy się, jak zdobyć MS SQL Server 2005 i jaka będzie treść kursu,
wszystkich, którzy czują się na siłach zachęcam do lektury kolejnych artykułów.
Autor: Grzegorz Chuchra Strona 5
www.CentrumXP.pl
02. Instalacja. Opis elementów wchodzących w
skład SQL Server 2005.
Microsoft SQL Server 2005 to potężne narzędzie  baza danych, która oprócz podstawowej
funkcji przechowywania, przetwarzania i wyszukiwania danych posiada szereg dodatkowych
narzędzi ułatwiających m.in. jej eksploatację, zarządzanie, analizę i raportowanie. Opis
niektórych z komponentów (podstawowych i zaawansowanych) przedstawiam poniżej.
SQL Server Database Services: podstawowy silnik bazy danych. Zawiera narzędzia do
zarządzania danymi relacyjnymi oraz XML. Posiada narzędzia do replikacji (Replication) oraz
wyszukiwania (Full-Text search).
W skład SQL Server Database Services wchodzi:
1. Data files (odpowiedzialny za stworzenie folderu w ścieżce, która użytkownik poda jako
folder docelowy instalacji);
2. Replication (umożliwia kopiowanie danych i obiektów bazy danych między jedną lub
wieloma bazami danych);
3. Full-Text search (zawiera silnik do szybkiego wyszukiwania danych tekstowych w bazie
danych  tworzy indeksy dla danych tekstowych, dzięki czemu wyszukiwanie jest
szybsze).
Analysis Services: zawiera narzędzia OLAP (Online Analytical Processing), które umożliwiają
przedstawianie analiz oraz wspierają wyszukiwanie informacji w hurtowniach danych.
Report Server: narzędzie służące do zarządzania, wykonywania, przedstawiania i dystrybucji
raportów. Instaluje domyślnie Report Builder, który umożliwia raportowanie ad hoc oraz Report
Manager  narzędzie do zarządzania Report Server em.
Notification Services: platforma, która umożliwia rozwijanie i udoskonalanie aplikacji służących
do wysyłania spersonalizowanych okresowych informacji do różnych urządzeń lub innych
aplikacji. W jej skład wchodzą Engine Components, Client Components oraz Rules
Components.
Autor: Grzegorz Chuchra Strona 6
www.CentrumXP.pl
Integration Services: zbiór narzędzi i programowalnych obiektów służących do tworzenia i
zarządzania zbiorami, które wydobywają, przetwarzają i ładują dane, jak również wykonują
różne zadania.
Client Components: zawiera narzędzia do zarządzania bazą danych oraz przykładowe bazy
danych i aplikacje. W skład Client Components wchodzą:
1. SQL Server Command Line Tools: zawiera narzędzia, które umożliwiają administrację
serwerem SQL z poziomu linii poleceń.
2. Report Server Tools: instaluje narzędzie do konfiguracji Report Server a jak i umożliwia
administrację Report Server em z poziomu wiersza poleceń.
3. Connectivity Components: instaluje komponenty umożliwiające komunikację między
klientem a serwerem (zawiera m.in. biblioteki dla ODBC oraz OLE DB.
4. Programming Models: instaluje oprogramowanie umożliwiające developerom
dodawanie lub modyfikowanie m.in. narzędzi replikacji oraz relacji.
5. Data Transformation Services Runtime: instaluje środowisko umożliwiające
uruchamianie pakietów stworzonych w SQL Server 2000 za pomocą narzędzia Data
Transformation Services.
6. Management Tools: zawiera narzędzia do zarządzania SQL Server em (SQL Server
Configuration Manager  do zarządzania ustawieniami konfiguracji sieci serwera i
klienta, SQL Server Management Studio  m.in. do edycji i wykonywania zapytań, SQL
Profiler  umożliwia graficzny monitoring bazy danych SQL Server - i Replication
Monitor  umożliwia graficzny podgląd topologii replikacji oraz dostarcza szczegółowych
informacji na o statusie publikacji i subskrypcji).
7. Development Tools: instaluje następujące narzędzia: Business Intelligence
Development Studio, Reporting Services Report Designer, Reporting Services Model
Designer, Report Designer (narzędzie do publikacji raportów dla Report Server) oraz
SDK.
Dodatkowo fimra Microsoft udostępnia cały szereg pomocy które umożliwiają lepsze i bardziej
efektywne zarządzanie bazą danych. Dostępne na stronach MS Books Online documentation
and Samples: zawiera wsparcie techniczne Microsoftu - Books Online, przykładowe bazy
danych (3 bazy danych AdventureWorks) oraz sample (przykładowe kody, progamy oraz
rozwiązania).
Większość powyżej opisanych modułów jest przeznaczona dla systemów bazodanowych o
wielkiej złożoności. Dlatego wersja Express, którą będziemy instalować, standardowo ich nie
posiada. Pomimo to w większości przypadków w ogóle nie czyni jej gorszej od swoich
Autor: Grzegorz Chuchra Strona 7
www.CentrumXP.pl
 bogatszych kuzynów. A w niektórych jej  lekkość , spowodowana tym iż nie posiada
pomięciożernych Analysis Services czy Notification Services, sprawia że praca jest
przyjemniejsza i znacznie szybsza.
Należy pamiętać iż zdecydowana większość małych systemów informatycznych potrzebuje
tylko prostego repozytorium danych do którego MS SQL Server 2005 Express Edition nadaje
się idealnie (a przy tym jest za darmo!).
03. SQL Server - instalacja krok po kroku
Wymagania sprzętowe:
Przed rozpoczęciem instalacji należy upewnić się, że sprzęt, na którym zostanie dokonana
instalacja Microsoft SQL Server 2005 jest odpowiedni.
Spośród najbardziej popularnych systemów operacyjnych, MS SQL Server 2005 można
zainstalować na Windows 2000 z SP4, Windows XP SP2 Professional oraz Windows 2003.
Pełna lista systemów operacyjnych, na których można zainstalować MS SQL Server 2005
znajduje się w SQL Server Setup Help (plik dostępny na płycie instalacyjnej) w zakładce
Hardware and Software Requirements for Installing SQL Server 2005.
Na platformach 32-bitowych instalacja powiedzie się na komputerach wyposażonych w
procesor co najmniej 550 MHz (zalecane 1 GHz lub więcej), 256 MB RAM (zalecane 512 MB
lub więcej). Pamięć dyskowa potrzebna do instalacji zależy od wybranych komponentów:
1. SQL Server database components: 214 MB
2. Analysis Services: 79 MB
3. Reporting Services: 51 M
4. Notification Services: 34 MB
5. DTS: 154 MB typical
6. Tools, samples, and sample databases: 880 MB
7. .NET Framework: 210 MB
8. Express Edition: 405 MB
Dla czystości sumienia wspomnę jakie wymagania są potrzebne na platformach 64-bitowych.
Kształtują się one w następujący sposób: procesor - X64 (lub kompatybilny  np. AMD
Opetron), minimum 1 GB RAM oraz w zależności od instalowanych komponentów pamięć
dyskowa:
Autor: Grzegorz Chuchra Strona 8
www.CentrumXP.pl
1. SQL Server database components: 335 MB dla I64 (214 MB dla platformy X64)
2. Notification Services: 42 MB dla I64 (32 MB dla platformy X64)
3. Tools, samples, and sample databases: 601 MB dla I64 (530 MB dla platformy X64)
4. .NET Framework: 645 MB dla I64 (662 MB dla platformy X64)
Instalacja MS SQL Server 2005 Express Edition
Powyżej przedstawiłem obraz profesjonalnej wersji SQL Server 2005. Nas jednak będzie
szczególnie interesuje wersja Express i jej instalacje. Postaram się dokładnie przedstawić co i
w jakiej kolejności należy zainstalować aby nasza baza danych działała oraz, co chyba jest
równie ważne, można było z niej swobodnie korzysta.
Po pierwsze musimy skompletować wszystkie odpowiednie składniki które muszą być
zainstalowane. Poniżej wylistowane są linki do odpowiednich programów. Ważne jest to, iż
wszystkie te programy są darmowe i można je pobrać bezpłatnie z stron firmy Microsoft.
" Microsoft Framework 2.0
http://www.microsoft.com/downloads/thankyou.aspx?familyId=0856eacb-4362-4b0d-8edd-
aab15c5e04f5&displayLang=en&oRef=http%3a%2f%2fwww.microsoft.com%2fdownloads%2f
details.aspx%3ffamilyid%3d220549b5-0b07-4448-8848-dcc397514b41%26displaylang%3den
" Microsoft SQL Server 2005 Express Edition
http://www.microsoft.com/downloads/info.aspx?na=46&p=3&SrcDisplayLang=en&SrcCategor
yId=&SrcFamilyId=220549b5-0b07-4448-8848-
dcc397514b41&u=http%3a%2f%2fdownload.microsoft.com%2fdownload%2ff%2f1%2f0%2ff1
0c4f60-630e-4153-bd53-
c3010e4c513b%2fSQLEXPR.EXE&oRef=http%3a%2f%2fwww.microsoft.com%2fsql%2fediti
ons%2fexpress%2fdefault.mspx
" Microsoft SQL Management Studio
http://www.microsoft.com/downloads/info.aspx?na=46&p=2&SrcDisplayLang=en&SrcCategor
yId=&SrcFamilyId=82AFBD59-57A4-455E-A2D6-
1D4C98D40F6E&u=http%3a%2f%2fdownload.microsoft.com%2fdownload%2ff%2f1%2f1%2f
f11aec2c-54d1-4b49-b339-5ccb265a5741%2fSQLServer2005_SSMSEE.msi
" AdventureWorks http://download.microsoft.com/download/a/1/8/a1872d6b-f740-4c52-a2c5-0
eb5bdd1a7e3/AdventureWorksDB.msi
Po pierwsze musimy zainstalować MS .NET Framework 2.0. Można w wielkim skrócie
powiedzieć, że jest to zbiór bibliotek które rozszerzają funkcjonalność serwera i bez których
wersja 2005 nie może być poprawnie zainstalowana. Ponieważ do najnowszego serwera
została dodana możliwość wykorzystania nie tylko zwykłego T-SQL do tworzenia zapytań, ale
również języków obiektowych takich jak C# i VB. Ale o tym powiemy w dalszej części kursu.
Teraz skupmy się na poprawnym zainstalowaniu samego Framework.
Autor: Grzegorz Chuchra Strona 9
www.CentrumXP.pl
Instalacja Framework 2.0
Jest to chyba jedna z łatwiejszych rzeczy którą musimy wykonać. Wystarczy uruchomić wersje
instalacyjną, zaznaczyć CheckBox który mówi, że zapoznaliśmy się z licencją.
I praktycznie po wszystkim. Teraz tylko odrobina cierpliwości która doprowadzi nas do ekranu z
podziękowaniami.
Autor: Grzegorz Chuchra Strona 10
www.CentrumXP.pl
Instalacja SQL Server Express Edition
Teraz gdy już mamy zainstalowanego Frameworka możemy przejść do instalacji samego
serwera baz danych. Jest to trochę bardziej skomplikowana operacja (łatwiejsza już chyba być
nie może). Podobnie jak w poprzednim przypadku wybieramy opcje Next, aż do momentu
kiedy napotkamy ekran wyglądający następująco.
Autor: Grzegorz Chuchra Strona 11
www.CentrumXP.pl
Należy wybrać sposób autoryzacji połączeń z serwerem SQL. Microsoft zaleca stosowanie
Windows Authentication Mode, jeśli to możliwe, ze względu na jakość zabezpieczeń, które są
tam wykorzystywane. W tym przypadku, jeśli użytkownik łączy się używając konta Microsoft
Windows, serwer SQL sprawdza nazwę i hasło konta używając informacji zapisanych w
systemie Windows przy pomocy bezpiecznego protokołu Kerberos. Konto sa (system
administrator) zostaje utworzone, lecz jest zablokowane.
Jeśli z jakichś względów użytkownik jest zmuszony do wybrania opcji Mixed Mode, musi
pamiętać o utworzeniu mocnego hasła dla konta administratora (sa). Hasło SQL Server może
mieć długość od 1 do 128 znaków i zawierać kombinację liter, znaków i cyfr. Mocne hasło musi
zawierać litery duże i małe, cyfry, znaki typu %,^ lub &. Jeśli wymyślone hasło nie będzie
spełniało powyższych zasad i nie będzie mocne, użytkownik otrzyma komunikat:  You must
provide a strong sa password to continue SQL Server Setup. To review strong password
requirements, click Help on the Authentication Mode page. tak długo, aż nie wprowadzi
odpowiednio mocnego hasła.
W naszym przypadku należy wybrać Widows authentication i kliknąć next.
Autor: Grzegorz Chuchra Strona 12
www.CentrumXP.pl
Error and Usage Report Settings
Domyślnie obydwie opcje są ustawnione na włączone, jednak można je wyłączyć. Jeśli opcja
Error Report zostanie włączona, SQL Server zostanie tak skonfigurowany, by przesyłać raport
do Microsoft w przypadku gdy pojawi się Fatal Error podczas korzystania z MS SQL Server
2005. Wysyłanie takich raportów pomaga firmie Microsoft w udoskonalaniu swoich produktów i
usuwaniu błędów w kolejnych edycjach oprogramowania. Oczywiście informacje przesyłane są
do Microsoft bezpiecznym połączeniem https i są traktowane jako poufne. Jeśli opcja Feature
Usage zostanie włączona, SQL Server będzie wysyłał do Microsoft raporty z informacjami o
konfiguracji sprzętowej podczas używania MS SQL Server. Zaznaczenie odpowiednich opcji
zależy tu od użytkownika i jego chęci współpracy z dostarczycielem oprogramowania.
Ready to Install
Po przejściu przez proces konfiguracji instalacji, program instalacyjny prosi o potwierdzenie
instalacji. Po kliknięciu Install rozpocznie się instalacja Microsoft SQL Server 2005. Można
sobie zrobić kawę i przystąpić do ostatnich operacji.
Autor: Grzegorz Chuchra Strona 13
www.CentrumXP.pl
Pomyślne zainstalowanie MS SQL Server 2005 zostanie zakomunikowane wyświetleniem
informacji. Należy potwierdzić, klikając Finish.
Instalacja MS Management Studio Express
Management Studio jest produktem który umożliwia zarządzanie repozytorium danych za
pomocą okienkowego interfejsu. Jest to bardzo przyjemny program integrujący zarówno usługi
administracyjne jak i programistycznie związane (Query Analyzer + Enterprise Manager z SQL
Server 2000).
Instalacja jest również banalnie prosta. Wystarczy kliknąć i czekać na zakończenie procesu.
Autor: Grzegorz Chuchra Strona 14
www.CentrumXP.pl
I w końcu okno podziękowań.
Następnie uruchamiamy Start  Programy  Microsoft SQL Server 2005  SQL Server
Management Studio. Teraz w SQL Management Studio baza danych jest uruchomiona.
Należy ją wybrać prawym klawiszem myszy, wcisnąć Connect i połączyć się z nią.
Autor: Grzegorz Chuchra Strona 15
www.CentrumXP.pl
Instalacja bazy danych AdventureWorks
Wraz z pojawieniem się nowej bazy danych oraz licznych udogodnień w niej wprowadzonych.
Została zrobiona wzorcowa baza danych wykorzystująca wszystkie ciekawostki nowo dodane.
Uruchommy sobie teraz ostatni skrypt który nam pozostał. Nie będę się rozwodził nad jego
instalacją gdyż najtrudniejsze w tym wszystkim było kliknięcie na przycisk Next. Prawdziwy
problem pojawia się dopiero wtedy gdy uruchamiamy Management Studio i nie widać tam
bazy danych która właśnie została dodana.
Aby to zmienić przejdziemy do najtrudniejszej operacji w całym artykule (nie licząc pobierania
wszystkich programów na słabym łączu). Musimy dodać plik z zainstalowaną przykładową
bazą danych do naszego serwera. W tym celu przeprowadzamy operacje tak jak na
poniższych zdjęciach.
W oknie Object Explorer klikamy prawym guzikiem myszy na folder Databases (tu znajdują się
wszystkie bazy danych składowane na serwerze) i wybieramy z podręcznego menu opcje
Attach.
Autor: Grzegorz Chuchra Strona 16
www.CentrumXP.pl
Musimy teraz wyszukać plik który został dodany podczas instalacji bazy danych
AdventureWorks i wcisnąć ok. Po tej operacji powinniśmy mieć dodaną nową bazę do
naszego serwera.
Podsumowanie
Operacja zainstalowania serwera baz danych nie jest banalną sprawą. Poprawna konfiguracja
i odpowiednie zabezpieczenia wymaga ogromnej wiedzy, a przy tym dużego doświadczenia.
Kurs ten jest przeznaczony dla osób które chcą zajmować się tworzeniem aplikacji
wykorzystujący repozytoria danych, więc opcje związane z zabezpieczeniem serwera
odsuniemy na dalszy plan.
Skupimy się na projektowaniu i wykorzystaniu maksymalnie możliwości jakie daje nam
środowisko i rozszerzenia w języku zapytań T-SQL oraz możliwością wykorzystania
nowoczesnego języka C#.
Autor: Grzegorz Chuchra Strona 17
www.CentrumXP.pl
04. Pierwszy kontakt. Czyli czym jest baza
danych i jak z niej korzystać.
Zakładam, że każdemu udało się już poprawnie zainstalować i uruchomić aplikacje. Startujemy
więc z miejsca, gdzie ostatnio zakończyliśmy.
Wszyscy mamy uruchomione Microsoft SQL Server Management Studio, poniższy
screenshoot pokazuje jak wygląda to u mnie.
Pierwszą operacją, jaką wykonamy, będzie wylistowanie informacji o zgromadzonych
kontaktach w tabeli Contacts. Operację wykonamy na dwa sposoby. W okienku Object
Explorer po rozwinięciu katalogu Databases pokazuje się lista baz danych dostępnych na
naszym serwerze. Standardowo będziemy korzystać z bazy danych AdventureWorks, która
została dołączona w podstawowym pakiecie. Kolejnym krokiem jest rozwinięcie folderu Tables.
Z listy tabel wybieramy Person.Contact i klikamy na nią prawym guzikiem myszy Z menu
podręcznego wybieramy  Open Table . W ten oto sposób wyświetliliśmy wszystkie dane
kontaktowe. Teraz przeprowadzimy tą operację za pomocą języka Transact-SQL. Ponieważ
sama aplikacja wspomaga tworzenie zapytań, nie będziemy musieli zbytnio się napracować.
Podobnie jak w poprzednim przypadku klikamy prawym guzikiem myszy na tabelę
Autor: Grzegorz Chuchra Strona 18
www.CentrumXP.pl
Person.Contact - z tą różnicą, że tym razem wybieramy opcję Script Table as, a następnie
SELECT To i New Query Editor Window. Otwiera się nowa zakładka z wygenerowanym
zapytaniem bazodanowym wyświetlającym wszystkie dane z tabeli Contact. Teraz wystarczy
tylko wcisnąć przycisk Execute (lub wcisnąć F5) znajdujący się nad nazwą zakładki.
Różnica między tym trybem wyświetlania, a poprzednią opcją jest następująca. W pierwszej
opcji mieliśmy możliwość edycji danych, usuwania wierszy itp. W drugim zaś przypadku
możemy jedynie przeglądać dane, bo wiersze są wylistowane w trybie read-only. Jednak
programowe wyświetlanie wierszy jest bardziej elastyczną formą, co zaraz postaram się
udowodnić. Jak pewnie zauważyliście, ilość informacji, która się pojawiła jest przytłaczająco
duża. A nam potrzeba zaledwie kilku podstawowych danych, takich jak imię, nazwisko, data
urodzenia oraz może jeszcze adres. Przechodzimy więc do modyfikacji naszego zapytania.
Usuwamy z niego większość pól tak, aby zostało w takiej formie jak jest poniżej.
SELECT [FirstName]
,[LastName]
,[EmailAddress]
,[Phone]
FROM [AdventureWorks].[Person].[Contact]
Autor: Grzegorz Chuchra Strona 19
www.CentrumXP.pl
Dane, które teraz zostały wyświetlone są znacznie czytelniejsze i pozwalają nam na lepszą
interpretacje wyników zapytania. Dodając dodatkową klauzulę, możemy posortować kontakty
według nazwiska bądz
imienia.
SELECT [FirstName]
,[LastName]
,[EmailAddress]
,[Phone]
FROM [AdventureWorks].[Person].[Contact]
ORDER BY [LastName]
Teraz, kiedy już umiemy wyświetlać dane, przejdziemy do ich edycji, czyli zmiany wartości w
poszczególnych komórkach, a także usuwania całych wierszy. Jak zwykle zrobimy to na dwa
sposoby (pierwszy  klikając, drugi - korzystając z języka zapytań). Postaram się, aby stało się
to już tradycją, że wszystkie operacje będę przedstawiał na dwa sposoby. Tak, aby każdy
mógł wyrobić w sobie swój własny sposób korzystania SQL Server 2005 w najwygodniejszy
dla niego sposób.
Przypuśćmy więc, że preferujemy polskie imiona, a że tak się składa, iż jesteśmy
administratorami serwera baz danych i wolno nam zrobić bardzo dużo z danymi, zmieniamy
imię pani Catherine na Katarzyna. Aby tego dokonać przystępujemy do analogicznej
procedury jak przed chwilą. Klikamy prawym klawiszem myszy na tabelę [Person].[Contact] i
wybieramy opcję Open Table, odszukujemy w wynikach pozycję z Ms. Catherine Abel (drugi
wiersz na liście) naciskamy na komórkę z imieniem Catherine i po prostu wpisujemy
Katarzyna. Po lewej stronie powinna pojawić się ikona z ołówkiem, co oznacza, że wiersz
został w jakiś sposób zmodyfikowany. W momencie gdy naciśniesz na jakąkolwiek inną
komórkę w innym wierszu ikon z ołówkiem znika a zmiany zostają zapisane. Kasowanie
odbywa się w bardzo podobny sposób. W miejscu gdzie przed momentem pojawił się ołówek
naciskamy prawym klawiszem myszy. Cały wiersz powinien ulec zaznaczeniu, a na menu
podręcznym powinna pojawić się opcja Delete. Po jej wybraniu pozostaje już tylko
zaakceptować wprowadzenie zmiany i wiersz zostaje na stałe usunięty z naszego
repozytorium danych. Jeśli już umiemy zmieniać dane z poziomu aplikacji, czas przejść do
nieco trudniejszego sposobu - języka zapytań. Chcąc zmienić imię pani Katarzyny z powrotem
na Catherine musielibyśmy napisać zapytanie wyglądające następująco:
UPDATE [AdventureWorks].[Person].[Contact]
SET [FirstName] = 'Catherine'
WHERE [FirstName] = 'Katarzyna'
Autor: Grzegorz Chuchra Strona 20
www.CentrumXP.pl
Na pierwszy rzut oka może wyglądać to nieco skomplikowanie, ale jeśli choć chwilkę skupisz
się na powyższym wyrażeniu, intuicyjnie stwierdzisz, że jest ono w pełni logiczne i zrozumiałe,
a stworzenie kolejnych zapytań nie sprawi Ci już żadnej trudności. Podobnie jest z drugą
operacją, jaką robiliśmy na zbiorze danych. Jeśli tym razem chcielibyśmy usunąć kontakt z
osobą o imieniu Catherine wystarczyłoby wpisać następującego selecta (za selecta uważamy
każdą z operacji typu Data Manipulation Language)
DELETE FROM [AdventureWorks].[Person].[Contact]
WHERE [FirstName] = 'Catherine'
Jeśli przez chwilę zastanowiłeś się nad powyższym kawałkiem kodu, zapewne masz
nieodparte wrażenie, iż nie jest ono do końca poprawne. Jeśli uruchomilibyśmy ten skrypt, z
repozytorium zostałyby usunięte wszelkie kontakty, które jako własność FirstName mają
Catherine. Czyli po prostu pozbylibyśmy się wszystkich kontaktów z paniami które mają na
pierwsze imię Katarzyna. A tego z pewnością nie chcielibyśmy. Aby uzyskać wynik zgodny z
naszymi oczekiwaniami, powinniśmy dodać jeszcze jeden warunek, który ograniczy zbiór
zmian. W tym celu nieco modyfikujemy klauzulę WHERE
DELETE FROM [AdventureWorks].[Person].[Contact]
WHERE [FirstName] = 'Catherine' AND [LastName] = 'Abel'
Tak zmieniony skrypt spowoduje usunięcie jednego rekordu ze zbioru kontaktów. Możemy
teraz sprawdzić, jak wielką pomyłkę zrobilibyśmy, gdybyśmy uruchomili najpierw pierwsze
zapytanie. I tak liczba usuniętych wierszy w pierwszym przypadku wyniosłaby:
SELECT COUNT(*) 'Ilosc wierszy'
FROM [AdventureWorks].[Person].[Contact]
WHERE [FirstName] = 'Catherine' AND [LastName] = 'Abel'
zaś w drugim:
SELECT COUNT(*) 'Ilosc wierszy'
FROM [AdventureWorks].[Person].[Contact]
WHERE [FirstName] = 'Catherine'
A
atwo więc jest zauważyć, iż zapytania są bardzo wrażliwe i należy poświęcić dużo uwagi, aby
je poprawnie zapisać. Szczególną uwagę powinniśmy poświęcić skryptom edytującym bądz

usuwającym dane. W tym konkretnym przypadku chwila nieuwagi mogła doprowadzić do
utraty 21 wierszy.
Autor: Grzegorz Chuchra Strona 21
www.CentrumXP.pl
Podsumowanie
Jeśli po przeczytaniu tego artykułu czujesz, że nie wszystko dokładnie zrozumiałeś, że są
jeszcze aspekty, które chciałbyś lepiej poznać, nie powinieneś się martwić. Kurs ten miał za
zadanie przybliżyć nam samą aplikacje, pokazać czym jest SQL Server 2005 i aplikacja
dostępowa Microsoft SQL Server Management Studio. Chciałem, by te osoby, które po raz
pierwszy zetknęły się z Transact-SQL mogły się oswoić, a przy tym troszeczkę pocieszyć, iż
znaczną cześć operacji można wykonać bez potrzeby pisania skomplikowanych skryptów. Zaś
te osoby, które czują troszeczkę niedosytu po powyższym artykule chciałbym uprzedzić, że
kolejne publikacje będą już bardziej konkretne i udokumentowane znacznie większą ilością
przykładów i teorii.
05. Wstęp do relacyjnych baz danych. Sposób
organizacji danych danych.
Większość serwisów gromadzących i operujących na danych korzysta z mniejszych lub
większych serwerów baz danych. Wśród tych serwerów zdecydowana większość działa w
oparciu o relacyjną strukturę danych. Tematowi relacyjnych baz danych i sposobu organizacji
danych chciałbym poświęcić poniższe parę stron.
Model relacyjnych baz danych został formalnie przedstawiony w latach siedemdziesiątych. Od
tamtego czasu na ten temat zostało wydanych setki książek i tysiące czasopism. Wszystko to
sprawiło, że z początkowej prostej zasady powstała rygorystycznie zdefiniowana koncepcja
gromadzenia i przetwarzania danych.
Model relacyjnych baz danych reprezentowany jest za pomocą dwuwymiarowych tablic. Każda
z tablic reprezentuje obiekty z rzeczywistego świata: ludzi, miejsca, ceny lub zdarzenia, o
których chcemy zbierać informacje. Sama relacyjna baza danych jest zbiorem takich
dwuwymiarowych tablic opisujących rzeczywistość. W ten sposób baza danych prezentuje
dane użytkownikom lub programistom.
Sposób, w jaki baza danych - w naszym przypadku MS SQL Server 2005 - przechowuje dane
na fizycznych dyskach nie będzie poruszany w tych artykułach.
Podstawowe zrozumienie mechanizmów funkcjonowania relacyjnego modelu danych jest
niezbędne do efektywnego używania systemów bazodanowych takich jak SQL Server
Autor: Grzegorz Chuchra Strona 22
www.CentrumXP.pl
2000/2005 czy Oracle, jak również małych systemów, takich jak MS Access, które również
bazują na relacyjnej organizacji danych.
Artykuły te są jedynie wstępem do relacyjnej koncepcji danych, skupimy się w nich na tych
aspektach, dzięki którym w szybkim czasie będziemy mogli skutecznie projektować i
administrować serwerem bazy danych. Pominiemy zaś matematyczne teorie związane z
relacjami.
Struktura i terminologia
Jak wspomniałem wcześniej, w relacyjnym modelu baza danych jest kolekcją powiązanych ze
sobą tabel. Tabela jest płaską strukturą zawierającą z góry określoną liczbę kolumn i dowolną
liczbę wierszy. Kolumny charakteryzują samą tabelę i dane, jakie mogą się w niej znajdować.
Wiersze zaś mówią już o obiekcie (osobie, samochodzie itd.), o którym dane chcemy
gromadzić. Każda z kolumn posiada kategorię danych, które mogą się pojawić w tej kolumnie.
Produkty
Kontakty
Pracownicy
Autor: Grzegorz Chuchra Strona 23
www.CentrumXP.pl
Powyższe zdjęcie pokazuje tabelę z danymi personalnymi osób zatrudnionych lub będących
klientami firmy Adventure Works. Kolumny przedstawiają więc informacje o imieniu, nazwisku
emailu lub innych danych dotyczących każdej z osób. Wiersze zaś są kolejnymi wpisami
odzwierciedlającymi te osoby.
Alternatywną nazwą tabeli jest słowo encja lub relacja. Pierwsze z nich określa jakąś
dziedzinę, zbiór danych, który definiuje obiekt. Drugie (relacja) zostało trochę wypaczone
przez pierwszą wersję Access, która pojawiła się na polskim rynku, gdzie niefortunnie relacją
nazwano asocjacje (połączenie pomiędzy tabelami). Jednakże w środowisku naukowym
popularnie stosowane są wszystkie trzy określenia: tabela, relacja i encja.
W praktyce tabele opisujemy poprzez nazwę kolumn. Na pierwszy rzut oka daje nam to
dostateczną ilość informacji o tabeli.
Autor: Grzegorz Chuchra Strona 24
www.CentrumXP.pl
Tabele charakteryzują się sześcioma podstawowymi cechami.
1. Autonomiczność wartości
2. Kolumny są tego samego typu
3. Wiersze są unikalne
4. Kolejność ustawienia kolumn jest dowolna
5. Kolejność ustawienia wierszy jest dowolna
6. Każda z kolumn musi mieć unikalną nazwę
Autonomiczność wartości
Własność ta nakłada ograniczenie na dane wpisywane do pola (skrzyżowanie kolumny i
wiersza). Przykładowo: jeśli mamy kolumnę z miastami, to do pojedynczej komórki nie
będziemy wpisywać danych w następujący sposób:
tylko
O tej własności szerzej sobie powiemy przy omawianiu zagadnienia związanego z
normalizacją relacyjnej bazy danych.
Autor: Grzegorz Chuchra Strona 25
www.CentrumXP.pl
Kolumny są tego samego typu
Oznacza to, że wartośći w danej kolumnie są takiego samego typu. Jeśli kolumna nazywa się
BirthDate, musimy mieć pewność, iż wszystkie wartości znajdujące się w tej kolumnie są
datami. Mechanizm bazy danych uniemożliwi wpisanie do danej kolumny wartości, która nie
mogłaby zostać przekonwertowana do daty. Taka sama sytuacja jest również z polem
odpowiedzialnym za stan produktów na magazynie. Przypuśćmy, że w bazie mamy produkty i
za opisanie ich liczności odpowiada kolumna Count. Naturalnie spodziewamy się, że liczba ta
będzie liczbą całkowitą dodatnią. W tej sytuacji mechanizm serwera bazy danych powinien nie
dopuścić do wpisania w tą kolumnę przykładowo wartości tekstowej  osiem .
Zarówno programiści jak i użytkownicy mogą być pewni typu danych jak jest składowany w
danej kolumnie. Umożliwia to sprawdzenie poprawności wpisywania danych za pomocą
różnych interfejsów użytkownika.
Unikalność wierszy
Własność ta zapewnia, że w bazie danych nie ma dwóch identycznych wierszy. Aby dodać
rekord, musi istnieć w nim przynajmniej jedna kolumna, w której wartości dla poszczególnych
wierszy są różne. Aby spełnić to ograniczenie został stworzony mechanizm kluczy, który
będzie omówiony w następnym artykule.
Dzięki tej własności mamy zagwarantowaną unikalność wierszy, dzięki czemu każdy rekord
może być jednoznacznie zidentyfikowany za pomocą klucza głównego.
Kolejność ustawienia kolumn jest dowolna
Ta własność mówi o tym, że kolejność kolumn w tabeli nie wpływa na przechowywane w niej
dane. Kolumny mogą więc być wstawiane i pobierane w przypadkowej kolejności i nie będzie
miało to najmniejszego znaczenia dla danych przechowywanych w bazie. Taka organizacja
danych sprawia, że użytkownicy współdzielący dane nie muszą zwracać w tak dużym stopniu
uwagi na sposób strukturę.
Dodatkowo umożliwia zmianę fizycznej struktury przechowywania danych bez żadnego
wpływu na strukturę logiczną tabeli.
Autor: Grzegorz Chuchra Strona 26
www.CentrumXP.pl
Kolejność ustawienia wierszy jest dowolna
Własność ta jest analogiczna do tej związanej z kolumnami. Głównym profitem, który z niej
wynika, jest możliwość pobierania rekordów w dowolnym porządku. Nie musimy też
dodatkowo zapewniać mechanizmów związanych z odpowiednim ustawieniem wierszy.
Każda z kolumn musi mieć unikalną nazwę
Każda z kolumn musi mieć unikalną nazwę, ponieważ nazwa kolumny jest jedyną prostą i
intuicyjną możliwością zapewnienia unikalności w tabeli (jeśli kolejność nie ma znaczenia).
Unikalność jednak dotyczy wyłącznie tabeli, a nie całej bazy danych.
Podsumowanie
Koncepcja relacyjnych baz danych znacząco ewoluowała od zdefiniowania koncepcji. Z prostej
i dość luz
nej teorii stała się ściśle określonym i rygorystycznie opisanym kanonem zasad.
Sama struktura bazy opiera się na trzech podstawowych pojęciach: tabelach, kolumnach i
wierszach oraz ograniczeniach, wymuszając logiczny schemat i niepowtarzalność
odpowiednich kolumn i wierszy.
06. Klucze główne i obce
Klucze są podstawową koncepcją w teorii relacyjnych baz danych. Zapewniają tabelom
możliwość skorelowania ze sobą dwóch lub więcej tabel. Nawigacja w relacyjnej bazie danych
zależy od możliwości identyfikacji określonego wiersza w tabeli za pomocą klucza głównego.
Artykuł ten poświęcony będzie poświęcony teorii kluczy głównych i obcych oraz zasadom ich
działania.
Związek pomiędzy dwoma lub większą ilością tabel to asocjacja (w potocznym bazodanowym
języku znana również jako relacja). Asocjacja jest wyrażona za pomocą wartości klucza
głównego i kluczy obcych.
Klucz główny - Primary Key, to kolumna lub zbiór kolumn, które w sposób unikalny definiują
wiersz w danej tabeli. Klucz obcy jest kolumną lub zbiorem kolumn, który jest kluczem
głównym w innej tabeli. Można powiedzieć, że klucz obcy jest kopią klucza głównego z innej
tabeli. Asocjacja jest utworzona pomiędzy tabelami poprzez zaznaczenie, iż wartość z jednej
Autor: Grzegorz Chuchra Strona 27
www.CentrumXP.pl
tabeli, w której jest kluczem obcym, jest powiązana z wartością z innej tabeli, gdzie jest
kluczem głównym. Ważne jest to, iż wartość klucza obcego nie może istnieć bez powiązania z
kluczem głównym. Jeśli w tabeli pracowników mamy pole, które jest kluczem głównym w tabeli
z państwami, to nie możemy wpisać w nie wartości, która nie istnieje w tabeli państw. Silnik
bazy danych nie zezwoli, aby wstawić wartość klucza obcego, który nie posiada odnośnika na
klucz obcy.
Teraz, kiedy mamy na czym skupić naszą uwagę, możemy przejść do opisywania krok po
kroku poszczególnych struktur i technik pracy z kluczami. Powiemy, co określa klucz główny,
co za tym idzie - w jaki sposób odnalez
ć pole, które aplikuje do bycia kluczem. Aby utworzyć
taki podstawowy model danych możemy posłużyć się następującym algorytmem:
" identyfikacja pól, które mogłyby się stać kluczami głównymi
" sprawdzenie poprawności kluczy i związków pomiędzy tabelami
" przeniesienie kluczy głównych do innych tabel jako klucze obce.
Określenie kluczy głównych
Klucz główny musi unikalnie identyfikować wiersz. Każdy wiersz w naszej tabeli musi mieć
wartość która unikalnie go identyfikuje.
Aby zakwalifikować dany atrybut jako klucz główny, musimy sprawdzić, czy posiada
następujące własności:
" musi posiadać wartość dla każdego z wierszy
" dla każdego z tych wierszy wartość musi być unikalna
" wartość ta nie może się zmienić, ani nie może zostać usunięta podczas całego funkcjonowania
wiersza w tabeli
Autor: Grzegorz Chuchra Strona 28
www.CentrumXP.pl
Jeśli mielibyśmy tabelę z użytkownikami zarejestrowanymi w portalu internetowym, kluczem
głównym mógłby być login. Jest unikalny w ramach całej tabeli, a po usunięciu logina
użytkownik ulega usunięciu. Kluczem głównym nie mogłoby być nazwisko, gdyż może być
wiele osób, które nazywają się Kowalski. A to łamie drugą zasadę, która mówi, iż wartość musi
być unikalna.
W niektórych przypadkach możemy mieć kilku kandydatów do klucza głównego. Rozważmy
ponownie ten sam przypadek. Przypuśćmy, że mamy dodatkowe informacje o użytkowniku:
datę urodzenia, PESEL, email. Wiemy, że unikalne są dwie wartości: PESEL i email. Zatem
zarówno jedną i drugą możemy zaproponować na klucz główny w tabeli User. Teraz od nas
tylko zależy czy wybierzemy email czy PESEL. Ja osobiście wybrałbym PESEL ponieważ
wyszukiwanie w bazach po wartości typu INT (czyli typu całkowitoliczbowego) jest znacznie
szybsze, niż po łańcuchu znaków. Jeśli jednak byłby to portal możliwością logowania, trzeba
wziąć pod uwagę, że zmuszenie użytkownika, aby przy logowaniu wpisywał PESEL jest
trudniejsze niż skłonienie go do wpasania adresu email.
Klucze złożone
Czasem pojedyncza wartość nie wystarczy na jednoznaczne określenie wiersza.
Jeśli chcielibyśmy zapisać oceny z przedmiotów dla danego studenta musielibyśmy zapisać
następujące informacje: przedmiot, z którego ocena została wystawiona, studenta któremu ją
wystawiamy i samą ocenę. W tym celu należało by stworzyć następującą tabelę.
Przyporządkowujemy zajęciom o kluczu głównym 01 i studentowi o kluczu głównym 01 ocenę
pięć. Zaś temu samemu studentowi tylko dla innego przedmiotu o kluczu głównym 02 już
ocenę trzy. W tym wypadku mamy złożony klucz główny. Ponieważ ani LessonID ani też
StudentID pojedynczo nie spełnia wymagań unikalności klucza głównego, musimy zastosować
Autor: Grzegorz Chuchra Strona 29
www.CentrumXP.pl
klucz złożony. W tym wypadku kluczem będzie powiązanie LessonID i StudentID. Pojedynczo
żaden z nich nie może spełnić tego zadania, ale w sumie unikalnie określają każdy wiersz.
Sprawdzenie poprawności kluczy
Podstawowe zasady zarządzania i identyfikacji kluczy głównych:
" każda tabela w bazie danych powinna posiadać klucz główny unikalnie identyfikujący wiersz.
" klucz główny nie może być opcjonalny. Czyli nie może posiadać wartości nieokreślonej.
" klucz główny nie może się powtarzać. Może być co najwyżej jedna wartość atrybutu klucza
głównego w danej tabeli.
Sprawdzenie poprawności kluczy
Klucze obce są polami, które umożliwiają zachowanie związku pomiędzy identyfikatorem w
tabeli, a tabelą, z której wartość ta się wywodzi. Klucze obce umożliwiają zarządzanie
integralnością i spójnością danych. Każda asocjacja w bazie powinna być opatrzona kluczem
obcym.
W przykładzie ze złożonym kluczem głównym mieliśmy do czynienia z dwoma kolumnami
LessonID oraz StudentID. Jeśli zobaczylibyśmy, jak to wygląda w szerszym kontekście,
moglibyśmy wyciągnąć bardzo ciekawy wniosek. LessonID i StudentID są kluczami obcymi z
tabel Lesson i Student. Ponieważ za pomocą LessonID możemy pobrać informacje o tym,
jakiej lekcji dotyczy LessonID 01, zaś z tabeli Student możemy wybrać informacje o osobie, do
której StudentID jest przyporządkowane.
To jest właśnie istota kluczy. Nie musimy przechowywać wartości pola, wystarczy nam sam
klucz. Za pomocą jego unikalnej wartości możemy bez problemu wyciągnąć interesującą nas
wartość.
Podsumowanie
Klucze główne i klucze obce są podstawą teorii relacyjnych baz danych. Każda z tabel
powinna mieć kolumnę lub kolumny, na które można nałożyć klucz główny jednoznacznie
określający każdy z wierszy. Każda tabela wykorzystująca w jakiś sposób dane z innych tabel
powinna być powiązana z nią za pomocą klucza obcego
07. Normalizacja baz danych
Autor: Grzegorz Chuchra Strona 30
www.CentrumXP.pl
W artykule tym chciałbym zająć się jedną z ważniejszych kwestii związanych z relacyjnymi
bazami danych, czyli ich projektowaniem. Z projektowaniem baz danych wiąże się
nierozerwalnie pojęcie normalizacji. I jemu właśnie chciałbym poświęcić kolejnych kilka stron.
Dowiemy się, czym jest sama normalizacja, dlaczego została zdefiniowana i tak właściwie
dlaczego powinniśmy zawracać sobie nią głowę.
Artykuł ten zawiera dużą dawkę teorii, lecz jest to zaledwie kropla w morzu całego tematu
związanego z pojęciem normalizacji. Jeśli ktoś będzie chciał bardziej poważnie zagłębić się w
tym temacie polecam książkę J.D. Ulmana lub W. Cellarego.
Czym więc jest normalizacja? Najprościej mówiąc jest to proces polegający na wyeliminowaniu
z bazy dany wszelkiej nadmiarowości. A to z kolei prowadzi do zmniejszenia ilości
przechowywanych danych oraz wyeliminowaniu prawdopodobieństwa powstania anomalii i
zniekształceń podczas codziennej eksploatacji.
Najłatwiej będzie to zrozumieć na przykładzie.
Przed
Po
Jak widać dzięki tej operacji udało nam się zmniejszyć ilość danych, jakie są przechowywane
w repozytorium. Z każdym dodatkowym użytkownikiem zysk będzie coraz większy. Można
jednak uczynić strukturę bazy jeszcze bardziej klarowną. Dodatkowy atutem będzie
elastyczność jaka dzięki temu zostanie uzyskana.
Autor: Grzegorz Chuchra Strona 31
www.CentrumXP.pl
Jeśli zaszłaby konieczność zmiany imienia użytkownika, nie trzeba przeszukiwać kolejno
wszystkich wierszy w tabeli i przeprowadzać operacji uaktualnienia. Teraz wystarczy zmienić
imię w tabeli Student, bez konieczności kosztownej operacji zmiany wielu wierszy. Tak powoli
zbliżyliśmy się do zdefiniowania pierwszej postaci normalnej która głosi:
Pierwsza postać normalna
" w poszczególnych tabelach wyeliminuj powtarzające się grupy
" dla każdego zestawu danych pokrewnych utwórz oddzielną tabelę
" dla każdego zestawu danych pokrewnych określ klucz podstawowy
Skoro już jesteśmy przy definicjach to możemy się pokusić o przytoczenie kolejnych postaci
normalnych:
Druga postać normalna
" utwórz oddzielne tabele dla zestawów wartości odnoszących się do wielu rekordów
" ustal powiązania tabel za pomocą klucza obcego
Autor: Grzegorz Chuchra Strona 32
www.CentrumXP.pl
Przypuśćmy, że rozważamy przeprowadzanie raportów w dziekanacie jednej z wyższych
uczelni. Dane dotyczące studenta: imię, nazwisko znajdują się zarówno w tabeli związanej z
stypendiami, zajęciami itp. W takiej sytuacji nie ma potrzeby przechowywać danych
personalnych w każdej z tabel. Robimy więc dodatkową tabelkę ze studentami gdzie
wpisujemy dane o studencie. Tak jak zostało to pokazane w powyższym przykładzie.
Trzecia postać normalna
" wyeliminuj pola, które nie zależą od klucza
Jeśli w naszej tabeli studentów mamy wpisy dotyczące studentów z wymiany, wypada dodać
kolejne kolumny dotyczące kraju, z jakiego pochodzą. Jednak w tym przypadku znacznie
lepszym rozwiązaniem będzie stworzenie tabeli słownikowej (z góry ustalona lista państw)
gdzie będziemy przechowywać dane dotyczące kraju, a dla każdego ze studentów dodamy
tylko powiązanie z krajem, z którego pochodzi.
Tym sposobem wypisaliśmy podstawowe trzy podstawowe postacie normalizacji baz danych.
Oprócz nich występuje jeszcze dwie: czwarta i piątak postać normalna. W przeciwieństwie do
pierwszych trzech, które są dość intuicyjne, są one dość skomplikowane i wymagają
zaprzężenia do samego procesu projektowania elementów matematyki i algorytmiki. W
rzeczywistośći jednak nie wpływają one znacząco na wydajność samego repozytorium
danych, a jedynie udoskonalają sam projekt.
Autor: Grzegorz Chuchra Strona 33
www.CentrumXP.pl
Podsumowanie
W artykule tym przebrnęliśmy przez bardzo ważny temat z punktu widzenia poprawnego
modelu baz danych. Każdy, kto poważnie myśli o projektowaniu większego systemu nie może
przejść obojętnie obok zagadnienia normalizacji. Jak wspomniałem normalizacja (przynajmniej
w pierwszych trzech punktach) jest procesem dość intuicyjnym i jest nieświadomie
wykonywana przez większość praktyków.
Ponieważ sam proces jest dość sformalizowany, nie zawsze najlepszy projekt bazy powstaje
przez ślepe podążanie za powyższymi wytycznymi. W największych systemach
informatycznych często łamie się podstawowe zasady normalizacji uzyskując dzięki temu
znacznie wydajniejsze i szybsze systemy. Jednak jeśli rozpoczynamy naszą przygodę z
projektowaniem, powinniśmy dość rygorystycznie przestrzegać powyższych wskazówek, a z
pewnością baza będzie dobrze wykonana.
08. Co to jest typ danych? Jakie typy danych
dostarcza nam SQL Server 2005.
W kursie tym chciałbym przybliżyć wszystkim pojęcie typu danych oraz wymienić typy danych,
jakie występują w nowym Microsoft SQL Server 2005. Szczególną uwagę zwrócimy na
nowości jakie zostały wprowadzone w najnowszej wersji serwera.
Typy danych charakteryzują daną komórkę w wierszu. Mówią, jakiego rodzaju informacje
możemy umieścić w danej kolumnie. Przykładowo innego typu danych użyjemy, gdy będziemy
chcieli przechowywać imię użytkownika, innego dla daty jego urodzin, a jeszcze innego, gdy
będziemy chcieli przechowywać jego zdjęcie.
Poszczególne typy danych można zakwalifikować do różnych grup. Ponieważ dokładny opis i
wynikający z tego podział znacznie wykracza poza ramy tego artykułu, zastosujemy tu dość
ogólną segmentację ze względu na funkcjonalność. Poniżej przedstawiony jest podział na
grupy funkcjonalności:
Autor: Grzegorz Chuchra Strona 34
www.CentrumXP.pl
Typy tekstowe
Typy tekstowe składają się z dwóch podtypów. Pierwszy to typ łańcuchowy: char, varchar,
drugi zaś, unicode: ntest, nvarchar. A
ańcuchy znaków mogą zawierać litery, liczby i symbole.
W zależności od zadeklarowanej długości(np.: char(16)) mamy możliwość przechowywania
określonej ilośći znaków. Jeśli pole zadeklarowane na 16 znaków nie zostanie całkowicie
wypełnione (wpisany tekst ma mniej niż 16 znaków) reszta znaków zostanie uzupełniona
spacjami. Jeśli zaś ma więcej niż zadeklarowana liczba, wyraz zostanie obcięty na granicy
szesnastego znaku. Zaleca się stosowanie typów o zmiennej ilość (varchar(n)), w
przeciwieństwie do poprzednich nie są one dopełniane spacjami, co w dość jasny sposób
przyczynia się do efektywniejszego zarządzania zasobami pamięci serwera. Kolejne typy
danych, które służą do zapisywania tekstów mają zupełnie inną strukturę fizyczną aniżeli char.
Typ nchar, bo o nim tutaj mowa, jest zapisany w standardzie unicode. Standard ten gwarantuje
nam skuteczną pracę z serwerem obsługującym aplikacje wielojęzyczne. Jeśli chcielibyśmy
aby nasza aplikacja działała zarówno w języku włoskim, polskim jak i w angielskim, pola
zawierające tekst muszą być zadeklarowane jako nchar, w przeciwnym wypadku dojdzie do
przekłamań. Kosztem wprowadzenia typu nchar, jest zwiększenie fizycznej objętości. Standard
unicode zapisuje pojedynczy znak na dwóch bajtach podczas gdy ANSI używa do tego
jednego bajtu. Tak więc powinniśmy dokładnie przemyśleć, czy nasza aplikacja ma działać w
środowisku wielojęzycznym lub czy w niedalekiej przyszłości jest prawdopodobne, aby w takim
otoczeniu działała.
Dodatkowym, dość ważnym ograniczeniem jest maksymalna ilość znaków, które mogą być
składowane w jednym polu. Jego objętość nie jest nieograniczona. Jeśli chcemy
Autor: Grzegorz Chuchra Strona 35
www.CentrumXP.pl
przechowywać tekst o dużej długości, lepiej użyć typu ntext, do którego możemy wstawiać
nawet tekst o wielkości 2 GB!
Typy liczbowe
Typy całkowite
Typy liczbowe dzielimy na całkowite, przybliżone i dokładne. Jeśli w projekcie mamy
możliwość wyboru pomiędzy typem całkowitym, a pozostałymi, powinniśmy zawsze użyć
całkowitego. Dzięki temu zwiększymy wydajność i efektywność pracy naszego serwera. Typy
całkowite różnią się tylko wielkością zajętej powierzchni i ilością bajtów, na których są
składowane, a co za tym idzie zakresem liczb jaki można w nich umieszczać. Poniższa tabele
przedstawia typy danych całkowitych i towarzyszące im zakresy.
Typy całkowite
Jeśli w bazie chcemy przechowywać liczby z dokładnością do najmniej znaczącej cyfry
powinniśmy użyć typów decimal lub numeric. Dla typu numeric konieczne jest określenie
dwóch parametrów: precyzji i skali. Jeśli ich nie podamy, zostanie to zrobione za nas
automatycznie i wyniesie kolejno 18, 0. Pierwszy parametr określa, ile cyfr znajduje się przed
przecinkiem, drugi zaś mówi, ile cyfr znajduje się po przecinku. Dla przykładu: w celu
przechowywania w wartości nie większej niż tysiąc z dokładnością do dwóch miejsc po
przecinku użylibyśmy następującego typu numeric(3, 2). Jeśli pewnego dnia zmienilibyśmy
zdanie i chcielibyśmy przechowywać liczby z dokładnością do czwartego miejsca po
przecinku, należałoby zmienić typ danych na numeric(3, 4).
Przybliżone typy danych to float i real. Liczby przechowywane za pomocą tych typów używane
są do składowania danych statystycznych, gdzie dokładność nie jest najważniejszą sprawą, a
najważniejsza jest sama rozpiętość liczb, jakie można składować. Generalnie należy unikać
używania typów przybliżonych. Jeżeli mamy możliwość przechowywania potrzebnych
informacji przy użyciu decimal i numeric, należy je stosować.
Autor: Grzegorz Chuchra Strona 36
www.CentrumXP.pl
Data i czas
MS SQL Server 2005 udostępnia nam dwa typy danych służące do przechowywania dat.
Pierwszym z nich jest typ datetime. Za pomocą niego możemy z dokładnością do milisekundy
zapisać informacje w repozytorium. A oto jak wygląda standardowy format pola datetime 2005-
08-06 14:24:09.433. Na ogół nie jest nam potrzebna aż tak dokładna data. Jeśli chcemy
przechowywać datę urodzin użytkownika, w zupełności wystarczy nam pole typu
smalldatetime. Określa ono czas z dokładnością do jednej minuty. Przykład tej samej daty
która została wygenerowana dla pola datetime po przekonwertowaniu na smalldatetime 2005-
08-06 14:24:00.
Nie zawsze najtrafniejszym sposobem przechowywania daty jest pole datetime czy
smalldatetime. Jeśli chcielibyśmy przechowywać tylko rok urodzin użytkownika aplikacji,
wystarczy nam pole int. Dzięki takiej operacji zaoszczędzimy znaczną ilość miejsca, a przy tym
zyskamy na efektywności poprzez uniknięcie kosztownych operacji parsowania daty.
Typy binarne
Jak sama nazwa wskazuje służą do przechowywania danych binarnych. Dane
reprezentowane są za pomocą par heksadecymalnych, czyli w postaci szesnastkowej
tworzonej ze znaków od 0-9 i A-F. Pozostałe własności są analogiczne jak przy typach
znakowych binary  char, i varbinary  varchar. Z tą subtelną różnicą iż tu uzupełniamy
znakami 0x20.
Typy walutowe
Walutowe typy danych to money, smallmoney. Typy te mogą oczywiście przechowywać
wartości inne niż wartości dolarowe, aczkolwiek sam serwer nie posiada standardowych
funkcji konwersji walutowych. Zarwno money jak i smallmoney mają skalę równą cztery.
Oznacza to, iż mogą przechowywać czety cyfry po przecinku dziesiętnym.
Typy specjalne
Dwa typy danych: text i image służą do składowania dużych plików. Mogą przechowywać do
2GB danych na pojedynczą deklaracje. Jeśli jednak w grę wchodzi przechowywanie tak
Autor: Grzegorz Chuchra Strona 37
www.CentrumXP.pl
dużych ilości informacji powinniśmy wziąć pod uwagę możliwość umieszczenia plików poza
serwerem, a w bazie danych przetrzymywać jedynie ścieżkę do pliku.
Kolejnym typem danych jest BIT. Jest to logiczny typ danych, służący do składowania
informacji w postaci 0 lub 1. Może być używany do przechowywania informacji o zdarzeniu
potwierdzone/niepotwierdzone, włączone/wyłączone tak/nie. Przykładem może być sytuacja
potwierdzenia wpłaty za czesne lub informacja o płci użytkownika itp.
Ostatnim typem jaki chciałbym omówić jest XML. Pole to zostało wprowadzone dopiero w tej
wersji serwera. Umożliwia nam utworzenie kolumny w której będziemy przechowywać dane
jako pliki w formacie XML. Dodatkowo załączony został język XQuery za pomocą którego
możemy przeszukiwać tak składowany plik.
Podsumowanie
Niestety, aby zacząć praktyczne działanie z SQL Server 2005 konieczne jest przyswojenie
znacznej ilości wiedzy o strukturze, projektowaniu i sposobie w jaki działają repozytoria
danych. W tym kursie poznaliśmy typy danych, jakie występują w najnowszym serwerze oraz
w jakich sytuacjach należy je wykorzystywać. Wiedza taka jest niezbędna do poprawnego
projektowania i korzystania ze wszystkich serwerów baz danych.
09. Z teorii do praktyki czyli "Data Definition
Language"
 Z teorii do praktyki to motto przewodnie poniższego artykułu. Kolejnych kilka stron będzie
poświęconych poleceniom Data Definition Language (DDL). Za pomocą których możemy
przeprowadzać operacje edycji i usuwania elementów w MS SQL Server 2005.
Teraz, kiedy już mamy głowę nabitą teorią, możemy przejść do praktycznego aspektu
tworzenia baz danych. Stworzymy własny system bazodanowy zgodnie z projektem jaki został
zrealizowany i znormalizowany na wcześniejszym kursie. Zaczynamy więc od wymienienia
elementów języka Data Definition Language i wyjaśnieniu, czym jest sam język DDL.
Używamy go zawsze wtedy, gdy tworzymy, usuwamy lub wstawiamy nowy obiekt do już
istniejącego systemu. Inaczej mówiąc służy do zarządzania obiektami bazodanowymi.
Podstawowe jego elementy to:
Autor: Grzegorz Chuchra Strona 38
www.CentrumXP.pl
Jednak praktycznie wszystkie powyższe operacje można wykonać bez konieczności pisania
kodu, a jedynie za pomocą aplikacji dostępowej Microsoft SQL Server Management Studio.
Teraz już kończę wywody i przechodzimy do tworzenia bazy.
Najpierw utworzymy bazę danych za pomocą skryptu SQL. W tym celu naciskamy na przycisk
New Query, spowoduje to otwarcie nowego okna gdzie wprowadzimy nasz skrypt. W
najprostszej postaci wymaga to jedynie wpisania jednej linii kodu.
CREATE DATABASE Deanery
GO
Teraz wystarczy tylko wcisnąć przycisk Execute (lub F5) i można już sprawdzić czy nasza
baza danych została dodana. Niestety standardowo w okienku Object Explorer lista baz
danych nie odświeża się automatycznie(dotyczy to wszystkich elementów i operacji na bazie).
Aby zobaczyć wyniki konieczne jest odświeżenie korzenia Database w wyżej wymienionym
oknie. Jest to jest pokazane na poniższym zrzucie ekranowym.
Autor: Grzegorz Chuchra Strona 39
www.CentrumXP.pl
Jeśli umiemy już tworzyć bazę danych, chciałbym pokazać, jak można ją usunąć, a następnie
ponownie utworzyć za pomocą GUI aplikacji. Aby usunąć całą bazę danych wystarczy tylko
prawym guzikiem myszy kliknąć na obiekt, który chcemy usunąć. Następnie wybrać z menu
podręcznego opcje Delete i potwierdzić wybór. Alternatywnie skrypt wyglądałby również dość
nieskomplikowanie. Rożni się jedynie tym iż zamiast polecenia CREATE wstawiamy DROP:
DROP DATABASE Deanery
GO
Czym jest jednak pusta baza danych? Przejdz
my do tworzenia poszczególnych tabel i relacji
pomiędzy nimi w taki sposób, aby odwzorować strukturę bazy którą zaprojektowaliśmy
wspólnie we wcześniejszych rozdziałach.
Rozpoczynamy od utworzenia tabeli słownikowej zawierającej kraje z jakich pochodzą
studenci studiujący na naszej uczelni.
CREATE TABLE [dbo].[Country]
(
[CountryID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[CountryName] [nvarchar] (64) NOT NULL
)
GO
Ponieważ w naszej bazie klucze główne są wartościami całkowitymi musimy mieć pewność, iż
każde następne wstawione państwo będzie miało unikalny w skali tabeli klucz główny. Aby
sprostać temu zadaniu, dokładamy do klucza głównego klauzule IDENTITY (1, 1). Mówi ona
tyle, że wartość będzie nadawana od wartości 1 i z każdym kolejnym nadanym ID będzie
zwiększana. Jeśli byśmy użyli wyrażenia w następujący sposób IDENTITY (15, 3) to pierwszy
wstawiony do bazy danych kraj miał by ID 15, a następny 18. Kolejnym interesującym nas
polem jest wartość NOT NULL. Wspomnieliśmy o niej już wcześniej w rozdziale dotyczącym
typów danych. Tu chciałbym tylko przypomnieć, że jeśli wartość kolumny w bazie danych jest
ustawiona na NOT NULL oznacza, iż nie ma możliwości wstawienia do bazy danych rekordu,
który będzie miał wartość nieokreśloną dla danej kolumny. W naszym konkretnym przypadku
dotyczącym państw niemożliwe byłoby wstawienie wiersza, który nie ma podanej nazwy kraju.
Dzięki temu, iż CountryID ma ustawioną IDENTITY, nie musimy dbać o wstawienie tam
wartości. Serwer bazy danych sam zaopiekuje się uzupełnieniem pola oraz nadaniem
unikalnego w ramach tabeli klucza. Dla krajów założyliśmy, że liczba liter nie będzie większa
niż 64 znaki czyli  Zjednoczone Emiraty Arabskie oraz  Stany Zjednoczone Ameryki
Autor: Grzegorz Chuchra Strona 40
www.CentrumXP.pl
Północnej bez problemu zmieszczą się w przeznaczonym dla nich rekordzie. Dodatkowo
umożliwiliśmy wpisywanie wszystkich znaków międzynarodowych (parz  Co to jest typ
danych? Jakie typy danych dostarcza nam SQL Server 2005 ).
Skrypty tworzące kolejne tabele będę mniej dokładnie opisywał. Skupię się na tych opcjach,
które nie były przedstawione dotychczas.
CREATE TABLE [dbo].[Student]
(
[StudentID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[Firstname] [nvarchar] (64) NOT NULL ,
[Lastname] [nvarchar] (64) NOT NULL ,
[Birth] [datetime] NULL ,
[EmailAdress] [nvarchar] (64) NULL ,
[Mobile] [nvarchar] (16) NULL ,
[CountryID] [int] NULL REFERENCES [dbo].[Country] ([CountryID])
)
GO
Jak łatwo zauważyć, kod tworzący tabelę jest dość intuicyjny. Jedyną obco wyglądającą linią
może być ta ostatnia:
[CountryID] [int] NULL REFERENCES [dbo].[Country] ([CountryID])
Informuje ona serwer o tym, iż wartość CountryID w tabeli Student jest wartością, która musi
się znajdować w tabeli Country w kolumnie CountryID. Podczas wstawiania nowego studenta
system sprawdza czy identyfikator kraju znajduje się w tabeli Country. Gdybyśmy mieli w tabeli
z krajami następujące wpisy:
To próba dodania następującego rekordu:
Autor: Grzegorz Chuchra Strona 41
www.CentrumXP.pl
zakończyłaby się niepowodzeniem. Przyczyną tego jest fakt, iż w tabeli Country nie mamy
kraju z CountryID = 7. Aby operacja zakończyła się powodzeniem, musielibyśmy najpierw
wstawić do zbioru krajów rekord:
Teraz bez problemu możemy umieścić w naszym repozytorium wpis dotyczący pana
nazywającego się Grzegorz Chuchra.
Po utworzeniu struktury danych opisujących studentów czas przejść do tabeli
charakteryzującej przedmioty.
CREATE TABLE [dbo].[Subject]
(
[SubjectID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[SubjectName] [nvarchar] (64) NOT NULL ,
[Type] [int] NULL
) GO
Skrypt tworzący jest analogiczny do tego, który posłużył nam do utworzenia tabeli zawierające
dane o krajach. Dodaliśmy tylko pole z możliwością wpisania rekordu bez uzupełnienia tej
wartości. Pole Type, bo o nim tu mowa, charakteryzuje rodzaj kursu - czy jest to zaliczenie,
wykład, laboratoria itd.
Przechodzimy teraz do encji związanej z danymi o lektorach:
CREATE TABLE [dbo].[Lector]
(
[LectorID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[Firstname] [nvarchar] (64) NOT NULL ,
[Lastname] [nvarchar] (64) NOT NULL ,
[EmailAdress] [nvarchar] (64) NULL ,
[Mobile] [nvarchar] (16) NULL ,
[Phone] [nvarchar] (16) NULL ,
[NIP] [nvarchar] (16) NULL ,
[Salary] [float] NULL
)
GO
Autor: Grzegorz Chuchra Strona 42
www.CentrumXP.pl
Po dokładnym przeanalizowaniu wcześniejszych skryptów wydaje mi się, że nie ma potrzeby
rozwodzenia się nad tabelą Lector. Jest ona tożsama do Student z dokładnością do danych
dotyczących wynagrodzenia i związanego z nim NIP-u.
Jeśli już opanowaliśmy tworzenie prostych relacji między obiektami bazodanowymi czas
przejść do najbardziej skomplikowanej struktury w naszej bazie. Sam opis tabeli i charakter jej
funkcjonowania został zawarty w rozdziale  Klucze główne, obce. Asocjacji. Tu tylko
ograniczę się do napisania skryptu, co jest znacznie prostszą operacją, aniżeli
zaprojektowanie takiej struktury.
CREATE TABLE [dbo].[StudentLectorSubject]
(
[StudentID] [int] NOT NULL REFERENCES [dbo].[Lector] ([LectorID]),
[LectorID] [int] NOT NULL REFERENCES [dbo].[Student] ([StudentID]),
[SubjectID] [int] NOT NULL REFERENCES [dbo].[Subject] ([SubjectID]),
[Mark] [float] NULL,
PRIMARY KEY ([StudentID], [LectorID], [SubjectID])
)
Jeśli już wiemy jak napisać skrypt tworzący bazę, chciałbym abyśmy przeszli do utworzenie
tego samego za pomocą Management Studio. Jak zaraz zobaczymy jest to znacznie
przyjemniejsze, a dodatkowo w każdym momencie możemy automatycznie wygenerować kod
tworzący poszczególne obiekty, jak i całą bazę danych. Przedstawię, jak utworzyć strukturę
dla tabelki Country, ponieważ utworzenie pozostałych tabel odbywa się na identycznej
zasadzie, więc wydaje mi się, że nie ma potrzeby rozwodzenia się nad tym zagadnieniem. Ale
koniec gadania - przechodzimy do pracy.
Standardowo zaczynamy od rozwinięcia katalogu z bazami danych istniejącymi na naszym
serwerze. Odszukujemy bazę Deanery, naciskamy prawym guzikiem myszy na katalogu
Tables. Z menu podręcznego wybieramy New Table& i przepisujemy kolejno dane ze
skryptów. Jako pierwszą dodaliśmy encję Country, więc edycję rozpoczniemy właśnie od tej
tabeli. Wpisujemy do pierwszej komórki zatytułowanej Column Name wartość CountryID, do
Column Type int, a ostatnie pole checkBox pozostawiamy odznaczone. Tym sposobem
wprowadziliśmy już większą część informacji dotyczącej kolumny. Teraz musimy przejść do
zakładki Column Properties gdzie włączamy opcję autoinkrementacji (kolejnego nadawania ID
dla klucza głównego). U mnie wygląda to następująco:
Autor: Grzegorz Chuchra Strona 43
www.CentrumXP.pl
Podsumowanie
W rozdziale tym zapoznaliśmy się z zagadnieniem implementacji bazy danych. Wydaje mi się,
że tworzenie bazy danych jest operacją mało skomplikowaną. Skrypty SQL ograniczają się do
zapoznania z niewielką liczbą komend, a dodatkowym atutem jest możliwość skorzystania z
narzędzi takich jak Microsoft SQL Server Management Studio wspomagających implementację
zarówno z poziomu skryptów, jak i za pomocą interfejsu graficznego.
Autor: Grzegorz Chuchra Strona 44
www.CentrumXP.pl
10. Operacje na danych "Data Manipulation
Language".
W artykule tym zajmiemy się językiem operacji na danych. Przedstawimy instrukcje
wchodzące w skład Data Manipulation Language(DML), bo o nim tutaj mowa, zrobimy kilka
przykładowych operacji na danych zawartych w bazie AdventureWorks oraz pokażemy, w jaki
sposób wykorzystać narzędzia ułatwiające tworzenie skryptów.
Data Manipulation Language służy do pracy z danymi zgromadzonym w systemie
bazodanowym. Dzięki wykorzystaniu wyrażeń DML możemy operować na danych - skutecznie
je zmieniać i przeglądać. DML składa się z czterech podstawowych komend:
Najprostszy z możliwych skryptów z wykorzystaniem języka zapytań jaki można wykonać to:
SELECT *
FROM [Person].[Contact]
Zwraca listę wszystkich rekordów jakie zawarte są w tabeli Contact. Słowo kluczowe SELECT
określa rodzaj operacji jaką będziemy wykonywać, * daje informacje o tym, iż pobieramy
wszystkie kolumny należące do danej tabeli, kolejne słowo kluczowe FROM określa, z jakiej
tabeli będziemy pobierać dane, a następnie podajemy nazwę tej tabeli. W SQL Server 2005
musimy przed nazwą tabeli podać dodatkowo nazwę schematu. Schemat jest związany z
bezpieczeństwem bazy danych. Każdy obiekt bazodanowy należy do któregoś ze schematów.
Jeśli nie przypiszemy jawnie schematu podczas tworzenia tabeli, procedury składowanej itp. to
domyślnie umieszczona zostanie w schemacie [dbo]. Szerzej o schematach powiemy sobie w
kolejnych publikacjach dotyczącej polityki bezpieczeństwa i języka Data Control
Language(DCL).
Ponieważ wychodzę z założenia, że jeśli coś można wyklikać, nie ma potrzeby tego pisać,
większość przykładów będziemy tworzyć w narzędziu przeznaczonym do edytowania
Autor: Grzegorz Chuchra Strona 45
www.CentrumXP.pl
skryptów. Dzięki temu zaoszczędzimy sporo czasu, a przy tym będziemy mieli pewność, iż
zapytanie, które napisaliśmy, nie posiada nieprzyjemnych literówek.
Na początek spróbujmy utworzyć zapytanie, które wcześniej wszyscy widzieliśmy -
wylistowujące wszystkie rekordy z tabeli Contact. W tym celu w Microsoft SQL Server
Management Studio otwieramy nową zakładkę (naciskamy przycisk New Query). Po
załadowaniu się okna służącego do edycji własnych skryptów, pojawi się dostępna opcja
uruchomienia Design Query in Editor.
Po jej naciśnięciu przechodzimy do edytowania zapytania. Pierwszy krok to wybranie tabeli
nas interesującej, w tym przypadku będzie to tabela Contact w schemacie Person.
Gdy już wybraliśmy tabelę z listy, możemy przejść do edytowania zapytania. Teraz wystarczy
wcisnąć checkbox-a opisanego (* All Columns) oraz potwierdzić wciskając OK.
Autor: Grzegorz Chuchra Strona 46
www.CentrumXP.pl
Jak łatwo zauważyć, mamy wygenerowane zapytanie praktycznie identyczne jak to powyżej. Jedyna
różnica polega na tym, że tu mamy gwiazdkę poprzedzoną nazwą tabeli z której dane chcemy wybrać.
Chciałbym pokrótce teraz opisać co oznaczają poszczególne pola i w jaki sposób należy
korzystać z designera. W oknie znajdującym się w górnej części ekranu mamy umieszczoną
tabelkę/tabele wybrane z listy i to właśnie na niej będziemy przeprowadzać wszelkie operacje.
Środkowa część monitora zawiera moduł edycji zapytania. Tu będziemy określać jakie
kolumny mają się wyświetlać oraz jak te wybrane kolumny mają się nazywać. Można też
wybrać wiele innych opcji, między innymi ukrywać niechciane kolumny, grupować i wiele, wiele
innych, o których będziemy mówić w następnych rozdziałach. Widok znajdujący się w dolnej
części ekranu jest rezultatem operacji w module edycji zapytania. Ważnym aspektem całego
designera jest fakt, że wszystkie widoki są ze sobą ściśle związane. Zmiana aliasu kolumny w
części środkowej spowoduje, iż zostanie wygenerowany kod odpowiedzialny za taką akcję.
Działa to również w drugą stronę. Jeśli napiszemy kod w oknie znajdującym się w dolnej
części monitora, odpowiedzialnej za generowanie skryptu, to automatycznie w środkowej
pojawią się zmiany odwzorowujące wprowadzony skrypt (o ile nie pojawią się w nim błędy).
Przejdz
my jednak do stworzenia zapytania edytującego dane już istniejące w tabeli Contact.
Powtarzamy kroki tak jak w pierwszym przykładzie. Kiedy mamy już włączonego designera
musimy zmienić rodzaj zapytania. Standardowo włączona jest opcja SELECT, więc jeśli
Autor: Grzegorz Chuchra Strona 47
www.CentrumXP.pl
chcemy zmienić istniejący wiersz/e musimy zaznaczyć opcje UPDATE. W tym celu musimy
nacisnąć prawym guzikiem myszy na oknie designera, a następnie z menu podręcznego
wybrać opcje Change Type i Update. Po dokonaniu wyboru w dolnym oknie możemy
zauważyć, jak zmienił się wygenerowany skrypt. Z opcji SELECT zmieniła się na UPDATE.
Teraz należy podać co i na jaką wartość chcemy zmienić. Podobnie jak we wcześniejszym
przykładzie zaznaczamy checkbox z tą różnicą, że teraz nie wybieramy wszystkich kolumn
tylko pojedynczą wartość FirstName. Będziemy chcieli zmienić imiona wszystkich Michele na
Michał. W środkowym oknie klikamy w pierwszym wierszu na kolumnie New Value i wpisujemy
Michał, następnie w kolumnie Filter wpisujemy starą wartość Michele. Teraz wystarczy już
tylko wcisnąć przycisk OK i zapytanie jest już zrobione. Ponieważ nie omawialiśmy wcześniej
składni DML do modyfikacji danych, więc na przykładzie wygenerowanego kodu opiszę jak to
działa.
UPDATE Person.Contact
SET FirstName = N'Michał'
WHERE (FirstName = N'Michele')
Pierwsze słowo kluczowe, podobnie jak przy wyrażeniu SELECT, określa rodzaj operacji, po
której występuje nazwa tabeli, którą chcemy edytować. Koleje słowo kluczowe określa listę
kolumn, które będziemy edytować i wartość, jaką chcemy przypisać do danej kolumny. Jeśli
zakończylibyśmy edycję po pierwszych dwóch liniach, skrypt zmodyfikowałby wszystkie imiona
w tabeli Contact na Michał. Takie zachowanie nie jest zgodne z naszym intencjami dlatego
ograniczamy zbiór danych do osób o imieniu Michele. Jeśli w jednym zapytaniu chcielibyśmy
zmienić automatycznie pole związane z datą modyfikacji, wystarczy do naszego zapytania
dodać po przecinku nazwę kolejnej zmienianej kolumny oraz wartość analogicznie jak we
wcześniejszym przykładzie.
Autor: Grzegorz Chuchra Strona 48
www.CentrumXP.pl
Tu zmieniamy datę modyfikacji wiersza, przy okazji wykorzystujemy jedną z funkcji SQL
Severa pobierającą datę.
UPDATE Person.Contact
SET FirstName = N'Michał', ModifiedDate = GETDATE()
WHERE (FirstName = N'Michele')
Potrafimy już wyświetlić dane i je z edytować. Przejdz
my teraz do operacji wstawiania nowych wierszy.
Ponownie włączamy designera, tylko zamiast tabeli Contact wybieramy CountryRegione. Kolejny krok
jest identyczny, jak podczas operacji UPDATE. Zmieniamy rodzaj zapytania z SELECT na INSERT
VALUES. Czyli najpierw naciskamy prawym guzikiem myszy na okienku designera i z menu
podręcznego wybieramy opcję Change Type, a następnie Insert Values.
Autor: Grzegorz Chuchra Strona 49
www.CentrumXP.pl
Dodanie nowego regionu jest już dziecinnie proste. Zaznaczamy w tabeli pola, które chcemy
wstawiać, a następnie w środkowym module wstawiamy wartości dla odpowiednich kolumn.
Tutaj również użyłem funkcji do automatycznego wstawienia daty GETDATE().
INSERT INTO Person.CountryRegion
Name, ModifiedDate)
VALUES N'Polska', GETDATE())
Składnia wyrażenia jest następująca: słowo kluczowe INSERT określające, że mamy do
czynienia z operacją wstawiania. Po nim następuje słowo kluczowe INTO, po którym
wstawiamy informacje, do jakiej tabeli chcemy wstawić wiersz oraz w jakiej kolejności
będziemy wstawiać wartości. Wartości są wylistowane po słowie kluczu VALUES i w naszym
przypadku jest to nazwa kraju oraz aktualna data. Jeśli byśmy zmienili kolejność w nazwie
kolumn, to aby wyrażenie mogło się wstawić musielibyśmy zmienić kolejność wartości po
klauzuli VALUES.
INSERT INTO Person.CountryRegion
(ModifiedDate, Name)
VALUES (GETDATE(), N'Polska')
Autor: Grzegorz Chuchra Strona 50
www.CentrumXP.pl
Z podstawowych operacji jakie możemy dokonać na repozytorium zostało nam jeszcze
usuwanie. Po tym jak wstawiliśmy do bazy danych wiersz z Polska przydałoby się go teraz
wykasować. Intuicja każdemu z nas już podpowiada, co powinniśmy zrobić. Tak więc
włączamy designera wybieramy tabelę CountryRegion, a po załadowaniu wybieramy z menu
podręcznego opcje DELETE. Tym razem jednak pojawia się pewna różnica. Na tabeli nie ma
możliwości zaznaczenia checkbox. Jest to spowodowane faktem, iż operacja usuwania działa
na całych wierszach, a nie na kolumnach. Sprawia to, że funkcjonalnie nie ma potrzeby
zaznaczania pojedynczych komórek. Chcąc usunąć wiersz gdzie nazwą regionu jest Polska
musimy dodać klauzulę WHERE. Klikamy więc w środkowym oknie designera na kolumnę
Columns i wybieramy z niej Name teraz w Filter wpisujemy Polska i gotowe.
A oto wygenerowany skrypt:
DELETE FROM Person.CountryRegion
WHERE (Name = N'Polska')
Autor: Grzegorz Chuchra Strona 51
www.CentrumXP.pl
Podsumowanie
Nauczyliśmy się jak w dość łatwy i przyjemny sposób tworzyć skrypty modyfikujące i
wyświetlające dane z naszego serwera bazodanowego. Pokazałem, jak dzięki wykorzystaniu
Design Query in Editor szybko zmieniać typy zapytań, tworzyć nowe skomplikowane zapytania
i edytować już istniejące. Dla takich małych skryptów jak te wyżej przedstawione zysk czasowy
jest niewielki, ale przy zapytaniach sięgających setek linii korzystanie z narzędzia wyżej
opisanego znacznie usprawnia i przyspiesza pracę.
11. Filtracja danych
Streszczenie
Artykuł ten chciałbym poświęcić klauzuli WHERE. Ponieważ do tej pory traktowaliśmy ją
bardzo ogólnikowo, myślę, że przyszedł czas na dokładne przeanalizowanie możliwości,
których może nam dostarczyć skuteczne jej wykorzystanie.
Treść
Klauzula WHERE służy do ograniczenia zbioru danych, na których przeprowadzana będzie
operacja. Dotyczy to zarówno operacji SELECT, jak i UPDATE i DELETE. W warunkach
wyszukiwania można uwzględnić szereg operatorów porównania, dopasowywania łańcuchów
znaków i wartości niezdefiniowanej NULL. Sama składnia ograniczająca warunki
wyszukiwania jest dość prosta:
Podstawowy sposób filtracji danych polega na pobieraniu wartości większy, mniejszy lub
równy i porównaniu z pewną wartością, którą podajemy. Przykładem może być wybranie
kontaktu z osobami o imieniu Marcin lub ludzi urodzonych po roku 1980. Wszystkie te operacje
można bez problemu wykonać za pomocą operatorów porównania. Poniższa tabela
przedstawia operatory porównania dostępne w standardzie Transact-SQL.
Autor: Grzegorz Chuchra Strona 52
www.CentrumXP.pl
Przejdz
my teraz do przeanalizowania prostego zapytania, które już wielokrotnie przetoczyło
się przez ten kurs. Wygenerujmy sobie w Query Designer skrypt wylistowujący kontakty do
osób mających nazwisko Diaz. Poprawnie wygenerowane zapytanie powinno wyglądać w
następująco:
SELECT Person.Contact.*
FROM Person.Contact
WHERE (LastName = N'Diaz')
Skupmy się jednak na klauzuli WHERE. Kolumna LastName jest typu znakowego, z tego
względu tekst wpisany do designera jest standardowo konwertowany do łańcuch znakowy. W
tym celu SQL Server zastosował funkcję konwersji:
N' wartość_do_konwersji 
Sprawdz
my, co się stanie, jeśli kolumna, po której będziemy wyszukiwać, będzie miała
wartość integer (całkowitoliczbową).
SELECT Person.Contact.*
FROM Person.Contact
WHERE (ContactID = 257)
Ponieważ kolumna ContactID jest typu całkowitego designer nie musi, a nawet nie może
konwertować wartości. Próba wpisania w kolumnie Filter wyrażenia innego niż cyfra zakończy
się niepowodzeniem i wypisaniem komunikatu o błędzie.
Wiemy już jak wybierać informacje o pojedynczym wierszu. Teraz przejdz
my do wybrania
Autor: Grzegorz Chuchra Strona 53
www.CentrumXP.pl
wszystkich rekordów z bazy, gdzie data modyfikacji wiersza jest większa niż 2004-07-26. W
tym celu przy kolumnie związanej z nakładaniem warunków wpisujemy datę 2004-07-26.
SELECT Person.Contact.*
FROM Person.Contact
WHERE (ModifiedDate > CONVERT(DATETIME, '2004-07-26 00:00:00', 102))
Zapytanie, które dostaliśmy jako wynik naszego ograniczenia, jest dość skomplikowane.
Spowodowane jest to tym, iż kolumna, na którą nakładamy warunki jest typu datetime.
Podobnie jak w przypadku łańcucha znakowego, designer konwertuje tekst wpisany przez
użytkownika do formatu takiego, jaki ma kolumna. Konwersja ta jest jednak znacznie bardziej
skomplikowana i używam do niej systemowej funkcji CONVERT. Funkcja ta przyjmuje jako
pierwszy argument typ na jaki konwertujemy. Drugim argumentem jest obiekt, którego typ
chcemy zmienić, zaś ostatni parametr określa styl podawania daty. W zależności, czy rok
oddzielimy od miesiąca myślnikami czy kropkami musimy podać stosowny do tego sposób
kodowania. Tabelę określającą odpowiednie formaty zamieściłem poniżej.
Autor: Grzegorz Chuchra Strona 54
www.CentrumXP.pl
Wiemy już, jak przeszukiwać tabelę po typie całkowitym, znakowym oraz po dacie. Spróbujmy
teraz złożyć wszystko to razem i zapisać zapytanie wybierające wszystkie kontakty, które
zostały zmodyfikowane po 21 lipca 2004 dla osób z nazwiskiem Diaz lub tą, która ma
identyfikator równy 13365. Takiego zapytania jeszcze nie pisaliśmy. Mamy tu do czynienia z
dwoma różnymi operatorami logicznymi pomiędzy poszczególnymi warunkami AND, OR.
Pierwszą operacją jest wybranie z tabeli pól, jakie będą nam potrzebne, czyli LastName,
ModificationDate i ContactID. Jeśli mamy już wybrane pola przechodzimy do określania
warunków. Zacznijmy od wyznaczenia zbioru rekordów, które zostały zmodyfikowane po 2004-
07-21. Czyli w kolumnie Filter wpisujemy naszą datę poprzedzoną znakiem >. Teraz
dokładamy warunek ograniczający zbiór rekordów do osób z nazwiskiem Diaz. Na tą chwilę
zapytanie wygląda następująco:
SELECT Person.Contact.*
FROM Person.Contact
WHERE (ModifiedDate > CONVERT(DATETIME, '2004-07- 21 00:00:00', 102)) AND (LastName = N'Diaz')
Teraz musimy dodać warunek, który dołoży do naszego zbioru rozwiązań kontakt z
identyfikatorem 13365. W tym celu do klauzuli WHERE dodajemy:
OR (ContactID = 13365)
Całe zapytanie wygląda tak:
SELECT LastName, ModifiedDate, ContactID
FROM Person.Contact
WHERE (ModifiedDate > CONVERT(DATETIME, '2004-07-21 00:00:00', 102)) AND (LastName = N'Diaz') OR
(ContactID = 13365)
Jednak przeważnie podczas wyszukiwania nie znamy całego nazwiska lub imienia (albo po
prostu nie chce nam się całego wpisywać) wtedy musimy nieco zmodyfikować nasze
zapytanie. Jeśli chcielibyśmy wyświetlić listę osób, których imię zaczyna się na literę A zaś
nazwisko na S powinniśmy użyć słowo kluczowe LIKE. Składnia wyrażenia jest następująca:
WHERE nazwa_kolumny [NOT] LIKE  szukane_tekst
a zapytanie wygląda w następujący sposób:
SELECT FirstName, LastName
FROM Person.Contact
WHERE (FirstName LIKE N'A%') AND (LastName LIKE N'S%')
Autor: Grzegorz Chuchra Strona 55
www.CentrumXP.pl
Znak % jest znakiem autouzupełnienia. Mówi o tym, że po naszej literze może występować
dowolny ciąg różnych znaków. Gdybyśmy przed literą A dodali znak % oznaczałoby to, że
szukamy takich imion, które posiadają literę A nie zważając na pozycję na której ona
występuje. Lista innych znaków autouzupełnienia przedstawia poniższa tabela.
Jeśli chcielibyśmy znalez
ć osobę, której nazwisko zaczyna się na literę D, po niej występują
dwie inne litery, następnie litera Z i ciąg innych liter musielibyśmy uruchomić następujący
skrypt.
SELECT FirstName, LastName
FROM Person.Contact
WHERE (LastName LIKE N'D__Z%')
Podsumowanie
W powyższym artykule dowiedzieliśmy się, jak w sposób efektywny ograniczać zbiór danych
wynikowych ze względu na nasze potrzeby. Wszystkie skrypty, które utworzyliśmy były
przedstawione na podstawie operacji SELECT jednak można bez żadnych przeszkód użyć ich
zarówno w operacjach UPDATE jak i DELETE. Składnia klauzuli WHERE jest we wszystkich
przypadkach taka sama.
12. Funkcje agregujące
Temat artykułu jest dość ważnym zagadnieniem w teorii baz danych i na pierwszy rzut oka
dość zagmatwanym. Zrozumienie agregatów i sposobu ich działania jest zasadniczą kwestią,
dlatego mam nadzieję, że wszyscy bez problemów opanują poniższy materiał.
Funkcje agregujące zwracają podsumowania dla całej tabeli lub grupy wierszy w tabeli.
Autor: Grzegorz Chuchra Strona 56
www.CentrumXP.pl
Podsumowaniem może być suma, średnia, ilość wierszy czy nawet wariancja z tabeli lub
określonej grupy rekordów.
SELECT COUNT(*) AS 'ContactsCount'
FROM [Person].[Contact]
Ta najprostsza z funkcji agregujących zlicza ilość wierszy w zbiorze wynikowym. Jeśli jednak
dodalibyśmy klauzule ograniczającą WHERE, zbiór wynikowy uległby zmianie i podsumowanie
również miałoby inną wartość.
SELECT COUNT(*) AS 'AContactsCount'FROM [Person].[Contact]
WHERE [FirstName] LIKE 'A%'
Jak widać liczba osób, których imię zaczyna się na literę A stanowi ok. 10% wszystkich osób w
tabeli Contact.
Przykłady te wprowadziły nas w obszar funkcji agregujących i ich natury działania na zbiorach
danych. Przejdz
my teraz do ciekawszego przykładu. Przypuśćmy, że chcemy znalez
ć w
naszej bazie danych listę dziesięciu najbardziej popularnych nazwisk. Przeprowadz
my
najpierw mały proces myślowy, mający na celu wyszczególnić kroki, jakie są niezbędne aby
otrzymać wynik. Na początku wypadałoby wylistować wszystkie nazwiska, jakie mamy w bazie
danych. Następnie zliczyć wystąpienia każdego z tych nazwisk. Gdy już będziemy posiadać
wyniki tych operacji, posortujemy je w kolejności malejącej i odetniemy te, które nie mieszczą
się w pierwszej dziesiątce. Przystąpmy więc do zadania.
Aby wylistować wszystkie grupy nazwisk w bazie danych musimy użyć specjalnej klauzuli
GROUP BY. Klauzula ta grupuje dane podsumowania, które spełniają warunki zawarte w
Autor: Grzegorz Chuchra Strona 57
www.CentrumXP.pl
klauzuli WHERE (lub z całej tabeli, jeśli nie posiadamy klauzuli WHERE), aby zostały
zwrócone w postaci pojedynczego wiersza. GROUP BY zbiera w jeden wiersz zbiór
spełniający wymagania.
SELECT [LastName]
FROM [Person].[Contact]
GROUP BY [LastName]
Jak łatwo zobaczyć, liczba wierszy wynikowych po dodaniu GROUP BY uległa znacznemu
zmniejszeniu. Jest to spowodowane tym, że każda z pozycji, które do tej pory występowały
wielokrotnie, ograniczona została do jednego rekordu. Ale co to nam daje? Ano to, iż teraz
wystarczy tylko dodać kolejną funkcję agregującą COUNT i mamy już prawie to, co chcieliśmy
uzyskać.
SELECT COUNT([LastName]), [LastName]
FROM [Person].[Contact]
GROUP BY [LastName]
Funkcja COUNT użyta w powyższym zapytaniu, pomimo iż wygląda podobnie, różni się od tej
użytej w pierwszym skrypcie. COUNT(*) zlicza wystąpienie wybranych wierszy, gdy
COUNT(nazwa_kolumny) zwraca liczbę wartości w wyrażeniu, z wszystkich kolumn lub z
różnych wartości kolumn.
Teraz do naszego zapytania wystarczy zastosować tylko parę zabiegów kosmetycznych i
mamy już gotowy wynik.
SELECT TOP 10 COUNT([LastName]), [LastName]
FROM [Person].[Contact]
GROUP BY [LastName]
ORDER BY 1 DESC
W celach wyjaśnienia: opcja TOP 10 przy klauzuli SELECT ogranicza zbiór wynikowy do
dziesięciu pierwszych wierszy.
Jeśli chcielibyśmy ograniczyć wyniki do trzech wystarczyłoby w miejsce 10 wpisać 3 i już
mamy gotowy wynik. Dodatkową klauzulą, z którą do tej pory nie mieliśmy kontaktu, jest
ORDER BY. Nie robi ona nic specjalnie ciekawego, za wyjątkiem tego, że porządkuje
zapytanie w kolejności rosnącej (domyślnie, jeśli nie podamy parametru) ASC lub malejącej
DESC (tak jak jest to w naszym przypadku). Dodatkowym parametrem jest kolumna, po której
będzie odbywało się sortowanie. W naszym przypadku chcemy, aby sortowanie odbyło się po
Autor: Grzegorz Chuchra Strona 58
www.CentrumXP.pl
ilości wystąpień nazwiska, czyli po pierwszej kolumnie. Jeśli jednak chcielibyśmy posortować
wynik w kolejności alfabetycznej, musielibyśmy zmienić kolumnę sortowania na 2, jednakże
wynik naszego zapytania uległby zniekształceniu. Jak rozwiązać ten problem, dowiemy się w
jednym z następnych artykułów.
Jeśli już umiemy obliczać ilość poszczególnych wystąpień w zbiorze wynikowym oraz
rozumiemy, na jakiej podstawie grupowane są dane, przejdz
my do operacji troszeczkę innego
typu. Za pomocą funkcji agregujących możemy bez problemu obliczyć średnią wagę
produktów dostępnych w bazie danych Adventureworks, lub średni czas jaki produkt jest
dostępny w sklepie. Skrypt, który to obliczy wygląda następująco
SELECT AVG([Weight]) * 0.453592 AS 'AverageWeight'
FROM [Production].[Product]
WHERE [Weight] IS NOT NULL
Ponieważ wartość wagi w bazie danych jest zapisana w funtach użyliśmy stałej 0.453592 aby
przeskalować wartość na kilogramy.
Teraz przejdz
my do skryptu podsumowującego nasze zmagania z funkcjami agregującymi.
Pozwoliłem sobie jeszcze nieco uszczegółowić treść zapytania, które przedstawiłem przed
chwilą. Treść brzmi następująco:  wypisz dwa kolory produktów, które znajdowały się najdłużej
w sprzedaży wraz ze średnią ilością dni w sprzedaży . Zapytanie brzmi dość skomplikowanie i
troszeczkę niejasno. Chodzi w nim mniej więcej o to, by wyszukać wszystkie produkty
określonego koloru i policzyć dni przez ile były dostępne. Następnie ze zbioru wybieramy
produkty takiego koloru, który średnio utrzymał się najdłużej w sprzedaży. Zapytanie wygląda
w sposób następujący.
SELECT TOP 2 [Color], AVG(DATEDIFF(dd, [SellStartDate], [SellEndDate])) AS 'AverageDaysInSales'
FROM [Production].[Product]
WHERE [SellEndDate] IS NOT NULL AND [SellStartDate] IS NOT NULL AND [Color] IS NOT NULL
GROUP BY [Color]
Teraz, kiedy już widzimy rozwiązanie, pora przyjrzeć się mu dokładnie. Jedyną nową rzeczą, z
którą się tu można spotkać jest systemowa funkcja serwera obliczająca różnicę pomiędzy
datami.
DATEDIFF(typ_czasu, data_początkowa, data_końcowa)
Autor: Grzegorz Chuchra Strona 59
www.CentrumXP.pl
Oprócz dat pobiera ona dodatkowo jako argumenty typ czasu w jakim chcemy otrzymać wynik.
A jaśniej mówiąc to czy chcemy mieć różnice obliczoną w dniach (dd), miesiącach(MM) czy
latach (yyyy). Ponieważ w treści zadania jest napisane, że dodatkowo chcemy wyświetlić ilość
dni, przyjmujemy dzień jako przedział do naliczania różnicy. Następnie wyliczamy średnią ilość
dni i zadanie zostało rozwiązane.
Przejdz
my teraz do ostatniej modyfikacji naszego przykładu. Przypuśćmy, że interesują nas
tylko te produkty, których średnia ilość dni w sprzedaży jest większa niż 400. Do rozwiązanie
tego zadania potrzeba nam będzie jeszcze jedna bardzo ważna klauzula HAVING. Klauzula ta
determinuje, które wiersze zostaną zwrócone przez klauzulę GROUP BY. Ważne jest, aby
dobrze zrozumieć zasadę współpracy między funkcjami, a klauzulami SQL'a WHERE oraz
HAVING. Podstawową różnicą między WHERE i HAVING jest to, że WHERE filtruje wiersze
przed grupowaniem i obliczeniami (decyduje, które wiersze wejdą do obliczeń funkcji
agregujących), podczas gdy HAVING selekcjonuje wiersze już pogrupowane, po wykonaniu
obliczeń. Dlatego klauzula WHERE nie może zawierać funkcji agregujących, ponieważ w
gruncie rzeczy nie ma to sensu. Z drugiej strony, klauzula HAVING zawsze zawiera funkcje
agregujące. Mówiąc wprost, możesz zastosować klauzulę HAVING bez funkcji agregujących,
ale jest wtedy mniej efektywna niż WHERE z tym samym warunkiem.
Zastosowanie funkcji agregujących jest efektywniejsze, niż dodanie warunku do HAVING,
ponieważ grupowanie i obliczenia dokonywane są dla wierszy, które przeszły przez
sprawdzenie warunku w WHERE.
SELECT [Color], AVG(DATEDIFF(dd, [SellStartDate], [SellEndDate])) AS 'AverageDaysInSales'
FROM [Production].[Product]
WHERE [SellEndDate] IS NOT NULL AND [SellStartDate] IS NOT NULL AND [Color] IS NOT NULL
GROUP BY [Color]
HAVING AVG(DATEDIFF(dd, [SellStartDate], [SellEndDate])) > 400
ORDER BY 2 DESC
Podsumowanie
Funkcje agregujące są potężnym narzędziem w relacyjnych bazach danych, bez którego nie
można wyobrazić sobie pracy. Poprawne zrozumienie i świadome korzystanie z agregatów
jest podstawą w pracy zarówno w mały bazach danych, jak i w dużych systemach
bazodanowych, gdzie skomplikowane raportowanie jest jedną z najważniejszych ról serwera
bazodanowego.
Autor: Grzegorz Chuchra Strona 60
www.CentrumXP.pl
13. A
ączenie tabel
Pobieranie danych z kilku niezależnych tabel jest rzeczą tak oczywistą, że można się zdziwić
dlaczego zajmujemy się tą kwestią tak póz
no. A to dlatego, że aby poprawnie używać złączeń,
musieliśmy zrozumieć, na jakiej zasadzie działa mechanizm wybierania danych z
pojedynczych tabel oraz jak efektywnie formatować przedstawione wyniki.
Na początku chciałbym przybliżyć wszystkim czym tak naprawdę są złączenia. Są niczym
innym, jak spojeniem danych z kilku tabel w jedną tabelę wynikową. Realizowane są poprzez
porównanie jednej lub kilku kolumn z jednej tabel z innymi kolumnami znajdującymi się w
drugiej tabeli. Najlepiej jednak zademonstrować to na przykładzie.
Jeśli chcielibyśmy zobaczyć jakie stanowisko w firmie piastuje dana osoba, musielibyśmy
połączyć tabelkę Contact z Employee. W tabeli Employee mamy dane dotyczące stanowiska
w firmie (kolumna Title), ale nic nam z tych danych, jeśli nie będziemy wiedzieli kogo one
dotyczą. Tą informację z kolei mamy w tabeli Contact. Wiemy jedynie, że częścią wspólną jest
tutaj kolumna ContactID. Jest ona kluczem głównym w tabeli Contact i obcym w Employee. Ta
informacja wystarcza nam, aby wydobyć interesujące nas informacje.
SELECT Person.Contact.FirstName, Person.Contact.LastName, HumanResources.Employee.Title
FROM Person.Contact INNER JOIN
HumanResources.Employee ON Person.Contact.ContactID = HumanResources.Employee.ContactID
Wynikiem zapytania jest tabela przyporządkowująca każdemu kontaktowi stanowisko, jakie
osoba posiadająca dany kontakt piastuje. Składnia złączenia na pierwszy rzut oka nie jest
zachęcająca, ale można tu zauważyć pewien szablon. W sekcji FROM znajduje się nazwa
pierwszej tabeli którą chcemy połączyć, potem następuje słowa kluczowe INNER JOIN i
nazwa drugiej tabeli. Następnie następuje znacznik ON i najważniejszy fragment złączenia
polegający na wypisaniu kolumn, po których i w jaki sposób chcemy złączać tabele. W
przypadku poprawnie znormalizowanej i zaprojektowanej bazy danych zdecydowana
większość złączeń będzie odbywała się po kluczach głównych i obcych. Gwarancją takich
złączeń jest szybkie wykonanie selektów, a co za tym idzie prezentacji wyników.
Bardzo istotną informacją jest fakt, że nasza tabela wynikowa posiada wszelkie cechy zwykłej
tabeli. Możemy dowolnie wybierać kolumny, które chcemy wyświetlać, zmiana dotyczy sytuacji
gdy kolumny się powtarzają - wtedy należy poprzedzać je nazwą tabeli. Możemy dokonywać
operacji filtracji, grupowania oraz używać klauzuli ORDER BY. Dodatkowo można używać
Autor: Grzegorz Chuchra Strona 61
www.CentrumXP.pl
złączeń dowolną ilość razy. Jedynym ograniczeniem jest szybkość działania zapytania.
Sprawdz
my więc jaka była historia zatrudnienia w firmie pani Sheela Word.
SELECT Person.Contact.FirstName, Person.Contact.LastName, HumanResources.Employee.Title,
HumanResources.Department.Name, HumanResources.EmployeeDepartmentHistory.StartDate,
HumanResources.EmployeeDepartmentHistory.EndDate
FROM Person.Contact INNER JOIN
HumanResources.Employee ON Person.Contact.ContactID = HumanResources.Employee.ContactID INNER
JOIN
HumanResources.EmployeeDepartmentHistory ON
HumanResources.Employee.EmployeeID = HumanResources.EmployeeDepartmentHistory.EmployeeID
INNER JOIN
HumanResources.Department ON HumanResources.EmployeeDepartmentHistory.DepartmentID =
HumanResources.Department.DepartmentID
WHERE (Person.Contact.ContactID = 1037)
ORDER BY 5, 6 DESC
Wyraz
nie widać, iż w celu pobrania informacji o historii zatrudnienia musieliśmy złączyć aż
cztery tabele. W przeciwieństwie do poprzedniego wyniku, mamy tutaj nie jeden, a trzy
rekordy. Spowodowane jest to faktem, iż w tabeli EmployeeDepartmentHistory występują trzy
wiersze z ContactID równym 1037. To z kolei sprawia, że podczas złączenia
EmployeeDepartmentHistory z Employee w tabeli wynikowej tworzone są trzy wpisy.
Przykłady, które przedstawiłem powyżej są złączeniami typu INNER JOIN (złączenie
wewnętrzne). Tabela wynikowa tworzona jest poprzez sprawdzenie, czy w obu tabelach
znajdują się pasujące do siebie dane.
Innym typem złączenia jest CROSS JOIN. Tworzy on iloczyn kartezjański dwóch tabel. Rezultatem
takiego złączenia jest przyporządkowanie każdemu wierszowi z pierwszej tabeli wszystkich rekordów z
drugiej tabeli.
Autor: Grzegorz Chuchra Strona 62
www.CentrumXP.pl
Należy używać powyższego złączenia z dużą uwagą. Nieprzemyślane użycie może
doprowadzić do sytuacji gdzie serwer ulegnie zawieszeniu. Wystarczy w tym celu
przeprowadzić małą symulację. Jeśli obie tabelki miałyby zaledwie po 10 000 rekordów tabela
wynikowa złączenia typu CROSS JOIN miałaby 100 000 000!!
Kolejnym rodzajem złączeń są złączenia zewnętrzne. OUTER JOIN, bo o nich tutaj mowa,
służą do ograniczenia w zbiorze wynikowym wierszy z jednej tablicy, podczas gdy wiersze z
drugiej tablicy nie zostaną ograniczone.
Przypuśćmy, że chcemy uzyskać listę klientów wraz z numerem karty kredytowej. Jeśli
zastosowalibyśmy zwykłe wewnętrzne złączenie, nie uzyskalibyśmy informacji o tych osobach,
które nie posiadają karty kredytowej. Dzieje się tak z bardzo prostej przyczyny. Złączenie
INNER JOIN zostałoby utworzone po kolumnie CreditCardID, a dla pewnego zbioru kontaktów
nie istnieją wpisy w kolumnie ContactCreditCard. Jeśli zastosujemy złączenie zewnętrzne
uzyskamy wszystkie wartości z tabeli kontaktów, a dla tych rekordów gdzie będziemy mieli
wartość nieokreśloną otrzymamy w tabeli wynikowej NULL.
SELECT Person.Contact.FirstName, Person.Contact.LastName, ISNULL(Sales.CreditCard.CardNumber, 'BRAK')
AS CardNumber
FROM Sales.CreditCard LEFT OUTER JOIN
Sales.ContactCreditCard ON Sales.CreditCard.CreditCardID = Sales.ContactCreditCard.CreditCardID
RIGHT OUTER JOIN
Person.Contact ON Sales.ContactCreditCard.ContactID = Person.Contact.ContactID
ORDER BY 3 DESC
Dodatkowego wyjaśnienia wymaga jeszcze tylko funkcja systemowa ISNULL. Umożliwia ona
takie sformatowanie wyniku zapytania, że gdy w kolumnie pojawi się wartość NULL
automatycznie zostanie pod nią podstawiona wartość podana jako drugi argument w funkcji.
Jeśli w tym przypadku zastosowalibyśmy prawe złączenie zewnętrzne, moglibyśmy uzyskać
tabelę z wszystkimi numerami kont i przyporządkowanymi im osobami. Z tą różnicą, że jeśli
Autor: Grzegorz Chuchra Strona 63
www.CentrumXP.pl
któryś z numerów karty kredytowej nie miałby przyporządkowanego kontaktu, miałby
standardowo wpisaną wartość nieokreśloną.
Podsumowanie
Złączenia są chyba najważniejszym mechanizmem relacyjnych baz danych. Za ich pomocą
możemy wybierać skorelowane ze sobą dane z różnych tabel, przeprowadzać operacje
tworzenia raportów i wydruków.
Podstawą do efektywnego projektowania i użytkowania profesjonalnych serwerów
bazodanowych jest poprawne zrozumienie procesu tworzenia złączeń. Jeśli na tym etapie
będziemy mieli wątpliwości, powinniśmy jeszcze raz przemyśleć cały proces tworzenia złączeń
i samemu poeksperymentować z bardziej skomplikowanymi zapytaniami.
14. Praca z podzapytańami, tabelami
tymczasowymi i zmiennymi tabelarycznymi
Zapytanie SELECT może być zagnieżdżone w dowolnym z poleceń INSERT, UPDATE,
DELETE i oczywiście innym poleceniu SELECT. Większość z podzapytań może być
zrealizowana za pomocą złączeń. Jednakże nie zawsze jest to możliwe. Jeśli jednak jest taka
możliwość, powinniśmy zastosować właśnie złączenia. Poprawi to wydajność zapytania, a
przez to czas jego wykonywania.
Składnia samego podzapytania jest dość prosta. Każde z nich zamykamy w nawiasach
okrągłych i każde może zawierać dowolną ilość podzapytań. Ograniczeniem jak zwykle jest
tylko zdrowy rozsądek i możliwości naszego serwera. Czas na przykład. Napiszemy teraz
skrypt który zwraca wszystkich wiceprzewodniczących znajdujących się w firmie
AdventureWorks.
SELECT FirstName, LastName
FROM Person.Contact
WHERE (ContactID IN
(SELECT ContactID
FROM HumanResources.Employee
WHERE (Title LIKE '%Vice President%')))
Autor: Grzegorz Chuchra Strona 64
www.CentrumXP.pl
Zapytanie zagnieżdżone zwraca wszystkie kontakty z tabeli Employee w schemacie
HumanResources które piastują pozycje wiceprzewodniczącego. Następnie pytanie
zewnętrzne wybiera imię i nazwisko tych osób które mają ContactID w wynikach zapytaniach
wewnętrznego. Można było by to przetłumaczyć w następujący sposób.
SELECT FirstName, LastName
FROM Person.Contact
WHERE ContactID IN (1001, 1052, 1010)
Gdzie liczby w zbiorze (w nawiasach okrągłych) są wynikami z zapytania zwracającego
odpowiednie ContactID.
Do tego samego wyniku udałoby nam się dojść za pomocą złączeń. Alternatywne zapytanie
wyglądało by tak:
SELECT Person.Contact.FirstName, Person.Contact.LastName
FROM Person.Contact INNER JOIN
HumanResources.Employee ON Person.Contact.ContactID = HumanResources.Employee.ContactID
WHERE (HumanResources.Employee.Title LIKE '%Vice President%')
W pierwszym i drugim przykładzie podzapytania zwracały tylko jedną kolumnę. W takich
przypadkach można użyć operatorów =, <, >, <=, >=, <>. Podzapytanie może jednak zwracać
też wiele wierszy, które mogą być używane do sprawdzania istnienia wartości. W tym
przypadku używamy słowa kluczowego EXISTS w klauzuli WHERE.
SELECT FirstName, LastName
FROM Person.Contact
WHERE EXISTS
(SELECT ContactID, Title
FROM HumanResources.Employee
WHERE (Person.Contact.ContactID = ContactID) AND (Title LIKE '%Vice President%'))
W klauzuli WHERE mamy bezpośrednio podzapytanie, które zwraca tabelę z wierszami.
Polecenie EXISTS sprawdza, czy jakakolwiek wartość jest zwracana przez zapytanie
wewnętrzne. Jeśli tak, zwraca to zapytanie zewnętrzne i wypisuje imię i nazwisko, w
przeciwnym wypadku pomija rekord. Możemy tu zauważyć dość charakterystyczną dla
podzapytań klauzulę WHERE w zapytaniu wewnętrznym. Pobiera ona wartości z tabeli
zapytania zewnętrznego i porównuje je z wartością zwracaną przez podzapytanie. Przy
podawaniu ograniczeń w podzapytaniach należy pamiętać o jednej bardzo ważnej zasadzie.
Autor: Grzegorz Chuchra Strona 65
www.CentrumXP.pl
Wszystkie wartości, które wywodzą się z zapytań zewnętrznych są widoczne w zapytaniach
wewnętrznych, ale nie na odwrót! Uporządkowując ograniczenia, które tyczą się podzapytań.
1. nie może być używane w klauzuli ORDER BY (nie ma to po prostu sensu jeśli i tak
wybieramy zbiór danych)
2. zapytanie wewnętrzne musi być ujęte w nawiasy okrągłe
3. w klauzuli WHERE IN nie może znajdować się więcej niż jedna kolumna
Dla tych, którzy dopiero zaczynają przygodę z SQL podzapytania na pierwszy rzut oka nie
wydają się zbyt zachęcające. Jednakże zrozumienie mechanizmu tworzenia zapytań
zagnieżdżonych jest kwestią dość kluczową w operacjach na profesjonalnych bazach danych.
Zignorowanie tego rozdziału może niekiedy uniemożliwić skuteczne pobieranie odpowiednich
danych.
Jednakże jakby w tym celu zostały wprowadzone dwie inne struktury: tabele tymczasowe oraz
zmienne tabelaryczne. Tabele tymczasowe znajdowały się już we wcześniejszej wersji
serwera, ale zmienne tabelaryczne to jedno z usprawnień, które zostało dodane dopiero w
SQL Server 2005.
Tabele tymczasowe są strukturami, które umożliwiają przechowywanie danych z wyjścia
jednego zapytania SQL i użycia ich w następnym zapytaniu. Tymczasowa struktura istnieje w
pamięci na czas sesji z serwerem, po czym jest niszczona.
SELECT ContactID
INTO #tblTemporary
FROM HumanResources.Employee
WHERE (Title LIKE '%Vice President%')
SELECT Person.Contact.FirstName, Person.Contact.LastName
FROM Person.Contact INNER JOIN #tblTemporary ON Person.Contact.ContactID = #tblTemporary.ContactID
Należy dodatkowo, zdawać sobie sprawę, że tabela tymczasowa jest widoczna tylko dla
użytkownika, który ją stworzył i nikogo więcej. Jeśli chcielibyśmy stworzyć tabelę tymczasową,
która byłaby widoczna dla wszystkich użytkowników, należałoby umieścić dwa znaki # przed
nazwą tabeli.
Jak wcześniej wspomniałem, podobną z punktu widzenia funkcjonalności do tabel
tymczasowych strukturą są zmienne tabelaryczne. Zmienne tabelaryczne są nowym typem
Autor: Grzegorz Chuchra Strona 66
www.CentrumXP.pl
danych, który został wprowadzony w MS SQL Server 2005. Mamy teraz możliwość
zadeklarowania zmiennej, która będzie odzwierciedlała całą tabele.
DECLARE @tblTemporary TABLE(ContactID int)
INSERT INTO @tblTemporary(HumanResources.Employee.ContactID)
SELECT HumanResources.Employee.ContactID
FROM HumanResources.Employee
WHERE (Title LIKE '%Vice President%')
SELECT Person.Contact.FirstName, Person.Contact.LastName
FROM Person.Contact
WHERE Person.Contact.ContactID IN
(
SELECT ContactID
FROM @tblTemporary
)
Pierwszy etap to zadeklarowanie zmiennej typu tabelarycznego. Deklaracja odbywa się w
sposób następujący: Najpierw słowo kluczowe DECLARE, następnie nazwa zmiennej (nazwy
zmiennych w SQL Server zawsze poprzedzamy @), potem typ zmiennej. Typ zmiennej może
być oczywiście różny w zależności od potrzeby. W naszym przypadku, ponieważ użyliśmy
zmiennej tabelarycznej, musimy w zwykłych okrągłych nawiasach umieści nazwę i typ
kolumny. Jeśli chcemy, aby nasza tabela zawierała więcej niż jedną kolumnę należy oddzielić
kolejne wpisy przecinkiem. Potem używamy już zmiennej jak normalnej tabeli.
Podsumowanie
W tym rozdziale dokonaliśmy przeglądu przez cały szereg sposobów wybierania wartości z
poszczególnych tabel. Złączenia, podzapytania, tabelki tymczasowe oraz zmienne
tabelaryczne wszystkie służą do tego samego celu: pobrania odpowiednich danych w formacie
jaki jest dla użytkownika najodpowiedniejszy.
Z doświadczenia wiem, że dla większości osób które rozpoczynają zabawę z SQL
najłatwiejsze i najbardziej intuicyjne będzie wykorzystanie tabel tymczasowych do pobierania
danych, jednakże z czasem i z nabranym doświadczeniem większość osób przejdzie na
używanie złączeń połączonych z zapytaniami zagnieżdżonymi. Gorąco polecam jak
najszybsze przestawienie się i korzystanie z tej właśnie opcji. Ograniczy to ból związany z
przestawieniem się na pracę z komercyjnymi projektami.
Autor: Grzegorz Chuchra Strona 67
www.CentrumXP.pl
15. Nowości w "Data Control Language", czyli
parę słów o schematach
Schematy
Schematy w MS SQL Server 2005 nie są tak naprawdę nowością. Zalążek koncepcji
schematów pojawił się już w MS SQL Server 2000, ale dopiero teraz doczekał się on pełnej
implementacji. Ale zamiast rozwodzić się nad historią schematów przejdz
my do wyjaśnienia co
to tak naprawdę jest schemat.
We wcześniejszej wersji serwera aby użytkownik mógł wykonywać operacje na bazie danych
takie jak SELECT, INSERT, DELETE czy EXECUTE musiał mieć przydzielone prawa do
wykonywania takiej operacji na tym obiekcie. Prawa mogły mu zostać indywidualnie
przydzielone lub mógł je odziedziczyć od grupy do której należał. Czyli wystarczyło sobie
stworzyć grupę STUDENTS nadać jej prawo do operacji SELECT na tabeli w której znajdował
się ich plan zajęć i wszystko było ok. Teraz wystarczy tylko utworzyć użytkownika w SCHEMA
STUDENTS (w której również znajduje się tabela z przedmiotami) i nie trzeba się bawić z
nadawaniem dostępu. Innym bardzo dużym udogodnieniem jest możliwość łatwego kasowania
użytkowników. W SQL Server 2000 jeśli użytkownik był właścicielem obiektu nie można było
go usunąć dopóki nie został zmieniony właściciel obiektu. Jeśli więc administrator bazy danych
zmieniał pracę trzeba było się sporo namęczyć aby usunąć jego konto na serwerze.
Wymagało to przepięcia wszystkich procedur, funkcji, tabel itd. które utworzył. Teraz obiekty
bazy danych są przyporządkowane do schematu. Zaś użytkownik może mieć
przyporządkowany domyślny schemat bez potrzeby jego posiadania.
Przejdz
my więc do utworzenia pierwszego schematu. Przyporządkowanie obiektu do
schematu może nastąpić na dwa różne sposoby: jawny i niejawny. Pierwszy polega na
bezpośredni wypisaniu przed obiektem nazwy schematu tak jak to ma miejsce w skrypcie
znajdującym się poniżej.
CREATE TABLE Person.MyTable
(
MyTableID INT
, Note NVARCHAR(32)
, ModificationDate DATETIME
)
Autor: Grzegorz Chuchra Strona 68
www.CentrumXP.pl
Większość z Nas jest teraz zalogowanym do SQL Servera w trybie Windows Authentication co
spowoduje, że gdy odpalimy podobny skrypt bez jawnie określonego schematu domyślnie
doda go do schematu w którym się znajdujemy, a to oznacza DBO. Jeśli jednak byłbym
zalogowany jak użytkownik grzegorzch. Zaś Grzegorz Ch. byłby domyślnie w schemacie
Person to tabela również została by dodana do schematu Person. Wystarczyło by więc
wpisać:
CREATE TABLE MyTable
(
MyTableID INT
, Note NVARCHAR(32)
, ModificationDate DATETIME
)
Takie niejawne określanie schematu jest jednak zalecane. Trzeba być bardzo ostrożnym jeśli
chcielibyśmy używać tej formy. Można w pewnym momencie zapomnieć o schemacie, co z
pewnością nie doprowadzi do niczego dobrego.
Dodawanie użytkowników
Skoro jesteśmy zalogowani do serwera MS SQL Server 2005 (wcześniejsza wersja nie
oferowała poniższej procedury) jako administrator możemy spróbować nowego użytkownika.
W tym celu należy wykonać dwie operacje:
1. Dodać login do serwera baz danych
2. Dodać użytkownika do konkretnej bazy danych
Login jest dodawany dla każdej z osób która chce mieć dostęp do serwera. Użytkownik
dodawany jest w ramach każdej z baz danych. Ponieważ Pan Jan Kowalski może mieć dostęp
do dwóch różnych baz danych to w ramach jednego możemy udostępnić mu taką opcje.
CREATE LOGIN grzegorzch
WITH PASSWORD = 'P@ssw0rd1'
, DEFAULT_DATABASE = AdventureWorks
Domyślnie serwer nie przyjmuje haseł mało skomplikowanych. Aby udało się dodać login hasło
musi składać się z małych i duży liter oraz znaków specjalnych.
Jeśli mamy już utworzony login przejdz
my do stworzenia użytkownika.
USE AdventureWorks
GO
CREATE USER grzegorzch
FOR LOGIN grzegorzch
WITH DEFAULT_SCHEMA = dbo
Autor: Grzegorz Chuchra Strona 69
www.CentrumXP.pl
Jeśli wszystko bez problemów i udało się dodać login i użytkownika to możemy teraz przejść
do zalogowania się w trybie SQL Server Authentication. Aby się za dużo nie rozpisywać jak to
zrobić po prostu restartujmy SQL Management Studio. A kiedy pojawi się okienko logowania
wybierzmy SQL Server Authentication, login = grzegorzch, password = P@ssw0rd1 i
wciskamy Connect.
Teraz już jako grzegorzch możemy przeprowadzać operacje na bazie danych. W identyczny
sposób można dodawać użytkowników do różnych schematów. Ponieważ grzegorzch został
dodany do schematu dbo (Database Owner) ma możliwość do przeprowadzania praktycznie
wszystkich operacji na bazie danych. Jeśli jednaka chcielibyśmy stworzyć użytkownika z
możliwością tylko odczytywania danych bez sposobności do zarządzania obiektami
bazodanowymi lub zmianą danych to nie ma z tym najmniejszego problemu. Wystarczy
przydzielić użytkownika do konkretnego schematu lub roli.
Podsumowanie
W ciągu ostatnich paru lat Microsoft postawił sobie za zadanie znaczne poprawienie
bezpieczeństwa swoich produktów. Jak sprawdzi się w tej sferze SQL Server 2005 czas
Autor: Grzegorz Chuchra Strona 70
www.CentrumXP.pl
pokaże. Z perspektywy programisty oraz administratora serwera bazodanowego
wprowadzenie schematów oraz nowych procedur umożliwiających łatwiejszy pracę z kontami
użytkowników znacznie usprawniło pracę.
16. Transakcje
Rozdział ten w całości będzie poświęcony operowaniu na relacyjnych danych oraz
niebezpieczeństwu jakie występuje podczas takich operacji. Dowiemy się, jak zapobiegać
przypadkom wycieku danych lub błędom podczas operowania na zasobach repozytorium.
Aby nie opowiadać o abstrakcyjnych procesach, podam kilka przykładowych operacji, które
mogą zaburzyć spójność całego systemu i doprowadzić do jego błędnego działania.
Książkowy przykład zastosowania transakcji to przelew bankowy. Jeśli chcemy zapłacić za
zakupy w sklepie, system informatyczny musi dokonać następujących czynności:
1. sprawdzić, czy stan twojego konta pozwala na dokonanie zakupu
2. pobrać odpowiednią kwotę z twojego konta
3. przekazać pobraną sumę i przesłać ją na konto sklepu
Najważniejsze w tym wszystkim jest to, że jeśli któraś z powyższych operacji się nie powiedzie
musimy powrócić do stanu sprzed rozpoczęcia operacji.
Innym technicznym przykładem jest proces wstawiania danych. Przypuśćmy, że obsługujemy
bazę danych operatora telefonii komórkowej. Robimy rozliczenie miesięczne i podliczanie
kosztów dla poszczególnego użytkownika z danego miesiąca. Operacja na tak ogromnym
zasobie danych może trwać parę godzin. I w pewnym momencie komputer się zawiesza. Co
się dzieje z danymi? Czy dane przeliczone zostały już wstawione? A jak zostały wstawione -
czy poprawnie? Oba powyższe przykłady powinny mieć status dwuwartościowy. Albo się w
pełni poprawnie wykonały, albo w całości zostają odwołane. Do tego właśnie służy transakcja.
Do odwołania wszystkich operacji, na które została nałożona.
Teraz, kiedy troszeczkę wyjaśniłem, czym jest transakcja przejdz
my do formalnego jej
zdefiniowania.
Autor: Grzegorz Chuchra Strona 71
www.CentrumXP.pl
Definicja transakcji
Transakcja jest jednostką wykonywania, w której wszystkie polecenia są wykonane poprawnie
lub w przeciwnym przypadku nie jest wykonywane żadne z poleceń. Jest to sekwencja
operacji wykonywanych jako jedna logiczna jednostka pracy.
Każda z transakcji tworzona jest z zachowaniem pewnych standardowych własności:
Własności transakcji
Atomic  transakcja jest najmniejszą jednostką logiczną. Znaczy to, że jest wykonywana w
całości albo w całości jest odwołana
Consistent  transakcja nie zmienia spójności bazy. To znaczy, że jeśli baza była spójna przed
wykonaniem transakcji, będzie też spójna po jej ukończeniu.
Isolated  transakcja musi być izolowana, czyli nie może wchodzić w konflikty z innymi
transakcjami wykonywanymi na tym samym zbiorze danych
Durable - transakcja jest trwała, jeżeli gwarantowane jest, że wykonane działania pozostaną
kompletne bez względu na to, co się stanie z bazą po poprawnym zakończeniu transakcji.
Jeżeli wystąpi awaria zasilania i serwer bazy danych ulegnie awarii, istnieje gwarancja, że
transakcja będzie kompletna po ponownym uruchomieniu serwera.
Aby zagwarantować spójność bazy i mieć pewność, że wykonywane operacje zakończą się
sukcesem lub porażką, stosowany jest mechanizm blokowania. Blokady są gwarancją tego, że
podczas operowania na danych przez jedną transakcję dane nie zostaną zmienione bądz

usunięte.
Rozpoczęcie transakcji na SQL Server 2005 można dokonać na trzy sposoby:
Explicite  jawne rozpoczęcie transakcji za pomocą polecenia BEGIN TRANSACTION
Autocommit  automatyczne, operacje na serwerze standardowo podlegają transakcjom.
Każde z poleceń jest automatycznie zatwierdzane po poprawnym wykonaniu. Nie ma wtedy
potrzeby commitowania transakcji.
Implicit  niejawne, wywoływane przez programy użytkowe działające na bazie danych.
Zakończenie transakcji odbywa się za pomocą poleceń COMMIT lub ROLLBACK. Polecenia
COMMIT używamy, gdy wszystkie operacje od rozpoczęcia transakcji powiodły się. W takiej
sytuacji możemy zatwierdzić wszystkie zmiany, jakie zostały wprowadzone. Polecenie to
dodatkowo zdejmuje wszystkie blokady z tabel, które zostały zablokowane na czas trwania
transakcji.
Autor: Grzegorz Chuchra Strona 72
www.CentrumXP.pl
Komenda ROLLBACK odwołuje transakcję. Polecenie to odwołuje wszystkie modyfikacje jakie
zostały dokonane podczas trwania transakcji poprzez przywrócenie stanu danych sprzed
transakcji. Polecenie to, podobnie jak COMMIT, również zdejmuje wszystkie blokady z danych,
jakie zostały założone na czas transakcji.
Poniżej przedstawiony jest skrypt zmieniający imiona wszystkich Gregory i John na ich polskie
odpowiedniki. Samo zapytanie jest już dla wszystkich oczywiste. Nowością jest oczywiście
zabezpieczenie go transakcją.
BEGIN TRANSACTION
UPDATE [AdventureWorks].[Person].[Contact]
SET [FirstName] = 'Grzegorz'
WHERE [FirstName] = 'Gregory'
IF @@ERROR <> 0
BEGIN
RAISERROR ('Błąd, operacja nie udana!', 16, -1)
ROLLBACK TRANSACTION
END
UPDATE [AdventureWorks].[Person].[Contact]
SET [FirstName] = 'Jan'
WHERE [FirstName] = 'John'
IF @@ERROR <> 0
BEGIN
RAISERROR ('Błąd, operacja nie udana!', 16, -1)
ROLLBACK TRANSACTION
END
COMMIT TRANSACTION
Pierwszą operacją, jaką robimy, jest otworzenie transakcji. W tym celu pojawia się polecenie
BEGIN TRANSACTION. Następnie mamy nasz skrypt przeprowadzający operacje na danych.
Potem następuje blok kodu sprawdzający czy operacja się udała. Standardowo zmienna
systemowa @@ERROR posiada informacje z numerem ostatniego błędu. Jeśli błąd nie
wystąpił, zmienna ta ma wartość zero. Wyrażenie warunkowe IF @@ERROR <> 0 służy do
Autor: Grzegorz Chuchra Strona 73
www.CentrumXP.pl
sprawdzania, czy wystąpił błąd. Jeśli wystąpił, wchodzimy do skryptu, który go wypisze
(RAISERROR) i odwołujemy całą transakcję. Jeśli wszystko zakończyło się pomyślnie
przechodzimy do kolejnej operacji zmiany imienia i znowu sprawdzamy czy wystąpił jakiś
nieprzewidziany wypadek.
Ważne, żeby uświadomić sobie fakt, iż zwykła pojedyncza operacja UPDATE, DELTE czy
INSERT, taka jak poniżej:
UPDATE [AdventureWorks].[Person].[Contact]
SET [FirstName] = 'Jan'
WHERE [FirstName] = 'John'
nie musi być opatrzona przez nas transakcją, gdyż jest to standardowa operacja Autocommit,
która z definicji zabezpieczona jest przez transakcje.
Powracając jednak do wcześniejszych przykładów, należy pamiętać, że jeśli modyfikujemy
dane, które są ze sobą wzajemnie powiązane i niepowodzenie przy zmianie jednego zbioru
danych wiąże się z dezaktualizacją zmian na innym zbiorze danych, musimy użyć transakcji.
Na tym etapie wystarczy używać standardowego bloku kodu, który widzieliśmy powyżej.
BEGIN TRANSACTION
/*
BLOK KODU ZMIENIAJ
CY POWI
ZANE ZE SOB
DANE
*/
IF @@ERROR <> 0
BEGIN
RAISERROR ('Błąd, operacja nieudana!', 16, -1)
ROLLBACK TRANSACTION
END
COMMIT TRANSACTION
W najnowszej wersji SQL Server 2005 pojawiła się strukturalna pojawia obsługa nie
krytycznych wyjątków. Umożliwia to nieco przyjemniejszą pracę z transakcjami. Kod który
funkcjonalnie odpowiada temu z pierwszego przykładu wygląda następująco.
BEGIN TRY
BEGIN TRANSACTION
UPDATE [AdventureWorks].[Person].[Contact]
SET [FirstName] = 'Grzegorz'
Autor: Grzegorz Chuchra Strona 74
www.CentrumXP.pl
WHERE [FirstName] = 'Gregory'
UPDATE [AdventureWorks].[Person].[Contact]
SET [FirstName] = 'Jan'
WHERE [FirstName] = 'John'
COMMIT
END TRY
BEGIN CATCH TRAN_ABORT
ROLLBACK
END CATCH
Szablon bloku try-catch wygląda tak.
BEGIN TRY
BEGIN TRANSACTION
/*
KOD WYKONYWANY
*/
COMMIT
END TRY
BEGIN CATCH TRAN_ABORT
ROLLBACK
END CATCH
Podsumowanie
Transakcje są jednym z kluczowych elementów zapewniających spójność i integralność
systemów bazodanowych. W dużych systemach informatycznych nie ma mowy o
niezabezpieczeniu operacji na danych za pomocą transakcji. Niewielkie systemy, gdzie utrata
bądz
niewielkie przekłamania w danych nie czynią dużych szkód i nie wpływają na straty
finansowe, mogą sobie pozwolić taki komfort. Moim jednak zdaniem niewielki wysiłek jaki
należy włożyć w odpowiednie zabezpieczanie spójności za pomocą opisanego mechanizmu
jest małym kosztem i warto stosować politykę zabezpieczenia danych.
Autor: Grzegorz Chuchra Strona 75
www.CentrumXP.pl
17. Nowości w SQL Server 2005
Funkcje pozycjonujące
Najnowszy MS SQL Server 2005 doczekał się w końcu mechanizmu numerowania wierszy.
Może to brzmi troszeczkę niezwykle, ale wszystkie poprzednie wersje serwera były
pozbawione jakiegokolwiek mechanizmu numerowania wierszy w zbiorze wynikowym
zapytania. Oczywiście zaimplementowanie takiego mechanizmu nie było zbyt wyrachowanym
zadaniem, aczkolwiek wydaje mi się, iż tak podstawowa operacja powinna mieć standardowy
mechanizm. Tak też się stało. Mamy dostęp teraz do czterech funkcji pozycjonujących których
opisy zamieszczam w poniższej tabeli.
Teraz, aby wyświetlić kontakty do wszystkich osób posortowanie ze względu na nazwisko z
przypisanym mu numerem wiersza wystarczy uruchomić następujący skrypt.
SELECT Row_Number() OVER (ORDER BY [LastName]) AS RowNumber
,[ContactID]
,[FirstName]
,[LastName]
FROM [Person].[Contact]
Funkcja Row_Number() zwraca numer wiersza, ale wymagane jest jeszcze określenie według
jakich parametrów numer ma być nadawany. W tym celu dodajemy klauzulę OVER z
Autor: Grzegorz Chuchra Strona 76
www.CentrumXP.pl
kolumnami, po których chcemy, aby numer był wyliczany. Użycie klauzul numerujących jest jak
widać proste, trudniejszą sprawą może być wytłumaczenie, na jakiej zasadzie działają
poszczególne funkcje pozycjonujące. Aby ułatwić sobie zadanie posłużę się przykładem.
SELECT Row_Number() OVER (ORDER BY [LastName]) AS RowNumber
,Rank() OVER (ORDER BY [LastName]) AS Rank
,Dense_Rank() OVER (ORDER BY [LastName]) AS DenseRank
,NTile(3) OVER (ORDER BY [LastName]) AS NTile_3
,NTile(4) OVER (ORDER BY [LastName]) AS NTile_4
,[ContactID]
,[FirstName]
,[LastName]
FROM [Person].[Contact]
Pewnie niektórzy już myślą o dodaniu do powyższego kawałka kodu linii która wyglądać
będzie mniej więcej następująco:
WHERE (RowNumber > 51) AND (RowNumber < 100)
i dzięki temu będzie miał rozwiązany problem o nazwie  stronicowanie wyników . Niestety tak
jednak się nie stanie. Klauzula WHERE determinuje te wiersze, które mają zostać
zaznaczone, a same funkcje pozycjonujące działają na gotowych wynikach. Aby uzyskać
skrypt działający zgodnie z naszymi oczekiwaniami moglibyśmy powyższe zapytanie zrobić
zapytaniem wewnętrznym i dopiero w zewnętrznym zapytaniu użyć klauzuli WHERE.
Moglibyśmy, ale możemy również użyć wyrażeń tablicowych.
Wyrażenia tablicowe (CTE)
Wyrażenia tablicowe, jak wcześniej wspomniałem, są mechanizmem, którego nie było w
poprzedniej wersji serwera. Umożliwiają definiowanie wirtualnych widoków, których można
użyć w następnym skrypcie. Sama składnia polecenia CTE jest nie skomplikowana.
WITH nazwa_wirtualnej_tabeli
AS
(
zapytanie
)
Po wykonaniu tego zapytania mamy już dostępną wirtualną tabelę na której możemy
swobodnie przeprowadzać operacje. Powróćmy więc do utworzenia skryptu, który wybierałby
wiersze od 51  100 z tabeli Contact przy użyciu CTE.
Autor: Grzegorz Chuchra Strona 77
www.CentrumXP.pl
WITH tblContact
AS
(
SELECT Row_Number() OVER (ORDER BY [LastName]) AS RowNumber
,Rank() OVER (ORDER BY [LastName]) AS Rank
,Dense_Rank() OVER (ORDER BY [LastName]) AS DenseRank
,NTile(3) OVER (ORDER BY [LastName]) AS NTile_3
,NTile(4) OVER (ORDER BY [LastName]) AS NTile_4
,[ContactID]
,[FirstName]
,[LastName]
FROM [AdventureWorks].[Person].[Contact]
)
SELECT *
FROM tblContact
WHERE (RowNumber > 50) AND (RowNumber <= 100)
Najpierw utworzyliśmy wirtualną tabelę tblContact zawierającą wszystkie dane, które zostały
zwrócone w wewnętrznym zapytaniu, łącznie z numerami wierszy wyliczonymi przez funkcje
pozycjonujące. Teraz możemy wybrać z wirtualnej tabeli tą stronę danych, której
potrzebujemy.
Rekurencja
Za pomocą wyrażeń tabelarycznych możemy zrealizować inną bardzo ciekawą funkcjonalność
 rekurencje. Rekurencja we wcześniejszym serwerze nie była wspomagana w żaden sposób.
Oczywiście nie ma przeszkody której nie dałoby się obejść. Programiści wypracowali parę
sposobów na tworzenie własnych zapytań rekurencyjnych. Teraz sytuacja znacznie się
poprawiła. Wprowadzenie wyrażeń tablicowych umożliwia złączenie wyników zapytania
wewnętrznego z wyrażeniem CTE, które je zawiera. Ten wyraz
ny sygnał to zielone światło dla
rekurencji.
Poniższy przykład wybiera z tabeli Employee pracowników i przypisanych im przełożonych.
WITH tblManager AS
(
SELECT EmployeeID
, LoginID
, ManagerID
, CAST(NULL as nvarchar(100)) AS MgrLogin
FROM HumanResources.Employee
WHERE ManagerID IS NULL
Autor: Grzegorz Chuchra Strona 78
www.CentrumXP.pl
UNION ALL
SELECT emp.EmployeeID
, emp.LoginID
, emp.ManagerID
, CAST(mgr.LoginID as nvarchar(100))
FROM HumanResources.Employee emp INNER JOIN tblManager mgr
ON emp.ManagerID = mgr.EmployeeID
)
SELECT *
FROM tblManager
ORDER BY ManagerID
Prześledz
my krok po kroku, co tak naprawdę dzieje się w powyższym zapytaniu. Pierwszy
krok to zainicjalizowanie wartości początkowej. Wybieramy prezesa firmy. Czyli osobę która
nie ma przełożonego. W tym celu wpisujemy następujący skrypt.
SELECT EmployeeID
, LoginID
, ManagerID
, CAST(NULL as nvarchar(100)) AS MgrLogin
FROM HumanResources.Employee
Zwraca on osobę o loginie adventure-works\ken0 i EmployeeID równym 109. Następnym
krokiem jest złączenie wyników, które już znajdują się w tabeli CTE z Employee z tym, że
złączenie dokonujemy po następujących polach:
emp.ManagerID = mgr.EmployeeID
Oznacza to, że dla wszystkich pracowników, których mamy już wstawionych w tabeli
wirtualnej, wybieramy ich podwładnych z tabeli Employee. A następnie dla wyników tego
zapytania wybieramy pracowników, którzy będą z kolei przełożonymi kolejnych itd.
W naszym konkretnym przypadku wybierzemy ludzi, których szefem jest pan z EmployeeID, a
następnie dla nich wybierzemy ich podwładnych itd.
Podsumowanie
Z całej gamy usprawnień, jakie posiada najnowszy SQL Server 2005 w stosunku do
poprzednika przedstawiłem te, które moim zdaniem są najbardziej użytecznie i których prędzej
czy póz
niej będzie trzeba się nauczyć. O ile funkcje pozycjonujące to bardziej ciekawostka,
która tylko lekko usprawnia pracę, to wprowadzenie wyrażeń tablicowych znacznie poszerza
możliwości rozwoju własnych aplikacji. Jak zademonstrowałem na przykładzie prostego
skryptu rekurencyjnego, którego implementacja za pomocą standardowych zabiegów byłaby
skomplikowanym zagadnieniem.
Autor: Grzegorz Chuchra Strona 79
www.CentrumXP.pl
18. Procedury składowalne i funkcje
bazodanowe
Procedury składowane
Wewnątrz procedury możemy robić wszystko to co w ramach każdego skryptu SQL. Możemy
więc używać poleceń zarówno z języka DML jak i DCL. Dzięki temu możemy sobie utworzyć
standardową procedurą nadającą uprawnienia użytkownikowi do odpowiednich obiektów
bazodanowych, jak i skrypt usuwający użytkownika, a co za tym idzie wszystkie powiązane z
nim dane z innych tabel.
Takie grupowanie operacji sprawi, iż nie będziesz musiał za każdym razem głowić się gdzie
znajdują się dane związane z użytkownikiem. Nie będzie też konieczności trzymania
wszystkich raz stworzonych skryptów w jakimś odpowiednim katalogu. Dzięki procedurą
składowanym wystarczy utworzyć procedurę sp_DeleteUser która będzie pobierała jak
parametr klucz główny użytkownika i sam zadba o to aby usunąć dane ze wszystkich tabel.
Aby utworzyć procedurę składowaną wystarczy wstawić nasze skrypt w szablon tworzący
procedurę składowaną
CREATE PROCEDURE
Oprócz nazwy procedury i parametrów procedura nie posiada żadnych dodatkowych opcji o
których należy pamiętać podczas jej budowania.
Aby zademonstrować jak łatwo można utworzyć procedurę napiszemy sobie skrypt który
wyświetla informacje o użytkownikach wybranych po nazwiskach.
SELECT [ContactID]
,[FirstName]
,[LastName]
,[EmailAddress]
,[Phone]
FROM [AdventureWorks].[Person].[Contact]
WHERE [LastName] LIKE 'Adams'
ORDER BY FirstName
Teraz wystarczy tylko wpisać nasz skrypt w szablon tworzący procedurę. Sam obiekt
nazwiemy uspGetContactsByLastName. Prefix usp mówi nam o tym, że procedura jest
stworzona przez użytkownika bazy danych UserStorageProcedure. Teraz należy jeszcze
zdefiniować parametr po jakim procedura ma wyszukiwać użytkowników. Ponieważ my
Autor: Grzegorz Chuchra Strona 80
www.CentrumXP.pl
chcemy wyszukiwać kontakty po nazwisku to należy jeszcze podać tą informacje przy
tworzenie procedury.
CREATE PROCEDURE uspGetContactsByLastName
@LastName NVARCHAR(32)
AS
SELECT [ContactID]
,[FirstName]
,[LastName]
,[EmailAddress]
,[Phone]
FROM [AdventureWorks].[Person].[Contact]
WHERE [LastName] LIKE @LastName + '%'
ORDER BY FirstName
Teraz już tylko wystarczy uruchomić skrypt i procedura już jest dostępna na naszym serwerze.
Aby sprawdzić jak działa wystarczy odświeżyć widok obiektów bazodanowych w okienku
ObjectExplorer. Rozwinąć folder Programmability ->Storaged Procedures nacisnąć prawym
guzikiem myszy na naszą procedurę i wybrać z menu podręcznego Execute Stored
Procedure&
Autor: Grzegorz Chuchra Strona 81
www.CentrumXP.pl
Efektem działania powinno być okno dialogowe w którym możemy wpisać nazwisko osób które
chcemy odnalez
ć. Następnie naciskamy OK. co w rezultacie prowadzi do wygenerowania nam
skryptu który służy do uruchomienia procedury.
Tak wygląda utworzony przez SQL Server 2005 skrypt:
USE [AdventureWorks]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[uspGetContactsByLastName]
@LastName = N'Ada'
SELECT 'Return Value' = @return_value
GO
W rzeczywistości wystarczyło by nam zaledwie tyle kodu aby uzyskać taki sam efekt.
EXEC [dbo].[uspGetContactsByLastName] 'Ada'
lub
EXEC [dbo].[uspGetContactsByLastName] @LastName = 'Ada'
Autor: Grzegorz Chuchra Strona 82
www.CentrumXP.pl
Jak widać możemy przekazywać parametry do procedury anonimowo nie przypisując jawnie
któremu z parametrów chcemy przypisać wartość. To rozwiązanie jest dobre jeśli nasza
procedura ma tylko jeden parametr wejściowy. Jeśli jednak nasza procedura oprócz
wyszukiwania po nazwisku wyszukiwała by dodatkowo po imieniu. To lepszym rozwiązaniem
było by jawne podanie parametrów wyglądało by to wtedy mniej więcej tak:
EXEC [dbo].[uspGetContacts] @LastName = 'Ada , @FirstName =  John
Jeśli w naszym skrypcie procedura jest wywoływana jako pierwszy element słowo EXEC jest
opcjonalne i procedura również się wykona.
Własności procedur
Procedury składowane działają szybciej niż taki sam skrypt. Dzieje się to z kilku prostych
przyczyn. Ponieważ SQL Server przy każdym uruchamianiu skryptu musi poczynić kilka
ważnych kroków takich jak:
1. sprawdzenie poprawności wpisanego skryptu
2. sprawdzenie statystyk
3. wykonanie planu zapytania
Jeśli wykonywana jest skrypt SQL wszystkie te operacje robione są podczas jego
uruchomienia. Procedura składowana jest z definicji sprawdzana przez kompilator podczas jej
tworzenia, a plan zapytania jest tworzony automatycznie przy jej pierwszym uruchomieniu. Tak
więc przy dodatkowym atutem procedur jest szybkość ich działania.
Funkcje składowane
Funkcje składowane mogą realizować identyczną funkcjonalność jak procedury składowane
mają jednak jeszcze parę dodatkowych atutów.
Przypuśćmy, że chcemy sobie zrobić funkcje która przerabia datę z nieprzyjemnego formatu
2005-09-11 23:44:44.327 do formy troszeczkę bardziej czytelnej 11-9-2005. Do takiego celu
świetnie nada się funkcja bazodanowa.
CREATE FUNCTION funDateFormater
(
@myDate datetime
, @separator varchar(8)
)
Autor: Grzegorz Chuchra Strona 83
www.CentrumXP.pl
RETURNS nvarchar(32)
AS
BEGIN
RETURN
CONVERT(nvarchar(32), DATEPART(dd, @myDate))
+ @separator
+ CONVERT(nvarchar(32), DATEPART(mm, @myDate))
+ @separator
+ CONVERT(nvarchar(32), DATEPART(yy, @myDate))
END
A teraz zobaczmy jak wywołać funkcje i prezentuje się sam wynik.
SELECT dbo.funDateFormater(GETDATE(), '-') 11-9-2005
Jako parametr pobiera datę i rodzaj seperatora pomiędzy dniem, miesiącem i rokiem. Jak
widać sam szablon funkcji nie różni się niczym oprócz słowa kluczowego FUNCTION i
wartości zwracanej RETURNS niczym od deklaracji procedury. Jednak powyższa funkcja ma
jedną cechę której nie posiada procedura. Mianowicie można wywołać ją bezpośrednio na
operacji SELECT.
SELECT Person.Contact.FirstName, Person.Contact.LastName,
dbo.funDateFormater(HumanResources.Employee.BirthDate, '-') AS BirthDate
FROM Person.Contact INNER JOIN HumanResources.Employee
ON Person.Contact.ContactID = HumanResources.Employee.ContactID
Autor: Grzegorz Chuchra Strona 84
www.CentrumXP.pl
Dzięki funkcją możemy stworzyć własną bibliotekę z niezbędnymi skryptami których zapewnie
będziemy używać w wielu projektach. Daje to nam możliwość wielokrotnego wykorzystywania
bloków raz napisanego kodu. Przykład funkcji formatującej datę jest przykładem jak jedna
prosta funkcja może zostać wykorzystana w wielu projektach wszędzie tam gdzie korzystamy
z daty.
W rozdziale poświęconym itegracji SQL Server 2005 z Frameworke rozwiniemy temat i
napiszemy troszeczkę zgrabniejszą funkcję formatującą datę.
Podsumowanie
Procedury składowane są podstawą przy tworzeniu warstwy bazodanowej skomplikowanych
systemów informatycznych. Oprócz wszystkich pozytywnych aspektów które zostały
poruszone w artykule istnieje jeszcze problem bezpieczeństwa. W portalach internetowych
gdzie dostęp do Internetu jest kluczową sprawą ważne jest aby użytkownicy mieli jak
najmniejszy dostęp do obiektów bazodanowych. Tu świetnie sprawdzają się procedury.
Stanowią zamkniętą w paczkę funkcjonalności którą można udostępnić bez potrzeby
nadawania użytkownikom dostępu do tabeli.
19. XML i XQuery
Microsoft SQL Server 2000 umożliwiał export relacyjnych danych z i do XML. Jednakże
jedynym sposobem na przechowywanie danych typu XML było pole typu string. Jest to bardzo
nieefektywny sposób przechowywania tego typu danych. Ograniczenia jakie to powoduje
można mnożyć:
1. brak możliwości sprawdzenia poprawności XML
2. brak możliwości wyszukiwania odpowiednich tagów
3. brak możliwości zmiany poszczególnych wartości
Oczywiście wszystkie te problemy można ominąć poprzez napisanie odpowiednich funkcji
bazodanowych. Ale żadna z tych opcji nie jest udostępniona przez starszą wersje serwera.
Większość tych ograniczeń została usunięta w SQL Server 2005 dzięki wprowadzanie typu
danych XML. Pole to może być indeksowane, zmieniane i oczywiście bez żadnych problemów
przeglądane. Dodatkowo został udostępniony nowy język XQuery, za pomocą którego
Autor: Grzegorz Chuchra Strona 85
www.CentrumXP.pl
możemy bezpośrednio manipulować na danych, zmieniać dane zaszyte wewnątrz któregoś z
pól XML oraz wyciągać pojedyncze wartości.
Jak wcześniej wspomniałem głównym zastosowaniem XML jest komunikacja. Zacznijmy więc
od bardzo życiowego przypadku. Jesteśmy administratorem bazy danych w firmie
AdventureWorks i dostaliśmy bezpośrednie polecenia na utworzenie bilansu sprzedaży
każdego z produktów. Ponieważ nasz przełożony używa Excela musimy mu podać dane w
takim formacie, aby program ten bez problemu mógł sobie z nimi poradzić. Oczywiście
rozwiązaniem naszego problemu będzie XML. Najpierw musimy stworzyć odpowiednie
zapytanie, ale to już nie powinno nam sprawić problemów.
SELECT [Production].[Product].[Name], SUM(OrderQty) AS Quantity
FROM [Sales].[SalesOrderHeader]
INNER JOIN [Sales].[SalesOrderDetail]
ON [Sales].[SalesOrderHeader].[SalesOrderID] = [Sales].[SalesOrderDetail].[SalesOrderID]
INNER JOIN [Production].[Product]
ON [Production].[Product].[ProductID] = [Sales].[SalesOrderDetail].[ProductID]
GROUP BY [Production].[Product].ProductID, [Production].[Product].[Name]
ORDER BY Quantity DESC
Teraz wystarczy dodać do naszego zapytania klauzulę FOR XML AUTO i już mamy gotowy
plik, który możemy bezpośrednio wysłać do osoby zamawiającej.
SELECT [Production].[Product].[Name], SUM(OrderQty) AS Quantity
FROM [Sales].[SalesOrderHeader]
INNER JOIN [Sales].[SalesOrderDetail]
ON [Sales].[SalesOrderHeader].[SalesOrderID] = [Sales].[SalesOrderDetail].[SalesOrderID]
INNER JOIN [Production].[Product]
ON [Production].[Product].[ProductID] = [Sales].[SalesOrderDetail].[ProductID]
GROUP BY [Production].[Product].ProductID, [Production].[Product].[Name]
ORDER BY Quantity DESC
FOR XML AUTO
Jak widać, nie ma potrzeby rozpisywać się, jak działa powyższa składnia. Poprzez proste
dodanie do zapytanie klauzuli otrzymujemy wynik w postaci XML. Gdy dodamy do naszej opcji
FOR XML AUTO dodatkową instrukcję ELEMENTS, będziemy mogli zmienić sposób
organizacji danych. Nie mamy już jednego elementu i dane jako atrybuty, lecz dane które
wcześniej były atrybutami teraz są jak elementy w nagłówku Produkt.
Autor: Grzegorz Chuchra Strona 86
www.CentrumXP.pl
SELECT [Production].[Product].[Name], SUM(OrderQty) AS Quantity
FROM [Sales].[SalesOrderHeader]
INNER JOIN [Sales].[SalesOrderDetail]
ON [Sales].[SalesOrderHeader].[SalesOrderID] = [Sales].[SalesOrderDetail].[SalesOrderID]
INNER JOIN [Production].[Product]
ON [Production].[Product].[ProductID] = [Sales].[SalesOrderDetail].[ProductID]
GROUP BY [Production].[Product].ProductID, [Production].[Product].[Name]
ORDER BY Quantity DESCFOR XML AUTO, ELEMENTS
Dzięki użyciu nowej opcji TYPE w klauzuli FOR XML mamy możliwość traktowania wyników
jako pojedynczej kolumny w głównym zapytaniu z informacją o nagłówku.
Kolejną nową opcją, do jakiej mamy dostęp w opcji FOR XML jest PATH. Ułątwia ona bardziej
świadome tworzenie konstrukcji XML z możliwością tworzenia pojedynczych pól typu XML
wewnątrz tabel. Poniższy przykład pokazuje, jak możemy stworzyć z danych już istniejących
tabelę z polem XML.
SELECT TOP 2 SalesOrderHeader.SalesOrderID AS SalesOrderID,
OrderDate AS OrderDate,
CustomerID AS CustomerID,
(SELECT ProductID AS '@ProductID',
OrderQty AS '@OrderQty',
UnitPrice AS '@UnitPrice'
FROM Sales.SalesOrderDetail AS SalesOrderDetail
WHERE SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
FOR XML PATH ('SalesOrderDetail'), type) AS 'ProductsLine'
FROM Sales.SalesOrderHeader AS SalesOrderHeader
FOR XML PATH ('SalesOrderHeader')
Skrypt ten wymaga małego wyjaśnienia. Po pierwsze: nazwa kolumny określa ścieżkę w XML.
Argument za opcją Path określa nazwę kolumny. Prefiks @ mówi, że kolumna wynikowa jest
atrybutem, a nie elementem w polu XML.
Teraz, kiedy już wiemy, w jaki sposób możemy transformować dane do typu XML sprawdz
my
jak można korzystać z danych, które zostały nam podane. Typowym przykładem korzystania z
informacji zgromadzonych w XML może być słownik zawierający nazwy krajów. W każdym
większym systemie bazodanowym zachodzi konieczność przechowywania danych
dotyczących poszczególnych krajów. Tak więc nie ma potrzeby przy każdym większym
Autor: Grzegorz Chuchra Strona 87
www.CentrumXP.pl
projekcie wprowadzać je od nowa. Załóżmy więc, że do naszego projektu bazy danych
Deanery chcemy wczytać plik z nazwami krajów. Użyjemy do tego klauzuli OPENXML.
Przypomnę tylko strukturę tabeli Country
CREATE TABLE [dbo].[Country]
(
[CountryID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[CountryName] [nvarchar](64) NOT NULL
)
i może przejść do pisania skryptu.
DECLARE @xml int
DECLARE @xmlData varchar(512)
SET @xmlData = 'Czechy
Anglia
Francja
Hiszpania
Portugalia
Ukraina
'
EXEC sp_xml_preparedocument @xml OUTPUT, @xmlData
INSERT INTO Country([CountryName])
SELECT [text]
FROM OPENXML (@xml, '/Countires/Name/')
WHERE [text] IS NOT NULL
EXEC sp_xml_removedocument @xml
Najpierw zdefiniowaliśmy tekst XML. Jest to w naszym przypadku plik z danymi, które chcemy
zaimportować. Kolejnym krokiem jest zdefiniowanie wskaz
nika na plik. Mówiąc troszeczkę
jaśniej, po tym jak wczytamy plik do pamięci SQL Server, będziemy chcieli na nim operować.
Aby móc go bez problemu  wyciągnąć z pamięci dostaniemy numer, pod jakim został on
zapisany. Numer ten nazywamy wskaz
nikiem. Teraz kiedy mamy już i dane i wskaz
nik na plik,
używamy systemowej procedury, dzięki której wczytamy naszego XML do pamięci.
EXEC sp_xml_preparedocument @xml OUTPUT, @xmlData
Autor: Grzegorz Chuchra Strona 88
www.CentrumXP.pl
Linia ta mówi po prostu: zrób mi dokument z tego pliku, a jego numer wpisz do @xml.
Teraz jest już z górki. Standardowe zapytanie wpisujące do naszego słownika Country dane
krajów. Klauzula
FROM OPENXML (@xml, '/Countires/Name/')
daje informacje o tym, że czytamy określony plik i tylko konkretny jego tag, ponieważ plik
mógłby posiadać kody pocztowe bądz
telefony kierunkowe do poszczególnych państw, a my
chcemy wybrać tylko dane dotyczące nazwy kraju. Ostatni etap to usunięcie z zasobów
serwera utworzonego dokumentu.
EXEC sp_xml_removedocument @xml
Ostatnią sprawą jaką chciałbym poruszyć jest pole typu XML. Jak mówiłem we wcześniejszym
rozdziale dotyczącym typów danych, jest to typ, który pojawił się dopiero w tej wersji SQL
Server. Umożliwia on nie tylko korzystanie z danych XML za pomocą języka T-SQL, ale
również może być używany jako inny typ danych, włączając w to zmienne i kolumny. Ten nowy
typ danych posiada dodatkowo język zapytań XQuery umożliwiający edycję dodawanie i
usuwanie danych znajdujących się wewnątrz pola.
Podsumowanie
XML umożliwia programistom i projektantom systemów informatycznych elastyczną pracę z
danymi. Komunikacja pomiędzy różnymi platformami informatycznymi za pomocą Web
services działa dzięki rozpowszechnieniu się standardu XML. Kanały informacyjne RSS
działające przy każdym większym portalu również istnieją wyłącznie dzięki XML.
Artykuł ten zaledwie napomknął o możliwościach SQL Server 2005 w tej dziedzinie.
Indeksowanie, zarządzanie, tworzenie widoków i schematów - to wszystko i wiele innych
aspektów, które mogłyby pochłonąć cały taki kurs.
Autor: Grzegorz Chuchra Strona 89
www.CentrumXP.pl
20. Użycie .NET CLR w SQL Server 2005
O wykorzystaniu CLR w SQL Server 2005 można by napisać wiele książek (i nie wątpię, że w
najbliższym czasie tak się stanie). My zajmiemy się dość zasadniczymi zagadnieniami, które
będą mogły przedstawić rewolucyjny charakter usprawnień. Pierwszym krokiem będzie
napisanie funkcji do formatowania daty. Drugim nieco bardziej skomplikowanym zagadnieniem
będzie napisanie własnej funkcji agregującej.
Funkcje bazodanowe
Pierwszym krokiem, jakiego musimy dokonać jest uruchomienie MS Visual Studio .NET 2005.
Gdy już to będziemy mieli za sobą, musimy utworzyć odpowiedni projekt. W tym celu musimy
wykonać następujące czynności. W głównym menu wybieramy File -> New -> Project.
Następnie z lewego okna rozwijamy Visual C# zaznaczamy Database i naciskamy OK.
Po utworzeniu projektu pojawi się okienko dialogowe z możliwością wyboru serwera, z jakim
chcemy się połączyć i bazy danych na nim istniejącym. My uzupełnimy to tak jak na zdjęciu
poniżej.
Autor: Grzegorz Chuchra Strona 90
www.CentrumXP.pl
Gdy projekt się uruchomi, odnajdujemy zakładkę Solution Explorer i klikamy prawym guzikiem
myszy na projekt w celu dodania naszej funkcji. Wybieramy Add -> User Defined Funcition po
otworzeniu okna dialogowego wpisujemy nazwę funkcji DateFormater i naciskamy OK.
Autor: Grzegorz Chuchra Strona 91
www.CentrumXP.pl
Pojawiło nam się okno z wygenerowaną przykładową funkcją. Przechodzimy teraz do
napisania funkcji, która jako argument będzie pobierać datę oraz format daty w jakim chcemy
ją przedstawić. Jak pamiętamy z wcześniejszych artykułów, standardowo zwracana data
wygląda następująco: 2005-08-20 17:19:22.977. Co jednak, jeśli chcemy, aby data zwracana
była w formacie 20.08.2005 lub 20//08//2005? Aby rozwiązać ten problem wystarczy tylko
jedna linia kodu w C#. Zmodyfikujmy wygenerowaną funkcję do takiej postaci:
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString DateFormater(SqlDateTime myDate, SqlString dateFormat)
{
return (SqlString)string.Format((string)("{0:" + dateFormat + "}"), myDate.Value);
}
Metoda pobiera standardowo dwa argumenty: datę oraz format, w jakim chcemy datę
przedstawić. Format daty przedstawiany jest w następujący sposób:  y reprezentuje rok,  M
miesiąc, a  d dzień. Pomiędzy podane znaczniki można podstawiać dowolne znaki. Teraz
wystarczy tylko utworzyć plik DDL i przerzucić go na serwer bazodanowy. Z menu wybieramy
Autor: Grzegorz Chuchra Strona 92
www.CentrumXP.pl
Build -> Build Solution, a następnie Bulid- > Deploy Solution. Jeśli wszystko poszło dobrze
możemy teraz przejść do serwera SQL i sprawdzić czy nasza funkcja działa poprawnie.
Musimy tylko jeszcze włączyć na MS SQL Server 2005 opcje korzystania z CLR. Funkcja ta ze
względów bezpieczeństwa jest domyślnie wyłączona. Aby ją uruchomić, wystarczy uruchomić
następującą linię kodu.
sp_configure 'clr enabled', 1
go
reconfigure
go
Teraz możemy przetestować naszą funkcję. Poniższy skrypt pokazuje w jaki sposób można ją
uruchomić i wyniki jakie otrzymujemy.
USE AdventureWorks
GO
SELECT dbo.DateFormater(GetDate(), 'yy.MM.dd') 05.08.20
SELECT dbo.DateFormater(GetDate(), 'dd///MM///yyyy') 20///08///2005
SELECT dbo.DateFormater(GetDate(), 'yy**MM**dd') 05**08**20
SELECT dbo.DateFormater(GetDate(), 'yy:-)MM:-)dd') 05:-)08:-)20
Funkcje agregujące
Funkcjom agregującym poświęciliśmy jeden z artykułów. I od tego czasu mam nadzieję, że
wszyscy zdają sobie sprawę, jak ważnym zagadnieniem są agregaty. A co jeśli moglibyśmy
napisać własne funkcje agregujące, z własnymi zasadami agregacji? Taką właśnie możliwość
daje nam połączenie SQL Server 2005 z .NET Framework.
Aby pracować z własnymi User-Defined Aggregates (UDA), należy zapoznać się z czterema
podstawowymi zasadami rządzącymi ich działaniem. Entering, accumulating, mering i existing.
Kiedy uruchamiamy funkcję agregującą na serwerze, musimy zainicjalizować wartość
początkowe. Jeśli robilibyśmy operacje dodawania, odejmowania - wartością startową byłoby
zero. Jeśli jednak chcielibyśmy przeprowadzić operacje mnożenia wartością pierwotną byłaby
wartość jeden.
Ponieważ nasz przykład nie będzie nazbyt wyrachowany i ograniczy się do powielenia
agregatu SUM, wartość domyślną inicjalizujemy zerem.
Autor: Grzegorz Chuchra Strona 93
www.CentrumXP.pl
public void Init()
{
result = 0;
}
Następnym krokiem jest zdefiniowanie tego, co się dzieje z kolejną wartością, jaka jest
pobierana. Czyli co się dzieje, jeśli podczas procesu przechodzenia po kolejnych wierszach w
tabeli dostajemy nowe wartości. Jak mają się zachować w stosunku do wartości, które już
mamy.
public void Accumulate(SqlInt32 Value)
{
result += (int)Value;
}
W naszym przypadku odpowiedz
jest dość prosta. Należy dodać wartość, która została
podana do wartości, które już zsumowaliśmy.
Metoda Terminale zwraca wynik po wykonaniu metody Accumulate. Jest również
odpowiedzialna za zwrócenie wyniku do kodu wywołującego. W naszym przypadku zwróci
sumę wyniku z całej tabeli.
public SqlInt32 Terminate()
{
return new SqlInt32(result);
}
Merge jest specjalną metodą, która pomaga w utrzymaniu spójności na poziomie obiektowym.
public void Merge(MyIntegerCalculate Group)
{
result += Group.result;
}
W ten sposób napisaliśmy funkcję agregującą SUM, która w naszym przypadku nazywa się
MyIntegerCalculate i używamy jej dokładnie w ten sam sposób, jak SUM z tą różnicą, że
wstawiamy przed nazwę agregata dbo.
Przykład ten miał za zadanie zademonstrować, jak działają funkcje agregujące i przedstawić
cztery podstawowe zasady tworzenia własnych agregatów. Następnym i wydaje się ostatnim
krokiem na drodze tworzeniem własnych efektywnych i poprawnie działających funkcji są
atrybuty.
Autor: Grzegorz Chuchra Strona 94
www.CentrumXP.pl
Atrybuty agregatów
Atrybuty są specjalnymi znacznikami na klasach w .NET, za pomocą których możemy
definiować dodatkową funkcjonalność dla danej klasy. Przykładem mogą być Web serwisy,
przy których należy do każdej z metod dodać atrybut [WebMethod], aby umożliwić korzystanie
z niej za pośrednictwem stron WWW.
W przypadku funkcji agregujących, które z definicji pracują na bazach danych, gdzie
najważniejsza jest wydajność, atrybuty są wyjątkowo ważne. Pomagają one SQL Server
Query Optimizer efektywniej budować zapytania.
Format  mówi SQL czy powinien serializować dane
IsInvariantToNull  jeśli jest ustawione na true, serwer SQL ignoruje wartości NULL. W naszym
przypadku nie ma potrzeby pobierać wartości nieokreślonych, więc możemy bez problemu
ustawić na true. Jeśli jednak tworzylibyśmy funkcję wyliczającą wartość średnią musielibyśmy
również uwzględnić wartości NULL.
IsInvariantToOrder  jeśli ustawimy na true, oznacza to, że nie zważamy na kolejność w jakiej
dane są pobierane. Ponieważ suma jest operacją przemienną, nie ma potrzeby zachowywać
kolejności.
IsNullIfEmpty  jeśli jest ustawione na true, zwraca wartość nieokreśloną NULL, gdy nie ma
żadnych wartości. W naszym przypadku ogranicza się to do tego, że jeśli tabela będzie pusta,
lub wiersze, które podaliśmy do sumowania posiadają wartość NULL to wynik również będzie
NULL.
Podsumowanie
Wydaje mi się, że wszystkich zachęciłem do zagłębienie się w CLR i nowe możliwości jakie
stoją przed SQL Server 2005 i jego integracją z .NET Framework. Mimo wielkich możliwości,
jakie zostały dane zarówno administratorom baz danych, jak i developerom powinniśmy
pamiętać, iż operacje wykonywane za pomocą Frameworka są znacznie bardziej kosztowne i
czasochłonne, niż alternatywna operacja w Transact-SQL. Przy użyciu CLR powinniśmy
najpierw sprawdzić, czy korzyści wynikające z łatwiejszego zakodowania są na tyle duże, aby
zrezygnować z SQL.
Autor: Grzegorz Chuchra Strona 95