Cyfroteka.pl

klikaj i czytaj online

Cyfro
Czytomierz
00941 013521 11050771 na godz. na dobę w sumie
Transact-SQL. Czarna księga - książka
Transact-SQL. Czarna księga - książka
Autor: Liczba stron: 492
Wydawca: Helion Język publikacji: polski
ISBN: 83-7361-050-2 Data wydania:
Lektor:
Kategoria: ebooki >> komputery i informatyka >> bazy danych >> inne
Porównaj ceny (książka, ebook, audiobook).
Transact-SQL to podstawowy język programowania baz danych w środowisku MS SQL Server. Transact-SQL jest zmodyfikowaną i uzupełnioną o elementy typowe dla proceduralnych języków programowania (jak zmienne i instrukcje sterujące wykonaniem programu) wersją standardu SQL-92. Dzięki temu rozszerzeniu, jego możliwości są znacznie większe niż możliwości standardowego SQL-a.

SQL Server został wyposażony w intuicyjne, a zarazem potężne narzędzie administracyjne -- konsolę SQL Server Enterprise Menager. W rezultacie część administratorów SQL Servera nie zna albo nie korzysta z możliwości języka Transact-SQL. A okazuje się, że ta sama operacja może być przeprowadzona kilkukrotnie szybciej, jeżeli zamiast konsoli użyjemy Transact-SQLa.

Książka 'Transact-SQL. Czarna Księga' to wyczerpujące i dogłębne kompendium omawiające nie tylko sam język Transact-SQL, ale również zasady projektowania baz danych, a także zarządzanie SQL Serverem za pomocą języka Transact-SQL.

Przedstawiono:

Znajdź podobne książki Ostatnio czytane w tej kategorii

Darmowy fragment publikacji:

