Darmowy fragment publikacji:
SQL Server 2005 Express.
Skuteczne rozwi„zania
Autor: Danuta Mendrala, Marcin Szeliga
ISBN: 978-83-246-1131-7
Format: B5, stron: 432
Zbi(cid:243)r praktycznych porad dla programist(cid:243)w i administrator(cid:243)w baz danych
(cid:149) Instalacja i konfiguracja SQL Server 2005 Express
(cid:149) Zabezpieczanie serwera i danych
(cid:149) Projektowanie baz i tabel
(cid:149) Tworzenie optymalnych zapytaæ
MS SQL Server to baza danych od dawna kojarzona ze stabilno(cid:156)ci„, wydajno(cid:156)ci„
i(cid:133) wysok„ cen„. Jednak najnowsza edycja tej bazy, oznaczona symbolem 2005,
ma tak¿e swoj„ nieodp‡atn„ wersjŒ. SQL Server 2005 Express dystrybuowany
jest za darmo, a co wiŒcej -- licencja pozwala na wykorzystywanie go do cel(cid:243)w
komercyjnych. Oczywi(cid:156)cie ta wersja posiada wiele ograniczeæ w stosunku do wersji
p‡atnej, mimo to jednak doskonale spe‡nia zadania zaplecza bazodanowego dla aplikacji
i portali internetowych, zapasowej bazy danych dla rozbudowanych system(cid:243)w
informatycznych i uniwersalnego narzŒdzia do nauki.
Ksi„¿ka (cid:132)SQL Server 2005 Express(cid:148) to zbi(cid:243)r porad dotycz„cych instalacji i konfiguracji
tej aplikacji oraz sposob(cid:243)w jej wykorzystania. Autor ksi„¿ki (cid:150) do(cid:156)wiadczony
administrator i programista baz danych (cid:150) umie(cid:156)ci‡ tu rozwi„zania niemal wszystkich
typowych zadaæ, jakie staj„ przed u¿ytkownikami bazy SQL Server 2005. Z tej ksi„¿ki
dowiesz siŒ, jak optymalnie skonfigurowa(cid:230) bazŒ danych, zabezpieczy(cid:230) serwer czy
zaprojektowa(cid:230) struktury baz i tabel. Nauczysz siŒ korzysta(cid:230) z jŒzyka SQL, formu‡owa(cid:230)
i optymalizowa(cid:230) zapytania, tworzy(cid:230) kopie zapasowe danych i zarz„dza(cid:230) transakcjami.
(cid:149) Instalacja i konfiguracja SQL Server 2005 Express
(cid:149) Zarz„dzanie bazami danych
(cid:149) Tworzenie kopii zapasowych i odtwarzanie danych
(cid:149) Zabezpieczanie serwera
(cid:149) Konta i role u¿ytkownik(cid:243)w
(cid:149) Typy danych
(cid:149) Projektowanie baz i tabel
(cid:149) Generowanie indeks(cid:243)w
(cid:149) Tworzenie i wykorzystywanie procedur sk‡adowanych
(cid:149) Pobieranie i modyfikowanie danych
(cid:149) Transakcje i blokady
Usprawnij swoj„ pracŒ, korzystaj„c z do(cid:156)wiadczenia profesjonalist(cid:243)w
Wydawnictwo Helion
ul. Ko(cid:156)ciuszki 1c
44-100 Gliwice
tel. 032 230 98 63
e-mail: helion@helion.pl
Spis tre(cid:264)ci
Wst(cid:246)p ............................................................................................ 11
SQL Server 2005 Express ...............................................................................................11
O czym jest ta ksi(cid:261)(cid:298)ka? ..................................................................................................12
Konwencje i oznaczenia .................................................................................................13
Cz(cid:246)(cid:264)(cid:232) I Administracja ............................................................... 15
Rozdzia(cid:228) 1. Instalacja ....................................................................................... 17
Przygotowanie instalacji .................................................................................................17
Wymagania ..............................................................................................................17
Ograniczenia ............................................................................................................19
Licencje ....................................................................................................................19
Instalacja .........................................................................................................................20
Wybór konta us(cid:225)ugi serwera SQL 2005 ....................................................................20
Instalacja serwera .....................................................................................................21
Instalacja narz(cid:266)dzi administracyjnych ......................................................................23
Instalacja pomocy BOL ............................................................................................23
Weryfikacja instalacji .....................................................................................................24
Pliki dziennika ..........................................................................................................24
Us(cid:225)ugi serwera ..........................................................................................................25
Po(cid:225)(cid:261)czenie z baz(cid:261) master i sprawdzenie wersji serwera ..................................................25
SQLDIAG ................................................................................................................25
Uwaga na kontrol(cid:266) konta u(cid:298)ytkownika systemu Windows Vista .............................26
Definiowanie aliasów po stronie komputerów klienckich ........................................26
Instalacja przyk(cid:225)adowej bazy AdventureWorks ..............................................................27
Zmiana nazwy serwera SQL ...........................................................................................27
Usuwanie instalacji serwera SQL 2005 ..........................................................................28
MSDE a SQL 2005 Express ...........................................................................................29
Workload Governor ..................................................................................................30
Porównanie funkcjonalno(cid:286)ci obu wersji serwera ......................................................30
SharePoint ......................................................................................................................30
WSS 2.0 i WSDE .....................................................................................................30
WSS 3.0 i Windows Internal Database .....................................................................31
MOSS 2007 i SQL 2005 Express .............................................................................31
Windows Server Update Services 3.0 .............................................................................32
Rozdzia(cid:228) 2. Aktualizacja ................................................................................... 29
4
SQL Server 2005 Express. Skuteczne rozwi(cid:241)zania
Rozdzia(cid:228) 3. Konfiguracja serwera i baz danych .................................................. 49
Aktualizacja serwera .......................................................................................................32
Scenariusze aktualizacji ...........................................................................................33
Aktualizacja baz danych .................................................................................................34
Upgrade Advisor ......................................................................................................35
Aktualizacja bazy danych .........................................................................................36
Zadania po aktualizacji .............................................................................................40
Migracja do edycji p(cid:225)atnych ...........................................................................................42
Dodatkowe funkcje edycji Standard .........................................................................43
Dodatkowe funkcje edycji Enterprise .......................................................................45
Architektura serwera .......................................................................................................49
Aparat relacyjny .......................................................................................................50
Aparat sk(cid:225)adowania danych ......................................................................................51
SQLOS .....................................................................................................................51
Konfiguracja serwera ......................................................................................................52
Protoko(cid:225)y sieciowe ...................................................................................................52
Us(cid:225)ugi .......................................................................................................................56
Wybrane opcje serwera ............................................................................................56
Bazy danych ...................................................................................................................58
Systemowe bazy danych ..........................................................................................59
Pliki bazodanowe .....................................................................................................61
Naprawa b(cid:225)(cid:266)dów .......................................................................................................68
Zarz(cid:261)dzanie bazami danych ............................................................................................70
Tworzenie baz danych ..............................................................................................70
Obiekty systemowe ..................................................................................................70
Wybrane opcje bazy danych .....................................................................................72
Modyfikowanie baz danych .....................................................................................73
Skrypty administracyjne ...........................................................................................75
Usuwanie baz danych ...............................................................................................77
XML dla administratorów ..............................................................................................77
Pliki konfiguracyjne .................................................................................................77
Plany wykonania zapyta(cid:276) .........................................................................................78
Zapisane plany wykonania instrukcji .......................................................................79
Statystyki wykonania instrukcji ...............................................................................79
Dyrektywy optymalizatora .......................................................................................79
Wyniki funkcji EventData() .....................................................................................79
Pliki formatu programu BCP ....................................................................................80
Best Practices Analyzer 2.0 ............................................................................................80
Tworzenie kopii zapasowych .........................................................................................81
Urz(cid:261)dzenia kopii zapasowych ..................................................................................81
Typy kopii zapasowych ............................................................................................83
Strategie ...................................................................................................................85
Automatyczne tworzenie kopii zapasowych .............................................................88
Sprawdzanie aktualno(cid:286)ci kopii .................................................................................90
Odtwarzanie kopii zapasowych ......................................................................................91
Spójno(cid:286)(cid:252) bazy ...........................................................................................................91
Odtwarzanie kopii danych ........................................................................................92
Odtwarzanie kopii dzienników transakcyjnych ........................................................93
Odtwarzanie pojedynczych stron ..............................................................................94
Weryfikacja kopii poprzez SMO ..............................................................................95
Zmiana nazwy i lokalizacji bazy ..............................................................................96
Odtwarzanie serwera ................................................................................................96
Rozdzia(cid:228) 4. Kopie zapasowe ............................................................................. 81
Spis tre(cid:264)ci
5
Rozdzia(cid:228) 5. Bezpiecze(cid:254)stwo ............................................................................. 99
Model dog(cid:225)(cid:266)bnej obrony .................................................................................................99
Zagro(cid:298)enia ................................................................................................................99
Zabezpieczenia na poziomie systemu Windows ...........................................................100
Lista us(cid:225)ug ..............................................................................................................101
Us(cid:225)ugi serwera SQL ...............................................................................................101
Protoko(cid:225)y sieciowe serwera SQL ...........................................................................102
Microsoft Baseline Security Analyzer ....................................................................102
Zabezpieczenia na poziomie instancji ..........................................................................103
Model bezpiecze(cid:276)stwa serwera SQL 2005 .............................................................103
Uwierzytelnianie ....................................................................................................104
Role serwera ...........................................................................................................108
Dodatkowe po(cid:286)wiadczenia .....................................................................................109
Delegowanie po(cid:286)wiadcze(cid:276) .....................................................................................109
Dodatkowe funkcje serwera ...................................................................................110
Zabezpieczenia na poziomie bazy danych ....................................................................111
Zaufane bazy danych ..............................................................................................111
Konta u(cid:298)ytkowników .............................................................................................111
Role bazy danych ...................................................................................................112
Schematy ................................................................................................................115
Monitorowanie .............................................................................................................116
Dzienniki systemu Windows ..................................................................................116
Dzienniki serwera SQL ..........................................................................................116
Cz(cid:246)(cid:264)(cid:232) II Programowanie .......................................................... 119
Rozdzia(cid:228) 6. Narz(cid:246)dzia, tabele, zmienne i typy danych ...................................... 121
Narz(cid:266)dzia ......................................................................................................................121
Diagramy baz danych .............................................................................................122
Szablony instrukcji .................................................................................................123
SQLCMD ...............................................................................................................124
BCP i BULK INSERT ...........................................................................................125
Typy danych .................................................................................................................125
Warto(cid:286)(cid:252) NULL .......................................................................................................126
Typy daty i czasu ....................................................................................................127
Typy znakowe ........................................................................................................129
Du(cid:298)e obiekty ..........................................................................................................129
Typy CLR ..............................................................................................................130
XML .......................................................................................................................130
Zmienne ........................................................................................................................132
Inicjowanie zmiennych ...........................................................................................132
Konwersja typów ....................................................................................................133
Warto(cid:286)(cid:252) czy wyra(cid:298)enie? .........................................................................................133
Zmienne tabelaryczne .............................................................................................133
Tabele ...........................................................................................................................134
Tworzenie tabel ......................................................................................................134
Ograniczenia ..........................................................................................................137
Modyfikowanie tabeli .............................................................................................139
Tabele tymczasowe ................................................................................................142
Instancje u(cid:298)ytkowników ...............................................................................................143
Pod(cid:225)(cid:261)czenie pliku bazy danych ..............................................................................144
SQL Server Express Utility ....................................................................................145
6
SQL Server 2005 Express. Skuteczne rozwi(cid:241)zania
Rozdzia(cid:228) 7. Indeksy ........................................................................................ 147
Podstawowe struktury danych ......................................................................................147
Sterta ......................................................................................................................148
Indeks zgrupowany ................................................................................................148
Indeks niezgrupowany ............................................................................................151
Mechanizmy odczytywania i modyfikowania danych ..................................................152
Odczytywanie wierszy ...........................................................................................152
Wstawianie wierszy ................................................................................................156
Usuwanie wierszy ..................................................................................................157
Modyfikowanie wierszy .........................................................................................157
Tworzenie indeksów .....................................................................................................158
Opcje indeksów ......................................................................................................158
Indeksy z(cid:225)o(cid:298)one ......................................................................................................159
Dodatkowe kolumny indeksów ..............................................................................160
Indeksowanie kolumn wyliczeniowych ..................................................................160
Wielko(cid:286)(cid:252) indeksów ................................................................................................161
Statystyki ......................................................................................................................162
Wy(cid:225)(cid:261)czenie aktualizacji statystyki ..........................................................................163
Zarz(cid:261)dzanie indeksami .................................................................................................164
Tworzenie widoków .....................................................................................................165
Restrykcje ...............................................................................................................165
Opcje widoków ......................................................................................................167
Indeksowanie widoków ................................................................................................170
Korzystanie z indeksów ..........................................................................................171
Indeksowanie wybranych wierszy tabeli ................................................................173
Partycjonowanie danych ...............................................................................................174
Modyfikowanie widoku ................................................................................................175
Sprawdzanie zale(cid:298)no(cid:286)ci mi(cid:266)dzy obiektami ............................................................175
Aktualizacja metadanych .......................................................................................176
(cid:224)a(cid:276)cuchy w(cid:225)asno(cid:286)ci ......................................................................................................177
Funkcje skalarne ...........................................................................................................179
T-SQL ....................................................................................................................180
CLR ........................................................................................................................182
Proste funkcje tabelaryczne ..........................................................................................186
Ograniczenie dost(cid:266)pu do danych ............................................................................187
Skrypty administracyjne .........................................................................................187
Modu(cid:225)owo(cid:286)(cid:252) ...........................................................................................................187
Z(cid:225)o(cid:298)one funkcje tabelaryczne .......................................................................................188
Procedury sk(cid:225)adowane ..................................................................................................191
Tworzenie procedur ................................................................................................192
Przetwarzanie procedur przez serwer SQL 2005 ....................................................193
Interfejs procedur ...................................................................................................195
Wywo(cid:225)ywanie procedur .........................................................................................199
Zmiana kontekstu wykonania .................................................................................205
Wyzwalacze ..................................................................................................................206
Wyzwalacze DML ..................................................................................................207
Wyzwalacze DDL ..................................................................................................211
Wyzwalacze logowania ..........................................................................................213
Rozdzia(cid:228) 10. Procedury i wyzwalacze ................................................................ 191
Rozdzia(cid:228) 8. Widoki ......................................................................................... 165
Rozdzia(cid:228) 9. Funkcje u(cid:276)ytkownika .................................................................... 179
Spis tre(cid:264)ci
7
Rozdzia(cid:228) 11. Obs(cid:228)uga b(cid:228)(cid:246)dów ............................................................................ 215
Rozdzia(cid:228) 12. Kryptografia ................................................................................. 229
Komunikaty b(cid:225)(cid:266)dów .....................................................................................................215
Kategorie b(cid:225)(cid:266)dów ...................................................................................................216
Zasi(cid:266)g b(cid:225)(cid:266)dów ........................................................................................................217
Niespójna obs(cid:225)uga b(cid:225)(cid:266)dów ......................................................................................218
Tworzenie w(cid:225)asnych komunikatów b(cid:225)(cid:266)dów ...........................................................219
Blok TRY … CATCH ..................................................................................................220
Unikajmy b(cid:225)(cid:266)dów, zamiast je przechwytywa(cid:252) .......................................................220
Informacje o b(cid:225)(cid:266)dach ..............................................................................................221
B(cid:225)(cid:266)dy, których nie przechwycimy ..........................................................................221
Transakcje ..............................................................................................................222
Zg(cid:225)aszanie b(cid:225)(cid:266)dów ........................................................................................................225
Ponowne zg(cid:225)oszenie b(cid:225)(cid:266)du .....................................................................................226
Algorytmy ....................................................................................................................229
Si(cid:225)a kryptografii .....................................................................................................230
Algorytmy symetryczne .........................................................................................231
Algorytmy asymetryczne .......................................................................................234
Funkcje mieszania ..................................................................................................235
Klucze ...........................................................................................................................237
G(cid:225)ówny klucz serwera ............................................................................................237
G(cid:225)ówny klucz bazy danych ....................................................................................239
Klucze u(cid:298)ytkowników ............................................................................................240
Podpisy cyfrowe ...........................................................................................................243
Sprawdzanie autentyczno(cid:286)ci danych ......................................................................244
Podpisywanie modu(cid:225)ów kodu .................................................................................246
Szyfrowanie danych .....................................................................................................248
Ograniczenie dost(cid:266)pu do klucza .............................................................................249
Indeksowanie szyfrogramów ..................................................................................250
Cz(cid:246)(cid:264)(cid:232) III Praca z danymi ........................................................... 253
Rozdzia(cid:228) 13. Wykonywanie zapyta(cid:254) przez serwer SQL 2005 ............................... 255
Rozdzia(cid:228) 14. Pobieranie danych ........................................................................ 275
Logiczny plan wykonania .............................................................................................256
Kolejno(cid:286)(cid:252) wykonywania klauzul instrukcji SELECT ............................................256
Fizyczny plan wykonania .............................................................................................264
Optymalizator.........................................................................................................264
Odczytywanie informacji o planach wykona(cid:276) ........................................................266
Podstawowe operatory ...........................................................................................269
Automatyczne tworzenie zapyta(cid:276) .................................................................................275
Graficzny konstruktor zapyta(cid:276) ...............................................................................275
Szablony zapyta(cid:276) ....................................................................................................278
Wyszukiwanie danych ..................................................................................................278
Klauzula WHERE ..................................................................................................278
Wydajne wyszukiwanie danych .............................................................................280
(cid:224)(cid:261)czenie (cid:296)róde(cid:225) danych ................................................................................................283
Z(cid:225)(cid:261)czenie krzy(cid:298)owe ................................................................................................283
Z(cid:225)(cid:261)czenia naturalne ................................................................................................284
Z(cid:225)(cid:261)czenia zewn(cid:266)trzne .............................................................................................285
Z(cid:225)(cid:261)czenia wielokrotne ............................................................................................285
Z(cid:225)(cid:261)czenia w(cid:225)asne ....................................................................................................287
8
SQL Server 2005 Express. Skuteczne rozwi(cid:241)zania
Rozdzia(cid:228) 16. Podzapytania ............................................................................... 307
Rozdzia(cid:228) 17. Modyfikowanie danych ................................................................. 323
Rozdzia(cid:228) 15. Grupowanie danych ...................................................................... 293
(cid:224)(cid:261)czenie wyników zapyta(cid:276) ....................................................................................287
Operator APPLY ....................................................................................................289
Porz(cid:261)dkowanie zwracanych wierszy i ograniczanie ich liczby .....................................289
Klauzula ORDER BY ............................................................................................289
Klauzula TOP .........................................................................................................291
Klauzula TABLESAMPLE ....................................................................................292
Funkcje grupuj(cid:261)ce ........................................................................................................293
Klauzula GROUP BY ...................................................................................................295
Operator CUBE ......................................................................................................295
Operator ROLLUP .................................................................................................297
Funkcja GROUPING() ...........................................................................................297
Klauzula HAVING .......................................................................................................298
Klauzula OVER ............................................................................................................298
Partycje ...................................................................................................................299
Funkcje rankingu ....................................................................................................299
Operator PIVOT .....................................................................................................303
Operator UNPIVOT ...............................................................................................305
Podzapytania jako zmienne ..........................................................................................307
Podzapytania niepowi(cid:261)zane ....................................................................................308
Podzapytania powi(cid:261)zane ........................................................................................310
Predykat EXISTS ...................................................................................................312
Podzapytania jako (cid:296)ród(cid:225)a danych .................................................................................314
Tabele pochodne ....................................................................................................314
Proste CTE .............................................................................................................316
Rekurencyjne CTE .................................................................................................318
Wstawianie danych .......................................................................................................323
Wstawianie wyników zapyta(cid:276) ................................................................................324
Wstawianie wyników procedur ..............................................................................326
Klauzula OUTPUT .................................................................................................326
Usuwanie danych ..........................................................................................................326
Instrukcja DELETE ................................................................................................327
Instrukcja TRUNCATE ..........................................................................................330
Modyfikowanie danych ................................................................................................331
Klauzula OUTPUT .................................................................................................331
Eksport i import danych ...............................................................................................332
Program BCP .........................................................................................................332
Instrukcja BULK INSERT .....................................................................................332
Funkcja OPENROWSET() .....................................................................................333
Transakcje ....................................................................................................................336
W(cid:225)a(cid:286)ciwo(cid:286)ci transakcji ...........................................................................................336
Zagnie(cid:298)d(cid:298)anie transakcji ........................................................................................338
Blokady ........................................................................................................................339
Charakterystyka blokad ..........................................................................................340
Jak serwer SQL 2005 zarz(cid:261)dza blokadami? ...........................................................344
Informacje o blokadach ..........................................................................................345
Zakleszczenia .........................................................................................................347
Modyfikowanie blokad zak(cid:225)adanych przez serwer .................................................349
Rozdzia(cid:228) 18. Transakcje, blokady i wersjonowanie ............................................ 335
Spis tre(cid:264)ci
9
Poziomy izolowania transakcji .....................................................................................351
Read Uncommited ..................................................................................................351
Read Commited ......................................................................................................352
Repeatable Read .....................................................................................................352
Serializable .............................................................................................................353
Wersjonowanie wierszy ................................................................................................354
Read Commited Snapshot ......................................................................................354
Snapshot .................................................................................................................355
Magazyn wersji ......................................................................................................357
Cz(cid:246)(cid:264)(cid:232) IV Optymalizacja ............................................................ 359
Rozdzia(cid:228) 19. Mierzenie wydajno(cid:264)ci ................................................................... 361
Narz(cid:266)dzia ......................................................................................................................361
Monitor wydajno(cid:286)ci ................................................................................................361
Raporty konsoli SSMSE .........................................................................................364
Performance Dashboard Reports ............................................................................366
(cid:285)ledzenie aktywno(cid:286)ci u(cid:298)ytkowników .....................................................................368
Widoki dynamiczne ................................................................................................370
Linia bazowa ................................................................................................................370
Próbkowanie danych zwracanych przez liczniki serwera SQL 2005 .....................371
Czyszczenie buforów i dokumentowanie wydajno(cid:286)ci zapyta(cid:276) ...............................372
W(cid:261)skie gard(cid:225)a ...............................................................................................................373
Na co czeka serwer SQL 2005? ..............................................................................373
Procesor ..................................................................................................................376
Pami(cid:266)(cid:252) ....................................................................................................................378
Dysk .......................................................................................................................380
Sie(cid:252) .........................................................................................................................382
Dziennik transakcyjny ............................................................................................382
Baza tempdb ...........................................................................................................383
Indeksy ...................................................................................................................384
Blokady i zatrzaski .................................................................................................387
Wolno wykonywane instrukcje ..............................................................................390
Optymalizacja baz danych ............................................................................................394
Struktura logiczna ..................................................................................................394
Struktura fizyczna ..................................................................................................397
Baza tempdb ...........................................................................................................399
Optymalizacja zapyta(cid:276) .................................................................................................400
Uwagi dotycz(cid:261)ce optymalizacji zapyta(cid:276) ................................................................400
Refaktoryzacja kursorów ........................................................................................403
Indeksy .........................................................................................................................405
U(cid:298)yteczne i nieu(cid:298)yteczne indeksy ..........................................................................406
Defragmentacja i przebudowa indeksów ................................................................407
Typowe b(cid:225)(cid:266)dy .........................................................................................................408
Blokady ........................................................................................................................409
Rozbudowa serwera ......................................................................................................410
Skorowidz .................................................................................... 411
Rozdzia(cid:228) 20. Poprawa wydajno(cid:264)ci ..................................................................... 393
Rozdzia(cid:228) 10.
Procedury i wyzwalacze
Procedury sk(cid:225)adowane i wyzwalacze to wykonuj(cid:261)ce okre(cid:286)lone zadania podprogramy
sk(cid:225)adaj(cid:261)ce si(cid:266) z dowolnych instrukcji T-SQL lub CLR. W przeciwie(cid:276)stwie do funkcji
u(cid:298)ytkownika, procedury i wyzwalacze mog(cid:261) modyfikowa(cid:252) stan bazy danych, dzi(cid:266)ki
czemu obiekty obu typów u(cid:298)ywane s(cid:261) do implementacji logiki aplikacyjnej po stronie
bazy danych.
Procedury sk(cid:228)adowane
1.Poprawiaj(cid:261) bezpiecze(cid:276)stwo bazy danych. Odbieraj(cid:261)c u(cid:298)ytkownikom
uprawnienia do tabel i zezwalaj(cid:261)c im na wywo(cid:225)ywanie procedur, zyskujemy
pewno(cid:286)(cid:252), (cid:298)e nikt przypadkowo nie wykona b(cid:225)(cid:266)dnej instrukcji, np. instrukcji
UPDATE bez klauzuli WHERE.Procedury sk(cid:225)adowane s(cid:261) te(cid:298) jedyn(cid:261) skuteczn(cid:261)
obron(cid:261) przed atakami polegaj(cid:261)cymi na iniekcji kodu SQL. Dodatkowo
ukrywaj(cid:261) one struktur(cid:266) bazy danych przed u(cid:298)ytkownikami.
Iniekcja kodu SQL polega na podstawieniu pod zmienne dowolnych instrukcji j(cid:246)zyka
SQL. Poniewa(cid:276) SQL jest j(cid:246)zykiem interpretowanym, odczytuj(cid:241)ca warto(cid:264)(cid:232) zmiennej
instrukcja mo(cid:276)e zosta(cid:232) zmodyfikowana, a nast(cid:246)pnie wykonana. To zagro(cid:276)enie mo(cid:276)na
wyeliminowa(cid:232), przekazuj(cid:241)c warto(cid:264)ci zmiennych jako parametry wywo(cid:228)ania procedur.
Drugie zagro(cid:276)enie zwi(cid:241)zane jest z wykonywaniem dynamicznie skonstruowanych in-
strukcji T-SQL. Konstruowanie dynamicznych instrukcji w ciele procedury nie chroni
przed iniekcj(cid:241) kodu. Dlatego nale(cid:276)y sprawdza(cid:232) poprawno(cid:264)(cid:232) wszystkich zmiennych
i uniemo(cid:276)liwia(cid:232) ich b(cid:228)(cid:246)dn(cid:241) interpretacj(cid:246) przez serwer SQL 2005. Je(cid:276)eli np. warto-
(cid:264)ci(cid:241) zmiennej powinna by(cid:232) nazwa obiektu bazy danych, nale(cid:276)y umie(cid:264)ci(cid:232) j(cid:241) w wy-
wo(cid:228)aniu funkcji QUOTENAME().
2.Tworz(cid:261),wraz z widokami i funkcjami u(cid:298)ytkownika, warstw(cid:266) abstrakcji
oddzielaj(cid:261)c(cid:261) u(cid:298)ytkowników od tabel. Je(cid:298)eli u(cid:298)ytkownicy nie odwo(cid:225)uj(cid:261) si(cid:266)
bezpo(cid:286)rednio do tabel, zmiana ich struktury nie wymaga zmian aplikacji klienckich1.
1 Zmiana definicji procedur czy widoków, o ile tylko ich interfejs pozostanie taki sam, jest dla u(cid:298)ytkowników
niewidoczna.
192
Cz(cid:246)(cid:264)(cid:232) II (cid:105) Programowanie
3.Zwi(cid:266)kszaj(cid:261) wydajno(cid:286)(cid:252) instrukcji. Domy(cid:286)lnie, skompilowane plany wykonania
procedur sk(cid:225)adowanych s(cid:261) buforowane i wykorzystywane do ich kolejnych
wywo(cid:225)a(cid:276). Ponadto wszystkie dane potrzebne do wykonania procedury
u(cid:298)ytkownik jednorazowo przesy(cid:225)a poprzez sie(cid:252) w postaci nazwy procedury
i parametrów jej wywo(cid:225)ania.
4.Zmniejszaj(cid:261) zasi(cid:266)g ewentualnych b(cid:225)(cid:266)dów i umo(cid:298)liwiaj(cid:261) ich spójn(cid:261) obs(cid:225)ug(cid:266).
Wykorzystanie procedur do obs(cid:225)ugi b(cid:225)(cid:266)dów zosta(cid:225)o opisane w nast(cid:266)pnym
rozdziale.
Tworzenie procedur
Procedury sk(cid:225)adowane:
1.pozwalaj(cid:261) na wywo(cid:225)ywanie innych procedur lub rekurencyjne wywo(cid:225)ywanie
samej siebie; maksymalny poziom zagnie(cid:298)d(cid:298)enia procedur wynosi 32, po jego
przekroczeniu serwer SQL 2005 zg(cid:225)asza b(cid:225)(cid:261)d i wycofuje wszystkie znajduj(cid:261)ce
si(cid:266) na stosie wywo(cid:225)a(cid:276) procedury,
2.akceptuj(cid:261) do 2 100 parametrów wywo(cid:225)ania; parametrami wywo(cid:225)ania procedury
nie mog(cid:261) by(cid:252) dane tabelaryczne,
3.zwracaj(cid:261) warto(cid:286)(cid:252) statusu, domy(cid:286)lnie informuj(cid:261)c(cid:261) o tym, czy procedura zosta(cid:225)a
wykonana bez b(cid:225)(cid:266)dów,
4.mog(cid:261) zwraca(cid:252) wiele warto(cid:286)ci w postaci parametrów wyj(cid:286)ciowych
(ang.output),
5.ich definicje mog(cid:261) zosta(cid:252) zaszyfrowane za pomoc(cid:261) opcji ENCRYPTION,
6.umo(cid:298)liwiaj(cid:261) wy(cid:225)(cid:261)czenie buforowania planu ich wykonania za pomoc(cid:261) opcji
RECOMPILE i zmiany kontekstu wykonania przy u(cid:298)yciu opcji EXECUTE AS.
Te kwestie zosta(cid:225)y opisane w dalszej cz(cid:266)(cid:286)ci rozdzia(cid:225)u.
Uwaga na dodatkowe instrukcje wsadu
Cia(cid:225)o procedury nie jest umieszczane w bloku BEGIN … END. W rezultacie wszystkie
instrukcje wsadu wyst(cid:266)puj(cid:261)ce po klauzuli CREATE PROCEDURE … AS zostan(cid:261) dodane do
cia(cid:225)a procedury. Zwracamy na to uwag(cid:266), bo cz(cid:266)sto podczas modyfikowania czy testo-
wania procedur w tym samym oknie edytora umieszcza si(cid:266) inne instrukcje, np. wywo-
(cid:225)uje si(cid:266) testowan(cid:261) procedur(cid:266) (rysunek 10.1).
Rozwi(cid:241)zaniem tego problemu jest jawne ko(cid:254)czenie cia(cid:228)a procedury dyrektyw(cid:241) GO.
Najlepiej wyrobi(cid:232) sobie nawyk stawiania s(cid:228)owa GO zaraz po zadeklarowaniu nag(cid:228)ówka
procedury, tak samo jak natychmiast po rozpocz(cid:246)ciu bloku BEGIN powinni(cid:264)my wpi-
sywa(cid:232) ko(cid:254)cz(cid:241)c(cid:241) go instrukcj(cid:246) END.
Rozdzia(cid:228) 10. (cid:105) Procedury i wyzwalacze
193
Rysunek 10.1. Je(cid:298)eli spróbujemy wywo
w jej ciele, to próba zako
zagnie(cid:298)d(cid:298)enia procedur
Przetwarzanie procedur przez serwer SQL 2005
(cid:276)czy si(cid:266) zg(cid:225)oszeniem przez serwer b
(cid:225)a(cid:252) procedur(cid:266), której wywo(cid:225)anie przypadkowo umie
(cid:286)cili(cid:286)my
(cid:225)(cid:266)du przekroczenia maksymalnego poziomu
Przetwarzanie procedury przebiega dwuetapowo.
1.Podczas tworzenia procedury sprawdzana jest poprawno(cid:286)(cid:252) syntaktyczna
instrukcji CREATE PROCEDURE. Je(cid:298)eli instrukcja jest poprawna, procedura
zostanie utworzona, a jej definicja i metadane zapisane w bazie danych.
2.Podczas wywo(cid:225)ywania procedury wyszukiwane s(cid:261) obiekty, do których
odwo(cid:225)uje si(cid:266) procedura. Je(cid:298)eli który(cid:286) z nich jest w(cid:225)asno(cid:286)ci(cid:261) innego
u(cid:298)ytkownika ni(cid:298) w(cid:225)a(cid:286)ciciel procedury, serwer SQL 2005 sprawdzi równie(cid:298),
czy procedura ma nadane wystarczaj(cid:261)ce uprawniania do wszystkich obiektów
bazowych. Nast(cid:266)pnie dochodzi do optymalizacji procedury, w której wyniku
najlepszy ze znalezionych plan jej wykonania jest kompilowany, wykonywany
i zapisywany w buforze procedur (rysunek 10.2).
Je(cid:276)eli plan wykonania procedury znajduje si(cid:246) w buforze, serwer SQL 2005 b(cid:246)-
dzie korzysta(cid:228) z niego przy kolejnych wywo(cid:228)aniach procedury, a wi(cid:246)c b(cid:246)d(cid:241) one
wymaga(cid:228)y jedynie odczytania i wykonania wcze(cid:264)niej skompilowanego planu wy-
konania. Je(cid:276)eli jednak nazwa procedury musia(cid:228)a by(cid:232) sprawdzona, jej kolejne wy-
wo(cid:228)ania b(cid:246)d(cid:241) wymaga(cid:228)y ponownego sprawdzenia, optymalizacji i kompilacji. Po-
niewa(cid:276) serwer SQL 2005 zak(cid:228)ada, (cid:276)e procedury o nazwach zaczynaj(cid:241)cych si(cid:246) od
prefiksu sp_ znajduj(cid:241) si(cid:246) w bazie master, nie nale(cid:276)y go u(cid:276)ywa(cid:232) w nazwach w(cid:228)asnych
procedur.
Cz(cid:246)(cid:264)(cid:232) II (cid:105) Programowanie
194
Rysunek 10.2.
Taki proces
przetwarzania
procedur powoduje,
(cid:298)e ewentualne b(cid:225)(cid:266)dy,
z wyj(cid:261)tkiem
syntaktycznych,
ujawni(cid:261) si(cid:266) dopiero
podczas pierwszego
ich wywo(cid:225)ania
Opó(cid:274)nione sprawdzanie nazw
Skoro podczas tworzenia procedury nie jest przygotowywany plan jej wykonania, mo(cid:298)-
liwe jest utworzenie procedur odwo(cid:225)uj(cid:261)cych si(cid:266) do nieistniej(cid:261)cych obiektów — tabel,
widoków, funkcji czy innych procedur2:
USE AdventureWorks
GO
CREATE PROC uspTest
AS
SELECT kol1
FROM nieMaTakiejTabeli
------------------------------------------------------------
Command(s) completed successfully.
Oczywi(cid:286)cie, próba wywo(cid:225)ania procedury sko(cid:276)czy si(cid:266) b(cid:225)(cid:266)dem:
EXEC uspTest
------------------------------------------------------------
Msg 208, Level 16, State 1, Procedure uspTest, Line 3
Invalid object name nieMaTakiejTabeli .
W zwi(cid:261)zku z tym, (cid:298)e serwer SQL 2005 sprawdza, czy obiekty, do których odwo(cid:225)uje si(cid:266)
tworzona lub zmieniana procedura, istniej(cid:261), odwo(cid:225)ania do nich musz(cid:261) by(cid:252) poprawne:
ALTER PROC uspTest
AS
SELECT kol7
FROM tab1
------------------------------------------------------------
Msg 207, Level 16, State 1, Procedure uspTest, Line 3
Invalid column name kol7 .
2 Jak pokazali(cid:286)my we wcze(cid:286)niejszym przyk(cid:225)adzie, mo(cid:298)liwo(cid:286)(cid:252) odwo(cid:225)ania si(cid:266) w ciele procedury do niej
samej (a wi(cid:266)c do nieistniej(cid:261)cej jeszcze procedury) pozwala na ograniczone stosowanie rekurencji.
Rozdzia(cid:228) 10. (cid:105) Procedury i wyzwalacze
195
Interfejs procedur
W nag(cid:225)ówku procedury nale(cid:298)y okre(cid:286)li(cid:252) nazwy i typy jej parametrów. Parametry pro-
cedury dziel(cid:261) si(cid:266) na:
1.parametry wywo(cid:225)ania, w ciele procedury b(cid:266)d(cid:261) one dost(cid:266)pne jako jej zmienne
lokalne,
2.parametry wyj(cid:286)ciowe; dodaj(cid:261)c po typie parametru s(cid:225)owo kluczowe OUTPUT,
dodatkowo umo(cid:298)liwimy procedurze zwrócenie warto(cid:286)ci takiego parametru.
W ramach procedur nale(cid:298)y okre(cid:286)li(cid:252) domy(cid:286)ln(cid:261) warto(cid:286)(cid:252) parametrów i sprawdzi(cid:252),
czy podane przez u(cid:298)ytkownika dane s(cid:261) prawid(cid:225)owe. Poni(cid:298)sza procedura mo(cid:298)e by(cid:252)
wywo(cid:225)ana z jednym parametrem — nazw(cid:261) kategorii. Je(cid:298)eli nie zostanie ona podana,
procedura zwróci informacje o wszystkich produktach: nazw(cid:266) kategorii, podkategorii
i produktu uzupe(cid:225)nione o cen(cid:266) i pozycj(cid:266) produktu w ramach podkategorii. Je(cid:286)li nato-
miast nazwa kategorii b(cid:266)dzie zawiera(cid:225)a niedozwolone znaki, dzia(cid:225)anie procedury zo-
stanie przerwane3:
EXEC uspDaneProduktu
EXEC uspDaneProduktu a OR 1=1--
3 Funkcja ufnLikeRegEx zosta(cid:225)a utworzona w poprzednim rozdziale.
CREATE Procedure uspDaneProduktu (@Categoryname varchar(50) = )
AS
IF dbo.ufnLikeRegEx (@Categoryname, ^[a-zA-Z s’]*$ ) = 0
IF dbo.ufnLikeRegEx (@Categoryname, /( 27)|(--)|( 23)|(#)/ix ) = 1
RETURN -1 -- nazwa nie sk(cid:225)ada si(cid:266) z samych liter i symbolu
RETURN -1 -- nazwa zawiera zabronione znaki
DENSE_RANK() OVER -- funkcja DENSE_RANK() ponumeruje wiersze
(PARTITION BY sc.Name -- w ramach poszczególnych podkategorii
ORDER BY p.ListPrice DESC) as Pozycja -- numerowane wiersze musz
(cid:261) by(cid:252) posortowane
SELECT c.Name AS Kategoria, sc.Name AS Podkategoria, p.Name Produkt, p.ListPrice,
FROM Production.Product p
JOIN Production.ProductSubcategory sc ON p.ProductSubcategoryID =
sc.ProductSubcategoryID
JOIN Production.ProductCategory c ON sc.ProductCategoryID = c.ProductCategoryID
WHERE c.Name LIKE @CategoryName
ORDER BY c.Name
(cid:261) zwróconych wierszy
RETURN @@ROWCOUNT -- zast(cid:266)pujemy systemowy status wykonania liczb
Parametry do procedury mo(cid:298)na przekaza(cid:252) na dwa sposoby.
1.Podaj(cid:261)c rozdzielone przecinkami warto(cid:286)ci. W takim przypadku kolejno(cid:286)(cid:252)
warto(cid:286)ci musi odpowiada(cid:252) kolejno(cid:286)ci parametrów, a dwa przecinki nie mog(cid:261)
wyst(cid:261)pi(cid:252) bezpo(cid:286)rednio obok siebie. Oznacza to, (cid:298)e nawet parametry, dla
których zdefiniowano warto(cid:286)ci domy(cid:286)lne, musz(cid:261) by(cid:252) podane, chyba (cid:298)e wyst(cid:266)puj(cid:261)
na ko(cid:276)cu listy parametrów.
2.Podaj(cid:261)c nazw(cid:266) parametru i przypisuj(cid:261)c mu warto(cid:286)(cid:252).
W poni(cid:298)szym przyk(cid:225)adzie pokazujemy, jak wywo(cid:225)a(cid:252) utworzon(cid:261) w poprzednim punkcie
procedur(cid:266) bez parametrów i z b(cid:225)(cid:266)dnym parametrem:
196
Cz(cid:246)(cid:264)(cid:232) II (cid:105) Programowanie
oraz z podaniem nazwy kategorii i sprawdzeniem zwróconego przez procedur(cid:266) statusu:
DECLARE @l INT
EXEC @l = uspDaneProduktu Accessories
PRINT @l
Poniewa(cid:298) procedury mog(cid:261) modyfikowa(cid:252) stan bazy, cz(cid:266)sto s(cid:261) u(cid:298)ywane do zautomaty-
zowania zada(cid:276) administracyjnych, np. nadawania lub odbierania uprawnie(cid:276). W poni(cid:298)-
szym przyk(cid:225)adzie pokazujemy rozbudowan(cid:261) procedur(cid:266) administracyjn(cid:261), której dzia(cid:225)anie
polega na nadawaniu wskazanemu u(cid:298)ytkownikowi uprawnie(cid:276) do wywo(cid:225)ywania wszyst-
kich procedur bazy danych:
(cid:286)my w rozdziale 11.
CREATE PROCEDURE uspGrantExec @user SYSNAME
AS
DECLARE @cSQL varchar(8000)
DECLARE @Obj varchar(8000)
DECLARE @Cur CURSOR
BEGIN TRY -- blok TRY … CATCH opisali
SET @Cur = CURSOR FOR SELECT QUOTENAME(routine_schema) + . +
QUOTENAME(routine_name)
FROM information_schema.routines
WHERE routine_name NOT LIKE dt_ AND routine_type = PROCEDURE
OPEN @Cur
FETCH NEXT FROM @Cur INTO @Obj
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cSQL = GRANT EXEC ON + @Obj + TO + @user
EXEC (@cSQL)
FETCH NEXT FROM @Cur INTO @Obj
END
DEALLOCATE @Cur
END TRY
BEGIN CATCH
END CATCH
RETURN -1 -- w rozdziale 11. wyja(cid:286)nili(cid:286)my te(cid:298) problemy zwi(cid:261)zane z tak(cid:261)
-- obs(cid:225)ug(cid:261) wyj(cid:261)tków
(cid:297)eby zezwoli(cid:252) u(cid:298)ytkownikowi na wywo(cid:225)ywanie wszystkich procedur, wystarczy wy-
kona(cid:252) poni(cid:298)szy skrypt:
DECLARE @w INT
EXEC @w = uspGrantExec blabla
IF @w 0 PRINT B(cid:273)(cid:230)d
------------------------------------------------------------
B(cid:273)(cid:230)d
DECLARE @w INT
EXEC @w = uspGrantExec Danka
IF @w 0 PRINT B(cid:273)(cid:230)d
------------------------------------------------------------
Command(s) completed successfully.
Wstawanie wyniku procedury do tabeli
Procedura mo(cid:298)e by(cid:252) wywo(cid:225)ana w ramach instrukcji INSERT INTO. Takie wywo(cid:225)anie
procedury spowoduje wstawienie zwracanych przez ni(cid:261) wyników do tabeli. Liczba i typ
kolumn tabeli musz(cid:261) odpowiada(cid:252) strukturze wyniku dzia(cid:225)ania procedury:
Rozdzia(cid:228) 10. (cid:105) Procedury i wyzwalacze
197
CREATE PROCEDURE uspPrac @Nazwisko nvarchar(50) = , @Imie nvarchar(50) =
AS
SELECT FirstName, LastName, JobTitle
FROM HumanResources.vEmployeeDepartment
WHERE FirstName LIKE @Imie AND LastName LIKE @Nazwisko
GO
CREATE TABLE #Pracownicy
(Imie nvarchar(50),
Nazwisko nvarchar(50),
Stanowisko nvarchar(50))
GO
INSERT INTO #Pracownicy
EXEC uspPrac H , D
SELECT *
FROM #Pracownicy
------------------------------------------------------------
Doris Hartwig Production Technician - WC10
Douglas Hite Production Technician - WC45
David Hamilton Production Supervisor - WC40
Don Hall Production Technician - WC50
Gdyby(cid:286)my w ramach procedury utworzyli tabel(cid:266) tymczasow(cid:261), zosta(cid:225)aby automatycznie
zniszczona po zako(cid:276)czeniu jej dzia(cid:225)ania, a wi(cid:266)c wywo(cid:225)anie procedury powiod(cid:225)oby si(cid:266),
ale próba odczytania wyników sko(cid:276)czy(cid:225)aby si(cid:266) b(cid:225)(cid:266)dem:
ALTER PROCEDURE uspPrac @Nazwisko nvarchar(50) = , @Imie nvarchar(50) =
AS
CREATE TABLE #tmpPracownicy
(Imie nvarchar(50),
Nazwisko nvarchar(50),
Stanowisko nvarchar(50))
INSERT INTO #tmpPracownicy
SELECT FirstName, LastName, JobTitle
FROM HumanResources.vEmployeeDepartment
WHERE FirstName LIKE @Imie AND LastName LIKE @Nazwisko
GO
EXEC uspPrac H , D
SELECT *
FROM #tmpPracownicy
------------------------------------------------------------
(4 row(s) affected)
Msg 208, Level 16, State 0, Line 1
Invalid object name #tmpPracownicy .
Parametry wyj(cid:264)ciowe
Parametry wyj(cid:286)ciowe w rzeczywisto(cid:286)ci dzia(cid:225)aj(cid:261) jak znane z obiektowych j(cid:266)zyków
programowania parametry przekazywane przez referencje. Mo(cid:298)emy za ich pomoc(cid:261)
przekaza(cid:252) warto(cid:286)(cid:252) do procedury, która dowolnie j(cid:261) zmodyfikuje i ode(cid:286)le wynik do
programu klienckiego:
CREATE PROCEDURE uspParamOut @l1 INT, @l2 INT OUTPUT
AS
SET @l2= @l1*@l2
198
GO
Cz(cid:246)(cid:264)(cid:232) II (cid:105) Programowanie
DECLARE @w INT
SET @w=10
EXEC uspParamOut 5,@w OUTPUT
SELECT @w
------------------------------------------------------------
50
Parametry tabelaryczne
Mo(cid:298)liwo(cid:286)(cid:252) przekazywania jako parametru wywo(cid:225)ania procedur tabel, pozwoli(cid:225)aby m.in.
na tworzenie procedur wywo(cid:225)ywanych z dowoln(cid:261) liczb(cid:261) parametrów (jednym para-
metrem by(cid:225)oby jedno pole tabeli) lub na wykonanie dla ka(cid:298)dego wiersza tabeli w ra-
mach procedury okre(cid:286)lonej operacji. Niestety, parametrem procedury nie mog(cid:261) by(cid:252)
dane typu TABLE.
W zamian za to serwer SQL 2005 obs(cid:225)uguje standard XML, pozwalaj(cid:261)cy na wywo(cid:225)y-
wanie procedur z parametrami typu XML. Skoro dokument XML mo(cid:298)e zawiera(cid:252) wiele
(do 2 GB) danych, rozwi(cid:261)zanie problemu sprowadza si(cid:266) do przekazania procedurze
dokumentu XML, który nast(cid:266)pnie w ramach procedury b(cid:266)dzie przeanalizowany, a od-
czytane z niego dane u(cid:298)yte w za(cid:225)o(cid:298)onym przez nas celu:
--mo(cid:298)emy skorzysta(cid:252) z funkcji OPENXML
CREATE PROCEDURE uspParamTab @doc XML
AS
DECLARE @h INT
EXEC sp_xml_preparedocument @h OUTPUT, @doc
SELECT *
FROM OPENXML(@h, Towary/Towar )
WITH (id INT @id , -- klauzula WITH pozwala sformatowa
(cid:252) wynik
nazwa VARCHAR(30) @nazwa ,
cena DECIMAL @cena )
EXEC sp_xml_removedocument @h
SELECT @doc.value ( (Towary/Towar/@nazwa)[1] , VARCHAR(30) )
--albo z metod typu XML
Przyk(cid:225)adowa procedura jest bardzo prosta, a jej dzia(cid:225)anie sprowadza si(cid:266) do odczytania
przekazanego jako parametr wywo(cid:225)ania dokumentu XML:
DECLARE @Towary XML
SET @Towary = Towary
Towar id= 1 nazwa= Syrop cena= 1000 /
Towar id= 4 nazwa= Zio(cid:273)a cena= 200 /
/Towary
EXEC uspParamTab @Towary
------------------------------------------------------------
1 Syrop 1000
4 Zio(cid:273)a 200
Syrop
Rozdzia(cid:228) 10. (cid:105) Procedury i wyzwalacze
199
Wywo(cid:228)ywanie procedur
Procedury nale(cid:298)y wywo(cid:225)ywa(cid:252) za pomoc(cid:261) dyrektywy EXEC. W przeciwnym razie, je-
(cid:298)eli nazwa procedury nie b(cid:266)dzie pierwszym wyra(cid:298)eniem wsadu, serwer SQL 2005 nie
b(cid:266)dzie móg(cid:225) poprawnie jej zinterpretowa(cid:252)4:
sp_who
sp_who2
------------------------------------------------------------
Msg 15007, Level 16, State 1, Procedure sp_who, Line 59
sp_who2 is not a valid login or you do not have permission.
Automatyczne wywo(cid:228)anie procedury przy uruchomieniu serwera SQL 2005
Podczas uruchamiania serwer SQL 2005 sprawdza, które ze znajduj(cid:261)cych si(cid:266) w bazie
master procedur sk(cid:225)adowanych maj(cid:261) by(cid:252) automatycznie wywo(cid:225)ane. Procedur(cid:266) jako
startow(cid:261) mo(cid:298)emy oznaczy(cid:252) za pomoc(cid:261) procedury systemowej sp_procoption, usta-
wiaj(cid:261)c warto(cid:286)(cid:252) parametru startup na True5.
Plany wykonania procedur
Optymalizacja i kompilacja s(cid:261) procesami silnie obci(cid:261)(cid:298)aj(cid:261)cymi procesor. (cid:297)eby zmniej-
szy(cid:252) to obci(cid:261)(cid:298)enie, serwer SQL 2005 automatycznie buforuje i wielokrotnie wykorzy-
stuje raz obliczone optymalne plany wykona(cid:276) procedur. Ten sam plan wykonania pro-
cedury jest ponownie u(cid:298)ywany, chyba (cid:298)e:
1.serwer SQL 2005 zostanie zatrzymany i ponownie uruchomiony,
2.bufor procedur zostanie wyczyszczony; tak(cid:261) operacj(cid:266) mo(cid:298)na przeprowadzi(cid:252):
a)na poziomie serwera, wykonuj(cid:261)c instrukcj(cid:266) DBCC FREEPROCCACHE;
b)na poziomie wybranej bazy danych, wykonuj(cid:261)c instrukcj(cid:266)
DBCC FLUSHPROCINDB();
3.plan zostanie usuni(cid:266)ty z bufora z powodu d(cid:225)ugiego okresu jego nieu(cid:298)ywania
albo z powodu braku dost(cid:266)pnej pami(cid:266)ci RAM,
4.plan zostanie uznany za przestarza(cid:225)y z powodu zmiany struktury obiektów
bazowych procedury lub z powodu od(cid:286)wie(cid:298)enia statystyk opisuj(cid:261)cych
przechowywane w indeksach tych tabel dane.
(cid:297)eby przekona(cid:252) si(cid:266) o tym, jak dzia(cid:225)a mechanizm buforowania planów wykonania pro-
cedur, utworzymy procedur(cid:266), która zwraca informacje o zamówieniach z podanego
przedzia(cid:225)u czasu:
4 W j(cid:266)zyku T-SQL znak ko(cid:276)ca wiersza jest ignorowany. Nawet umieszczenie po nazwach obu
Pobierz darmowy fragment (pdf)