IDZ DO IDZ DO PRZYK£ADOWY ROZDZIA£ PRZYK£ADOWY ROZDZIA£ SPIS TREĎCI SPIS TREĎCI KATALOG KSI¥¯EK KATALOG KSI¥¯EK KATALOG ONLINE KATALOG ONLINE ZAMÓW DRUKOWANY KATALOG ZAMÓW DRUKOWANY KATALOG TWÓJ KOSZYK TWÓJ KOSZYK DODAJ DO KOSZYKA DODAJ DO KOSZYKA CENNIK I INFORMACJE CENNIK I INFORMACJE ZAMÓW INFORMACJE ZAMÓW INFORMACJE O NOWOĎCIACH O NOWOĎCIACH ZAMÓW CENNIK ZAMÓW CENNIK CZYTELNIA CZYTELNIA FRAGMENTY KSI¥¯EK ONLINE FRAGMENTY KSI¥¯EK ONLINE Wydawnictwo Helion ul. Chopina 6 44-100 Gliwice tel. (32)230-98-63 e-mail: helion@helion.pl Transact-SQL. Czarna ksiêga Autor: Marcin Szeliga ISBN: 83-7361-125-8 Format: B5, stron: 492 Przyk³ady na ftp: 65 kB Transact-SQL to podstawowy jêzyk programowania baz danych w ġrodowisku MS SQL Server. Transact-SQL jest zmodyfikowan¹ i uzupe³nion¹ o elementy typowe dla proceduralnych jêzyków programowania (jak zmienne i instrukcje steruj¹ce wykonaniem programu) wersj¹ standardu SQL-92. Dziêki temu rozszerzeniu, jego mo¿liwoġci s¹ znacznie wiêksze ni¿ mo¿liwoġci standardowego SQL-a. SQL Server zosta³ wyposa¿ony w intuicyjne, a zarazem potê¿ne narzêdzie administracyjne — konsolê SQL Server Enterprise Menager. W rezultacie czêġæ administratorów SQL Servera nie zna albo nie korzysta z mo¿liwoġci jêzyka Transact- -SQL. A okazuje siê, ¿e ta sama operacja mo¿e byæ przeprowadzona kilkukrotnie szybciej, je¿eli zamiast konsoli u¿yjemy Transact-SQLa. Ksi¹¿ka „Transact-SQL. Czarna Ksiêga” to wyczerpuj¹ce i dog³êbne kompendium omawiaj¹ce nie tylko sam jêzyk Transact-SQL, ale równie¿ zasady projektowania baz danych, a tak¿e zarz¹dzanie SQL Serverem za pomoc¹ jêzyka Transact-SQL. Przedstawiono: • Standardowe interfejsy jêzyka SQL instalowane wraz z SQL Serverem • Elementy jêzyka Transact-SQL i sk³adniê poszczególnych instrukcji jêzyka wraz z praktycznymi przyk³adami ich wykorzystania • Metody pobierania i modyfikowania danych • Optymalizacjê zapytañ • Wyszukiwanie pe³notekstowe i us³uga MS Search. • Zasady projektowania relacyjnych baz danych • Algorytmy przekszta³cania relacji poprzez kolejne postacie normalne • Tworzenie, modyfikowanie i usuwanie wszystkich typów obiektów bazodanowych • Bezpieczeñstwo i kontrolowanie dostêpu do danych • Integracjê SQL Servera z innymi serwerami firmy Microsoft • Tworzenie i przywracanie kopii zapasowych • Automatyzacjê czynnoġci administracyjnych • Monitorowanie pracy SQL Servera i optymalizacjê jego wydajnoġci • £¹czenie serwerów bazodanowych • Replikacjê danych pomiêdzy wieloma SZBD Spis treści Wstęp ...................................................z...................................................z........... 11 Część I Język Transact-SQL ...................................................k...................15 Rozdział 1. Interfejsy języka Transact-SQL ...................................................z......... 17 SQL Query Analyzer ...................................................n...................................................n................ 18 Uruchamianie programu...................................................n...................................................n...... 19 Praca z programem...................................................n...................................................n............. 19 OSQL ...................................................n...................................................n..................................... 24 Uruchamianie programu...................................................n...................................................n...... 25 Praca z programem...................................................n...................................................n............. 25 BCP................................................n...................................................n........................................... 26 Uruchomienie programu...................................................n...................................................n...... 26 Praca z programem...................................................n...................................................n............. 26 TEXTCOPY...................................................n...................................................n............................ 27 Uruchomienie programu...................................................n...................................................n...... 27 Praca z programem...................................................n...................................................n............. 27 Oprogramowanie dodatkowe...................................................n...................................................n..... 28 T-SQLEditPro ...................................................n...................................................n................... 28 SQL Editor ...................................................n...................................................n........................ 28 Rozdział 2. Leksykon języka Transact-SQL ...................................................z......... 29 Standardy SQL i historia ich powstania ...................................................n......................................... 29 Typy instrukcji języka Transact-SQL...................................................n............................................ 31 Instrukcje DDL ...................................................n...................................................n.................. 32 Instrukcje DML ...................................................n...................................................n................. 32 Instrukcje DCL ...................................................n...................................................n.................. 33 Znaczniki języka ...................................................n...................................................n...................... 33 Dyrektywy wsadowe ...................................................n...................................................n.......... 33 Komentarze ...................................................n...................................................n....................... 36 Identyfikatory ...................................................n...................................................n.................... 36 Typy danych...................................................n...................................................n...................... 37 Zmienne ...................................................n...................................................n............................ 41 Funkcje ...................................................n...................................................n............................. 44 Operatory ...................................................n...................................................n.......................... 92 Wyrażenia ...................................................n...................................................n......................... 94 Znaczniki sterujące wykonaniem programu...................................................n.............................. 94 Słowa kluczowe ...................................................n...................................................n................. 96 Rozdział 3. Pobieranie danych ...................................................z........................... 97 Przetwarzanie zapytań przez SQL Server ...................................................n......................................97 Instrukcja SELECT ...................................................n...................................................n.................. 98 Zwracanie określonej liczby wierszy ...................................................n..................................... 100 Klauzula FROM......................................................n...................................................n............ 101 Łączenie wielu obiektów...................................................n...................................................n... 103 4 Transact-SQL. Czarna księga Klauzula WHERE ...................................................n...................................................n............ 107 Klauzula ORDER BY...................................................n...................................................n....... 111 Klauzula GROUP BY...................................................n...................................................n....... 113 Klauzula HAVING ...................................................n...................................................n........... 116 Klauzule COMPUTE i COMPUTE BY...................................................n................................. 116 Klauzula OPTION...................................................n...................................................n............ 118 Klauzula FOR ...................................................n...................................................n.................. 119 Klauzula INTO ...................................................n...................................................n................ 120 Podzapytania ...................................................n...................................................n......................... 121 Podzapytania jako źródła danych ...................................................n.......................................... 121 Podzapytania jako wyrażenia...................................................n................................................ 121 Podzapytania powiązane ...................................................n...................................................n...122 Podzapytanie jako złączenie ...................................................n.................................................123 Podzapytania z operatorem EXISTS ...................................................n..................................... 124 Kursory...................................................n...................................................n................................. 125 Deklarowanie kursora ...................................................n...................................................n....... 125 Typy kursorów...................................................n...................................................n................. 126 Blokady...................................................n...................................................n........................... 127 Pobieranie danych ...................................................n...................................................n............ 127 Opcje kursora ...................................................n...................................................n.................. 128 Usuwanie kursora...................................................n...................................................n............. 130 Rozdział 4. Optymalizacja zapytań ...................................................z................... 133 Query Optimizer ...................................................n...................................................n.................... 133 Plan wykonania zapytania ...................................................n...................................................n....... 135 Indeksy zawierające zapytania...................................................n.................................................... 138 Analiza zapytań ...................................................n...................................................n..................... 139 SARG ...................................................n...................................................n............................. 139 Zapytania z operatorem AND ...................................................n............................................... 142 Zapytania z operatorem OR...................................................n.................................................. 143 Zapytania pobierające dane z kilku źródeł danych ...................................................n.................. 144 Zapytania grupujące dane...................................................n...................................................n.. 152 Rozdział 5. Wyszukiwanie pełnotekstowe..................................................z.......... 155 Usługa Microsoft Search...................................................n...................................................n......... 155 Indeksy wyszukiwania pełnotekstowego...................................................n...................................... 156 Procedury systemowe związane z usługą Microsoft Serach ...................................................n..... 156 Funkcje języka Transact-SQL związane z usługą Microsoft Search............................................. 157 Zapytania pełnotekstowe...................................................n...................................................n......... 159 Predykat CONTAINS ...................................................n...................................................n....... 159 Predykat FREETEXT ...................................................n...................................................n....... 161 Funkcja CONTAINSTABLE...................................................n................................................ 161 Funkcja FREETEXTTABLE...................................................n................................................ 162 Rozdział 6. Modyfikowanie danych ...................................................z.................. 165 Przetwarzanie transakcyjne ...................................................n........................................................ 165 Wstawianie danych ...................................................n...................................................n................ 167 Instrukcja INSERT...................................................n...................................................n........... 167 Usuwanie danych ...................................................n...................................................n................... 171 Instrukcja DELETE...................................................n...................................................n.......... 171 Instrukcja TRUNCATE ...................................................n...................................................n.... 173 Aktualizowane danych...................................................n...................................................n............ 174 Instrukcja UPDATE ...................................................n...................................................n......... 174 Spis treści 5 Część II Projektowanie i programowanie baz danych.................................. 177 Rozdział 7. Projektowanie relacyjnych baz danych..........................................z..... 179 Model relacyjnych baz danych ...................................................n................................................... 179 Zasady dotyczące struktury danych...................................................n....................................... 182 Zasady dotyczące przetwarzania danych...................................................n................................ 182 Zasady dotyczące integralności danych ...................................................n................................. 188 Diagram związków E/R ...................................................n...................................................n.......... 193 Określanie typów obiektów ...................................................n.................................................. 193 Określanie atrybutów obiektów poszczególnych typów...................................................n........... 194 Wyodrębnianie danych elementarnych ...................................................n.................................. 194 Określanie zależności funkcyjnych zachodzących pomiędzy atrybutami...................................... 195 Grupowanie danych w tabelach ...................................................n............................................ 196 Określanie związków (relacji) zachodzących między encjami...................................................n.. 198 Normalizacja ...................................................n...................................................n......................... 200 Pierwsza postać normalna 1PN...................................................n............................................. 200 Pierwsza postać anormalna.............................n...................................................n......................200 Druga postać normalna 2PN...................................................n................................................. 206 Trzecia postać normalna 3PN ...................................................n............................................... 207 Algorytmy sprowadzania relacji do wyższej postaci normalnej ...................................................n 208 Postać normalna Boyce’a-Codda ...................................................n.......................................... 213 Rozdział 8. Bazy danych ...................................................z.................................. 215 Konwencja nazewnicza..................................n...................................................n............................ 215 Fizyczna struktura bazy danych...................................................n.................................................. 215 Dziennik transakcyjny ...................................................n...................................................n...... 216 Struktura plików bazodanowych ...................................................n........................................... 217 Struktura wiersza danych ...................................................n...................................................n.. 218 Tworzenie baz danych...................................................n...................................................n............ 219 Instrukcja CREATE DATABASE...................................................n......................................... 220 Opcje baz danych...................................................n...................................................n............. 222 Modyfikacja baz danych ...................................................n...................................................n......... 228 Instrukcja ALTER DATABASE ...................................................n........................................... 228 Zwiększanie rozmiaru plików bazy danych...................................................n............................ 229 Zmniejszanie rozmiaru plików bazy danych...................................................n........................... 230 Usuwanie baz danych ...................................................n...................................................n............. 233 Instrukcja DROP DATABASE ...................................................n............................................. 233 Grupy plików...................................................n...................................................n......................... 233 Rozdział 9. Tabele...................................................z........................................... 237 Typy danych użytkownika ...................................................n...................................................n...... 237 Procedura systemowa sp_addtype...................................................n......................................... 237 Procedura systemowa sp_droptype ...................................................n....................................... 238 Instrukcja CREATE TABLE...................................................n...................................................n... 238 Przechowywanie wartości obliczonych na podstawie innych wartości ......................................... 240 Opcje tabel ...................................................n...................................................n............................ 242 Opcje zawężeń...................................................n...................................................n................. 244 Atrybuty rozszerzone.................................n...................................................n................................ 245 Procedura sp_addextendedproperty ...................................................n....................................... 245 Odczytywanie wartości atrybutów rozszerzonych...................................................n................... 246 Procedura sp_updateextendedproperty...................................................n................................... 247 Procedura sp_dropextendedproperty...................................................n...................................... 247 Instrukcja ALTER TABLE ...................................................n...................................................n..... 248 Zmiana nazwy tabeli...................................................n...................................................n......... 249 Instrukcja DROP TABLE ...................................................n...................................................n....... 250 6 Transact-SQL. Czarna księga Rozdział 10. Indeksy ...................................................z....................................... 251 Po co tworzyć indeksy? ...................................................n...................................................n.......... 251 Typy indeksów ...................................................n...................................................n...................... 252 Indeksy grupujące ...................................................n...................................................n............ 252 Indeksy niegrupujące ...................................................n...................................................n........ 253 Indeksy kompozytowe ...................................................n...................................................n......253 Dla których kolumn tworzyć indeksy?........................n...................................................n.................254 Wpływ indeksów grupujących na sortowanie danych...................................................n.............. 255 Wpływ indeksów niegrupujących na sortowanie danych ...................................................n......... 257 Wpływ indeksów na modyfikowanie danych...................................................n.......................... 258 Wpływ indeksów na dodawanie danych...................................................n................................. 261 Indeksy zawierające zapytanie ...................................................n.............................................. 262 Instrukcja CREATE INDEX ...................................................n...................................................n... 264 Współczynnik wypełnienia...................................................n...................................................n 266 Opcje indeksów ...................................................n...................................................n..................... 268 Instrukcja DROP INDEX ...................................................n...................................................n....... 269 Statystyki ...................................................n...................................................n.............................. 270 Instrukcja CREATE STATISTICS ...................................................n....................................... 271 Instrukcja UPDATE STATISTICS ...................................................n....................................... 272 Instrukcja DROP STATISTICS...................................................n............................................ 272 Rozdział 11. Widoki............................................z................................................ 273 Instrukcja CREATE VIEW ...................................................n...................................................n..... 273 Złączenie zewnętrzne w definicji widoków ...................................................n............................ 276 Uporządkowywanie danych poprzez widoki...................................................n........................... 276 Opcje widoków...................................................n...................................................n...................... 277 Modyfikowanie danych poprzez widoki ...................................................n...................................... 279 Instrukcja ALTER VIEW ...................................................n...................................................n....... 280 Instrukcja DROP VIEW ...................................................n...................................................n......... 280 Rozdział 12. Procedury składowane ...................................................z................. 281 Przetwarzanie procedur przez SQL Server ...................................................n.................................. 282 Tworzenie ...................................................n...................................................n....................... 282 Wykonanie ...................................................n...................................................n...................... 282 Wywołanie ...................................................n...................................................n...................... 283 Konwencja nazewnicza procedur składowanych ...................................................n.......................... 283 Instrukcja CREATE PROCEDURE...................................................n............................................ 284 Automatyczne uruchamianie procedur...................................................n................................... 286 Zagnieżdżanie procedur ...................................................n...................................................n.... 287 Opcje procedur składowanych...................................................n.................................................... 287 Wykonywanie procedur składowanych...................................................n........................................ 288 Instrukcja ALTER PROCEDURE ...................................................n.............................................. 289 Instrukcja DROP PROCEDURE ...................................................n................................................ 289 Rozdział 13. Wyzwalacze...................................................z................................. 291 Wyzwalacze a zawężenia ...................................................n...................................................n........ 292 Typy wyzwalaczy ...................................................n...................................................n.................. 292 Wyzwalacze wywoływane wykonaniem instrukcji INSERT...................................................n.... 292 Wyzwalacze wywoływane wykonaniem instrukcji DELETE...................................................n... 292 Wyzwalacze wywoływane wykonaniem instrukcji UPDATE ...................................................n.. 293 Instrukcja CREATE TRIGGER...................................................n.................................................. 293 Wyzwalacze a monitorowanie aktywności użytkowników...................................................n....... 295 Opcje wyzwalaczy ...................................................n...................................................n................. 297 Instrukcja ALTER TRIGGER ...................................................n...................................................n.298 Instrukcja DROP TRIGGER ...................................................n...................................................n... 299 Spis treści 7 Rozdział 14. Funkcje użytkownika ...................................................z................... 301 Typy funkcji użytkownika...................................................n...................................................n....... 301 Instrukcja CREATE FUNCTION ...................................................n............................................... 301 Tworzenie funkcji powiązanych ze schematem bazy danych ...................................................n... 305 Opcje funkcji ...................................................n...................................................n......................... 305 Instrukcja ALTER FUNCTION ...................................................n................................................. 306 Instrukcja DROP FUNCTION ...................................................n...................................................n 307 Część III Zarządzanie SQL Serverem za pomocą języka Transact-SQL........... 309 Rozdział 15. Microsoft SQL Server 2000...................................................z.......... 311 Usługi SQL Servera ...................................................n...................................................n............... 311 Wymiana danych pomiędzy usługami MSSQLServer i SQLServerAgent .................................... 312 Instancje SQL Servera...................................................n...................................................n............ 315 Bazy danych SQL Servera ...................................................n...................................................n...... 315 Rozdział 16. Bezpieczeństwo...................................................z........................... 319 Model bezpieczeństwa SQL Servera ...................................................n........................................... 319 Tryb Windows NT/2000 ...................................................n...................................................n... 320 Tryb mieszany ...................................................n...................................................n................. 321 Delegacja uprawnień...................................................n...................................................n......... 322 Przedstawienie uprawnień ...................................................n...................................................n.323 Dostęp do baz danych...................................................n...................................................n....... 323 Zarządzanie dostępem do SQL Servera ...................................................n....................................... 323 Tworzenie loginów ...................................................n...................................................n........... 323 Opcje loginów...................................................n...................................................n.................. 326 Usuwanie loginów ...................................................n...................................................n............ 328 Zarządzanie uprawnieniami użytkowników ...................................................n................................. 329 Zarządzanie dostępem do baz danych...................................................n.................................... 329 Role standardowe ...................................................n...................................................n............. 332 Role aplikacyjne...................................................n...................................................n............... 338 Przypisywanie uprawnień użytkownikom i rolom...................................................n................... 340 Właściciel obiektu ...................................................n...................................................n............ 346 Ograniczanie uprawnień za pomocą widoków i procedur składowanych............................................ 347 Rozdział 17. Automatyzacja typowych zadań administracyjnych........................... 349 Integracja SQL Servera z serwerem poczty elektronicznej..................................................n.............. 349 Konfiguracja usług SQLAgentMail oraz SQL Mail...................................................n................. 350 Procedury rozszerzone usługi SQL Mail ...................................................n................................ 350 Integracja SQL Servera z serwerem WWW ...................................................n.................................. 353 Konfiguracja katalogu wirtualnego...................................................n........................................ 353 Umieszczanie instrukcji języka Transact-SQL bezpośrednio w adresie URL................................ 354 Wykonywanie instrukcji zapisanych w szablonach XML ...................................................n........ 355 Wykonywanie zapytań typu XPATCH ...................................................n.................................. 356 Łączenie serwerów bazodanowych ...................................................n............................................. 356 Tworzenie powiązań między serwerami...................................................n................................. 356 Zdalne wykonywanie instrukcji języka Transact-SQL ...................................................n............ 358 Usuwanie powiązań pomiędzy serwerami ...................................................n.............................. 361 Operatorzy ...................................................n...................................................n............................ 362 Zadania ...................................................n...................................................n................................. 365 Tworzenie zadań ...................................................n...................................................n.............. 365 Tworzenie kroków zadania...................................................n...................................................n 368 Tworzenie harmonogramów wykonania zadań ...................................................n....................... 372 Wykonywanie zadań.....................................n...................................................n....................... 375 8 Transact-SQL. Czarna księga Historia wykonania zadań ...................................................n...................................................n.376 Zadania wykonywane na wielu serwerach...................................................n.............................. 378 Usuwanie zadań ...................................................n...................................................n............... 381 Alarmy...................................................n...................................................n.................................. 382 Komunikaty błędów........................................n...................................................n..................... 383 Tworzenie alarmów ...................................................n...................................................n.......... 385 Wywoływanie błędów użytkownika ...................................................n...................................... 389 Alarmy wywoływane bieżącą wydajnością ...................................................n............................ 391 Usuwanie alarmów ...................................................n...................................................n........... 392 Rozdział 18. Tworzenie kopii zapasowych ...................................................z........ 393 Kopie zapasowe ...................................................n...................................................n..................... 395 Kiedy tworzyć kopie zapasowe? ...................................................n........................................... 396 Urządzenia kopii zapasowych...................................................n..................................................... 398 Trwałe urządzenia kopii zapasowych ...................................................n.................................... 398 Tymczasowe urządzenia kopii zapasowych...................................................n............................ 399 Wykonywanie kopii zapasowych ...................................................n................................................400 Instrukcja BACKUP DATABASE ...................................................n........................................ 400 Instrukcja BACKUP LOG ...................................................n...................................................n 401 Pełna kopia bazy danych...................................................n...................................................n... 402 Przyrostowa kopia bazy danych...................................................n............................................ 403 Kopia dziennika transakcyjnego...................................................n............................................ 404 Kopia plików lub grup plików ...................................................n.............................................. 406 Strategie wykonywania kopii zapasowych ...................................................n................................... 407 Strategia pełnych kopii bazy danych ...................................................n..................................... 407 Strategia pełnych kopii bazy danych i kopii dziennika transakcyjnego......................................... 407 Strategia przyrostowych kopii bazy danych ...................................................n........................... 408 Strategia kopii plików bazy danych...................................................n....................................... 409 Rozdział 19. Odtwarzanie kopii zapasowych ...................................................z..... 411 Proces odtwarzania spójności bazy danych ...................................................n.................................. 411 Przygotowanie do odtworzenia kopii zapasowej...................................................n........................... 412 Weryfikacja kopii zapasowej ...................................................n................................................ 412 Ograniczenie dostępu do bazy danych...................................................n................................... 414 Odtwarzanie kopii zapasowych...................................................n................................................... 414 Instrukcja RESTORE DATABASE...................................................n....................................... 415 Instrukcja RESTORE LOG ...................................................n.................................................. 416 Inicjowanie procesu odtwarzania spójność bazy danych...................................................n.......... 417 Odtwarzanie pełnych kopii baz danych...................................................n.................................. 417 Odtwarzanie przyrostowych kopii baz danych...................................................n........................ 418 Odtwarzanie kopii dziennika transakcyjnego...................................................n.......................... 418 Odtwarzanie systemowych baz danych ...................................................n....................................... 420 Co zrobić w przypadku braku kopii baz systemowych?...................................................n........... 421 Co zrobić w przypadku posiadania aktualnych kopii baz systemowych?...................................... 422 Konfigurowanie serwera zapasowego...................................................n.......................................... 423 Rozdział 20. Monitorowanie i optymalizacja pracy SQL Servera............................ 425 Optymalizacja wydajności systemu bazodanowego ...................................................n...................... 425 Zasoby komputera...................................................n...................................................n............ 426 System operacyjny ...................................................n...................................................n........... 433 SQL Server.........................................n...................................................n................................ 435 Baza danych ...................................................n...................................................n.................... 437 Program kliencki ...................................................n...................................................n.............. 442 Monitorowanie bieżącej aktywności użytkowników ...................................................n..................... 443 Spis treści 9 Rozdział 21. Replikacja baz danych ...................................................z................. 447 Wstęp do replikacji...................................................n...................................................n................. 447 Model wydawca — dystrybutor — subskrybent ...................................................n..................... 447 Publikacje...................................................n...................................................n........................ 448 Synchronizowanie danych...................................................n...................................................n. 448 Typy replikacji ...................................................n...................................................n................. 449 Fizyczne modele replikacji ...................................................n................................................... 450 Konfiguracja replikacji pomiędzy serwerami bazodanowymi ...................................................n........ 451 Wybór dystrybutora......................................n...................................................n....................... 452 Wybór wydawcy i subskrybenta ...................................................n........................................... 452 Agenci replikacji ...................................................n...................................................n.................... 452 Agent migawki...................................................n...................................................n................. 452 Agent transakcji ...................................................n...................................................n............... 453 Agent scalania...................................................n...................................................n.................. 454 Agent dystrybucji ...................................................n...................................................n............. 454 Agent kolejkowania ...................................................n...................................................n.......... 454 Replikacja migawkowa ...................................................n...................................................n........... 455 Replikacja transakcyjna ...................................................n...................................................n.......... 457 Replikacja scalana ...................................................n...................................................n.................. 460 Rozwiązywanie konfliktów ...................................................n.................................................. 461 Zarządzanie replikacjami ...................................................n...................................................n........ 462 Replikowanie definicji tabel ...................................................n................................................. 462 Replikowanie definicji widoków, funkcji i procedur ...................................................n............... 463 Sprawdzanie replikacji danych...................................................n.............................................. 463 Dodatki ...................................................k............................................ 467 Skorowidz ...................................................z...................................................z... 469 Rozdział 4. Optymalizacja zapytań Zapytania, tak jak pozostałe instrukcje języka Transact-SQL, przed skompilowaniem i wy- konaniem są optymalizowane przez wewnętrzny proces SQL Servera o nazwie Query Optimizer. Jego zdaniem jest znalezienie najtańszego sposobu wykonania instrukcji. Query Optimizer bazuje na liczbie operacji wejścia-wyjścia oraz na liczbie obliczeń doko- nanych przez procesor niezbędnych do wykonania instrukcji. Query Optimizer szacuje koszt operacji wejścia-wyjścia na podstawie: 1. Struktury tabel przechowujących dane, do których odwołuje się instrukcja i istniejących, związanych z nimi indeksów. 2. Kosztu operacji złączenia różnego typu danych zapisanych w kilku tabelach źródłowych. 3. Istniejących lub tworzonych dynamicznie przez Query Optimizera statystyk opisujących dane źródłowe. 4. Metainformacji opisujących fizyczną strukturę plików, w których zapisane są żądane dane. Ponieważ Query Optimizer oblicza koszt wykonania instrukcji na podstawie statystyk, nieaktualne lub nieadekwatne statystki spowodują utworzenie nieefektywnego planu jej wykonania. Mechanizm tworzenia i aktualizacji statystyk został opisany w rozdziale 10. Wynikiem optymalizacji jest znalezienie takiego sposobu wykonania instrukcji, który zwraca najmniejszą z możliwych liczbę wierszy i w związku z tym wymaga odczytania najmniejszej liczby stron (8 KB spójnych bloków danych). Celem optymalizacji jest także znalezienie takiego wykonywania, którego całkowity czas przeprowadzania jest najkrótszy. Fizyczna struktura baz danych została opisana w znajdującym się w. części poświęconej projektowaniu i tworzeniu baz danych rozdziale 8. W rozdziale 10. znajdują się szczegółowe wskazówki dotyczące tworzenia i wykorzystywania indeksów w celu poprawy wydajności zapytań. Query Optimizer Optymalizacja instrukcji języka Transact-SQL przebiega według następującego schematu: 134 Część I  Język Transact-SQL 1. Po sprawdzeniu poprawności syntaktycznej sprawdzana jest poprawność semantyczna (ang. Parse). Na tym etapie instrukcja zostanie „podzielona” na znaczniki interpretowane przez SQL Server. 2. Następuje standaryzacja — zapisanie znaczników instrukcji Transact-SQL w jednoznacznej postaci (np. ujednolicenie definiowania aliasów raz definiowanych za pomocą słowa kluczowego #5, raz bez niego). Na tym etapie wszystkie redundantne znaczniki instrukcji zostają usunięte. 3. Kolejnym etapem jest optymalizacja — wybór jednego z przygotowanych planów wykonania. Na tym etapie następuje analiza indeksów i statystyk oraz metod złączania danych. Etap można podzielić na trzy fazy: a. Analiza zapytania — wybór metod wyszukiwania i łączenia danych źródłowych mający na celu zminimalizowanie liczby danych, które muszą zostać odczytane do wykonania instrukcji. b. Wybór indeksów — na podstawie statystyk tabel i indeksów następuje wybór tych indeksów, do których odwołanie spowoduje zwrócenie najmniejszej liczby danych (minimalizacja liczby logicznych i fizycznych odczytów bloków danych). c. Wybór metody złączania tabel — na podstawie struktury i liczby danych oraz liczby pamięci operacyjnej niezbędnej do wykonania złączenia wybierany jest sposób złączania tabel. W tym momencie określone zostaje również, która tabela będzie traktowana jako wewnętrzna, a która jako zewnętrzna, w przypadku złączenia poprzez pętlę wyszukiwania. 4. Następnie instrukcja wykonana według opracowanego optymalnego planu zostaje skompilowana. 5. Query Optimizer wybiera optymalny sposób pobrania wybranych (wynikowych) danych. Na przykład: czy odwołać się do indeksu, czy odczytać tabelę — wbrew pozorom dla małych tabel odczytanie całej ich zawartości okazuje się tańszym sposobem na odczytanie danych niż odwoływanie się do nich poprzez indeksy. Skompilowana według optymalnego planu instrukcja języka Transact-SQL zostaje zapisana w buforze procedury — przydzielonej przez SQL Server części pamięci operacyjnej wykorzystywanej wyłącznie do przechowywania skompilowanych procedur. W buforze procedury mogą być przechowywane maksymalnie dwie wersje skompilowanej instrukcji — jedna wykonywana równolegle, druga szeregowo. Ponieważ w pamięci procedury zapisana zostaje wyłącznie instrukcja języka Transact-SQL, bez informacji o tym, który użytkownik ją wykonał, odwołanie się do niej powoduje, że SQL Server musi wyznaczyć bieżący kontekst jej wykonania. Dlatego, jeżeli instrukcja zawiera wyłącznie identyfikatory obiektów, bez identyfikatora ich właściciela, SQL Server przyjmie, że właścicielem obiektów jest użytkownik wykonujący instrukcję, a dopiero potem sprawdzi, czy obiekty nie należą do użytkownika FDQ. Wynika z tego, że jawne odwoływanie się do obiektów za pomocą nazwy użytkownika i nazwy obiektu przy- spiesza wykonanie skompilowanych instrukcji. Rozdział 4.  Optymalizacja zapytań 135 Zoptymalizowana na podstawie nieaktualnych statystyk instrukcja nie będzie dłużej wy- konywana według najlepszego planu. Również, jeżeli zmieniła się struktura obiektów, do których odwoływała się instrukcja, próba wykonania tej instrukcji zakończy się błę- dem. Z tych powodów skompilowana instrukcja jest w zależności od potrzeb rekom- pilowana. Ponowna kompilacja instrukcji jest przeprowadzana, jeżeli: 1. Zmieniona została definicja obiektu, do którego instrukcja się odwołuje (wykonano polecenie #.6 4). 2. Wymuszono aktualizacje statystyk, na podstawie których przygotowany został plan wykonania instrukcji (wykonano instrukcję 72 #6 56#6+56+ ). 3. Usunięto indeks wykorzystywany przez instrukcję (wykonano instrukcję 412 +0 :). 4. Z tabeli źródłowej usunięto lub dodano do niej dużą (względem stanu w momencie optymalizacji instrukcji) liczbę wierszy. 5. Wymuszono rekompilację instrukcji (wywołano procedurę systemową URATGEQORKNG). Plan wykonania zapytania Plan wykonania instrukcji można poznać odczytując zawartość tabeli systemowej U[UKPFGZGU. Jednak bezpośrednie odwoływanie się do tabel systemowych nie jest zalecane, a w tym przypadku istnieją inne sposoby uzyskania żądanych danych. Query Analyzer pozwala na wyświetlenie statystyk związanych z czasem (włączenie opcji 5 656#6+56+ 6+/ ) i liczbą operacji wejścia-wyjścia (opcja 5 656#6+56+ +1). Ponadto możemy wyświetlić opracowany przez Query Optimizera plan wykonania instrukcji (opcja 5 65*192.#06 :6 lub 5 65*192.#0A#..). Na przykład po włączeniu dwóch pierwszych opcji wykonanie poniższego zapytania spowoduje wyświetlenie poniższych informacji: 75 RWDU 5 . 6 +56+0 6VV[RG (41/VKVNGU#5V +00 4,1+0VKVNGU#5V 10VV[RGVV[RG 9* 4 VRWDAKF VRWDAKF )1 53.5GTXGT ZGEWVKQP6KOGU  27VKOGOUGNCRUGFVKOGOU 53.5GTXGTRCTUGCPFEQORKNGVKOG  27VKOGOUGNCRUGFVKOGOU 53.5GTXGT ZGEWVKQP6KOGU  27VKOGOUGNCRUGFVKOGOU V[RG  DWUKPGUU RU[EJQNQI[ 136 Część I  Język Transact-SQL TQY U CHHGEVGF 6CDNG VKVNGU 5ECPEQWPVNQIKECNTGCFURJ[UKECNTGCFUTGCFCJGCF TGCFU 53.5GTXGT ZGEWVKQP6KOGU  27VKOGOUGNCRUGFVKOGOU 53.5GTXGTRCTUGCPFEQORKNGVKOG  27VKOGOUGNCRUGFVKOGOU Natomiast włączenie opcji 5 65*192.#06 :6 spowoduje wyświetlenie pokazanego w roz- dziale 3. planu wykonania instrukcji. Informacje na temat czasu wykonania poszczególnych opisanych wcześniej faz przetwa- rzania przez SQL Server instrukcji języka Transact-SQL nie wymagają komentarza. Przyj- rzyjmy się informacjom związanymi z liczbą operacji wejścia-wyjścia: 1. UECPEQWPV — określa liczbę odwołań do tabeli źródłowej. 2. NQIKECNTGCFU — określa liczbę stron danych odczytanych z pamięci podręcznej. 3. RJ[UKECNTGCFU — określa liczbę stron danych odczytanych z dysku. Ta liczba nigdy nie jest większa od liczby stron odczytanych z pamięci podręcznej. Na podstawie tych dwóch wartości można obliczyć współczynnik trafień: współczynnik trafień = (logical reads – physical reads) / logical reads 4. TGCFCJGCFTGCFU — określa liczbę stron umieszczoną w pamięci podręcznej. Sam plan wykonania instrukcji może również zostać przedstawiony w postaci graficznej (rysunek 4.1). Zawarte są w nim informacje dotyczące następujących zagadnień: 1. Kroki wykonywania instrukcji i ich kolejność. 2. Logiczne operatory algebry zbiorów użyte podczas wykonywania instrukcji. 3. Fizyczna implementacja tych operatorów wykorzystana do wykonania zapytania (tabela 4.1). Rysunek 4.1. Plan wykonania zapytania Rozdział 4.  Optymalizacja zapytań 137 Tabela 4.1. Lista operatorów fizycznych Symbol Znaczenie Opis Odczytanie indeksu Wyszukanie (na podstawie podanych warunków) danych z indeksu niegrupującego Pętla wyszukiwania Wyszukanie (z reguły w oparciu o indeks) w wewnętrznej tabeli złączenia wszystkich wierszy zgodnych z kolejnym wierszem zewnętrznej tabeli złączenia Porządkowanie danych Uporządkowanie wszystkich danych źródłowych Przeszukanie indeksu Wyszukanie w indeksie niegrupującym wierszy danych Przeszukanie tabeli Wyszukanie w tabeli danych spełniających podane kryteria Przeszukanie wskaźników Wyszukanie danych na podstawie identyfikatora wiersza lub klucza indeksu grupującego rekordy w tabeli lub w indeksie grupującym Wybieranie danych Wyszukanie w zbiorze danych źródłowych rekordów spełniających podane kryteria Zgodność funkcji skrótu Złączenie tabel na podstawie wyliczonej dla każdego wiersza wartości funkcji skrótu Złączenie Dowolnego typu (z wyjątkiem złączenia tabeli z nią samą oraz złączenia krzyżowego) złączenie tabel lub widoków Poszczególne kroki związane z wykonaniem instrukcji zapisywane są od strony prawej do lewej. W ramach każdego kroku możliwe jest wykonanie dowolnej liczby operacji — w tym przypadku odczytanie indeksów obu kolum n zostało wykonane jako jeden krok składający się z dwóch operacji. Związek pomiędzy poszczególnymi operacjami reprezentują strzałki. Ustawienie kursora na symbolu operatora fizycznego spowoduje wyświetlenie dodatkowych informacji o danej operacji (rysunek 4.2). Rysunek 4.2. Informacje o złączeniu tabel t1 i t2 138 Część I  Język Transact-SQL Wyświetlając dodatkowe informacje o każdej operacji poznamy:  argumenty wywołania operacji (ang. Argument),  koszt wykonania operacji i jego szacunkowy udział w koszcie wykonania instrukcji (ang. Cost),  koszt wykonania operacji i operacji przez nią wywołanych (ang. Subtree cost),  liczbę wykonania operacji w ramach instrukcji (ang. Number of executes),  liczbę zwróconych przez operację wierszy (ang. Row count),  szacunkową wielkość zwróconych przez operację wierszy (ang. Estimated row size),  szacunkowy koszt operacji wejścia-wyjścia przeprowadzonych przez operację (ang. I/O cost),  szacunkowy koszt wykorzystania zasobów procesora przez operację (ang. CPU cost). Indeksy zawierające zapytania Indeksy różnego typu i sposoby ich tworzenia zostały opisane w rozdziale 10. Optymalną pod względem szybkości odczytu danych jest sytuacja, w której wszystkie żądane dane (wyrażenia wymienione w instrukcji 5 . 6) mogą zostać odczytane z in- deksu. Mówimy wtedy, że indeks zawiera zapytanie. Aby indeks zawierał zapytanie, wszystkie dane źródłowe muszą być poindeksowane. Dotyczy to kolumn wymienionych w poleceniu 5 . 6, w klauzuli 9* 4 , )4172$; i 14 4$;. W takim przypadku pobranie danych sprowadza się do znalezienia i odczytania odpowiednich liści indeksu, bez koniecz- ności odczytywania stron zawierających dane. SQL Server pozwala na tworzenie indeksów dla danych będących wynikiem funkcji grupującej. Jeżeli zapytania dotyczą wyliczanych wartości utworzenie indeksów tego typu spowoduje wielokrotny wzrost wydajności zapytania, w ramach którego obliczane są te wartości. Sprawdzić, czy istnieje indeks zawierający zapytanie, możemy wyświetlając graficzny plan jego wykonania, a następnie wyświetlając szczegóły operacji odczytania indeksu. Jeżeli znajduje się tam informacja: 5ECPPKPICPQPENWUVGTGFKPFGZGPVKTGN[QTQPN[ CTCPIG oznacza to, że do wykonania zapytania wykorzystano wyłącznie dane przechowywane w niegrupującym indeksie (rysunek 4.3). Nie oznacza to, że najlepszym rozwiązaniem jest stworzenie indeksu zawierającego wszystkie kolumny wybranej tabeli — w takim przypadku utworzymy po prostu kopię tabeli i zamiast spodziewanego zysku wydajności uzyskamy jej spadek. Tworząc indeksy zawierające zapytania należy wybierać wyłącznie kolumny często występujące w zapyta- niach i o podobnej wielkości (dodanie do indeksu zawierającego dane z trzech kolumn typu UOCNNKPV danych z kolumny typu XCTEJCT  jest ekstremalnym przykładem źle zaprojektowanego indeksu). Rozdział 4.  Optymalizacja zapytań 139 Rysunek 4.3. Odczytanie nazw poszczególnych kategorii z tabeli categories Niegrupujący indeks zawierający zapytanie jest funkcjonalnym odpowiednikiem indeksu grupującego i korzystanie z niego wiąże się z tymi samymi korzyściami — po znalezieniu pierwszej spełniającej podany warunek wartości nie potrzebne jest tworzenie wskaźników do zewnętrznych danych (tabeli), a ponieważ dane indeksu zapisane są w określonym porządku, wystarczy odczytać określoną liczbę stron przechowujących żądane dane. Analiza zapytań SARG W zależności od typu zapytania, wykorzystanych operatorów logicznych czy metod łączenia tabel, Query Optimizer posłuży się odmiennymi szablonami umożliwiającymi znalezienie optymalnego planu wykonania instrukcji. Znajomość tych szablonów jest niezbędna do tworzenia wydajnych zapytań. Aby wyniki uzyskane przez Czytelników nie różniły się od przedstawianych w książce przed wykonaniem opisywanych programów należy przywrócić oryginalną postać baz 0QTVJYKPF i RWDU. Można to osiągnąć zatrzymując SQL Server i nadpisując pliki .mdf i .ldf tych baz plikami znajdującymi się na płycie instalacyjnej serwera. Akronim 5#4) (ang. Search ARGuments) określa pewien specjalny podzbiór argumentów wyszukiwania, czyli wyrażeń wymienionych w klauzuli 9* 4 instrukcji 5 . 6. Argu- menty 5#4) charakteryzuje:  Obecność stałej, której wartość jest porównywana z polami wybranej kolumny tabeli źródłowej.  Wyszukiwanie wartości równych wzorcowi, należących do zakresu wyznaczonego przez wzorzec lub przez połączenie kilku argumentów 5#4) za pomocą operatora koniunkcji. Wynika z tego, że dla argumentów 5#4) dopuszczalnymi operatorami są: , , , , , $ 69 0 oraz, po spełnieniu dodatkowych warunków, .+- . To, czy argument zawiera- jący operator .+- może zostać uznany za argument 5#4), zależy od pozycji symbolu wieloznacznego (). Jeżeli występuje on jako ostatni znak wzorca, czyli możliwe będzie 140 Część I  Język Transact-SQL ograniczenie liczby stron, które SQL Server będzie musiał odczytać, aby znaleźć żądane wartości, to taki argument będzie argumentem 5#4). W przypadku użycia operatora  , 016, ,  , , 016 :+565, 016+0 czy 016.+- ko- nieczne okazuje się sprawdzenie wartości wszystkich wierszy tabeli źródłowej. Chociaż nie oznacza to, że SQL Server nie potrafi skorzystać z indeksów przy tworzeniu planu zapytania zawierającego wyżej wymienione operatory, to należy dążyć do zastąpienia takich operatorów argumentami 5#4). Wykonanie zapytania zawierającego argument 5#4) przebiega według następującego schematu: 1. Optymalizator sprawdza, czy istnieją przydatne do wykonania zapytania indeksy. 2. Jeżeli taki indeks zostanie znaleziony, rozpoczyna się wyszukiwanie (za pomocą operatora ) stron indeksu przechowujących dane zgodne z żądanym wzorcem. Domyślnie dane indeksów zapisane są na dysku w porządku rosnącym. 3. Wszystkie wartości spełniające zadane kryteria są odczytywane, a jeśli jest to konieczne, odczytywane są z tabeli wartości przechowywane w pozostałych polach danego wiersza. Porównajmy koszt i plan wykonania zapytania wykorzystującego argument 5#4) z zapy- taniem zwracającym ten sam wynik, ale niezawierającym argumentu 5#4): 75 0QTVJYKPF 5 . 6 (41/FDQ1TFGTU 9* 4  WUVQOGT+  )4154 )1 5VOV6GZV  ^$QQMOCTM.QQMWR $11-/#4- =$OM? 1$, 6 =0QTVJYKPF?=FDQ?=1TFGTU? ^+PFGZ5GGM 1$, 6 =0QTVJYKPF?=FDQ?=1TFGTU?= WUVQOGT+ ?  5 - =1TFGTU?= WUVQOGT+ ? QPXGTV = ? 14 4 (149#4 TQY U CHHGEVGF 6CDNG 1TFGTU 5ECPEQWPVNQIKECNTGCFURJ[UKECNTGCFUTGCFCJGCFTGCFU Ponieważ pobieramy wszystkie dane z wszystkich kolumn tabeli, optymalizator musi użyć wskaźnika do tabeli — utworzenie indeksu kompozytowego dla wszystkich kolumn tabeli spowodowałby jedynie pogorszenie wydajności. Jednak wybór wierszy spełniających zadane kryteria odbywa się poprzez odczytanie kolejnych liści indeksu, dzięki czemu SQL Server musi odczytać jedynie wybrane strony tabeli. Gdyby liczba odczytanych stron była stosunkowo duża, tak jak na przykład w przypadku klienta o identyfikatorze (4#0-, optymalizator zdecydowałby się na wykorzystanie indeksu grupującego i sprawdzanie warunku bezpośrednio na danych tabeli, bez wykorzystywania indeksu powiązanego z kolumną WUVQOGT+ : 75 0QTVJYKPF 5 . 6 (41/FDQ1TFGTU Rozdział 4.  Optymalizacja zapytań 141 9* 4  WUVQOGT+  (4#0- )1 5VOV6GZV  ^ NWUVGTGF+PFGZ5ECP 1$, 6 =0QTVJYKPF?=FDQ?=1TFGTU?=2-A1TFGTU?  9* 4  =1TFGTU?= WUVQOGT+ ? QPXGTV = ? TQY U CHHGEVGF 6CDNG 1TFGTU 5ECPEQWPVNQIKECNTGCFURJ[UKECNTGCFUTGCFCJGCFTGCFU Wykonanie zapytania według wybranego przez optymalizator planu wymagało odczy- tania 21 stron danych. Przekonajmy się, czy próba wymuszenia użycia indeksu powią- zanego z kolumną WUVQOGT+ poprawi wydajność: 75 0QTVJYKPF 5 . 6 (41/FDQ1TFGTU +0 : WUVQOGT+ 9* 4  WUVQOGT+  (4#0- )1 5VOV6GZV  ^$QQMOCTM.QQMWR $11-/#4- =$OM? 1$, 6 =0QTVJYKPF?=FDQ?=1TFGTU? ^+PFGZ5GGM 1$, 6 =0QTVJYKPF?=FDQ?=1TFGTU?= WUVQOGT+ ?  5 - =1TFGTU?= WUVQOGT+ ? (4#0- 14 4 (149#4 TQY U CHHGEVGF 6CDNG 1TFGTU 5ECPEQWPVNQIKECNTGCFURJ[UKECNTGCFUTGCFCJGCFTGCFU Jak widać, optymalizator właściwie oszacował liczbę operacji wejścia-wyjścia i wybrał optymalny plan wykonania zapytania. Jeżeli któryś z Czytelników udokumentuje i prześle do firmy Microsoft informację o tym, że wystąpił przypadek, w którym optymalizator podjął błędną decyzję, będzie to podstawą do poprawienia kodu programu i (czasami) do nagrodzenia użytkownika. Wróćmy do porównania planu wykonania zapytania z operatorem 5#4) z planem wyko- nania zapytania zwracającego te same dane, ale niewykorzystującego operatora 5#4): 75 0QTVJYKPF 5 . 6 (41/FDQ1TFGTU 9* 4  WUVQOGT+ .+-  4154 )1 5VOV6GZV  ^ NWUVGTGF+PFGZ5ECP 1$, 6 =0Q
Pobierz darmowy fragment (pdf)

Gdzie kupić całą publikację:

Transact-SQL. Czarna księga
Autor:

Opinie na temat publikacji:


Inne popularne pozycje z tej kategorii:


Czytaj również:


Prowadzisz stronę lub blog? Wstaw link do fragmentu tej książki i współpracuj z Cyfroteką: