Cyfroteka.pl

klikaj i czytaj online

Cyfro
Czytomierz
00192 016704 17206888 na godz. na dobę w sumie
T-SQL dla zaawansowanych. Przewodnik programisty. Wydanie IV - książka
T-SQL dla zaawansowanych. Przewodnik programisty. Wydanie IV - książka
Autor: , , Liczba stron: 632
Wydawca: Helion Język publikacji: polski
ISBN: 978-83-283-2247-9 Data wydania:
Lektor:
Kategoria: ebooki >> komputery i informatyka >> bazy danych >> inne
Porównaj ceny (książka, ebook, audiobook).
W wielu środowiskach IT serwer bazodanowy jest szczególnie ważnym elementem infrastruktury. Microsoft SQL Server jest jednym z częściej spotykanych rozwiązań. Aby w pełni wykorzystać jego możliwości, trzeba dogłębnie poznać język Transact-SQL — potężne narzędzie, które wyewoluowało z języka zapytań SQL i wciąż jest rozwijane przez firmę Microsoft.

Niniejsza książka, przeznaczona dla programistów T-SQL, jest uaktualnionym wydaniem świetnego przewodnika po SQL Server. Omówiono w niej zaawansowane mechanizmy dostępne w T-SQL, włączając w to również nowości, takie jak mechanizmy pamięciowe będące częścią SQL Server 2014. Istotne kwestie są przystępnie wyjaśnione za pomocą praktycznych przykładów i obszernych fragmentów kodu źródłowego. Autorzy położyli nacisk przede wszystkim na demonstrację opcji T-SQL, ich możliwe zastosowania, a sam podręcznik zorganizowali w sposób umożliwiający szybkie wyszukanie potrzebnych informacji.

Najważniejsze zagadnienia omówione w książce:

Sprawdź nowe możliwości T-SQL i SQL Server!


Miguel Cebollero — od ponad 16 lat zajmuje się SQL Server i innymi systemami bazodanowymi, włączając w to projektowanie, programowanie i administrację. Często wypowiada się na różnego rodzaju konferencjach związanych z bazami danych.
Jay Natarajan — od ponad 15 lat projektuje i implementuje rozwiązania oparte na SQL Server. Ma na koncie wiele skomplikowanych rozwiązań dla dużych klientów. Dołączyła do Microsoft Consulting Services w 2008 roku.
Michael Coles — ma za sobą ponad dekadę doświadczeń przy projektowaniu i administrowaniu bazami danych SQL Server. Jest autorem licznych artykułów na temat SQL Server, szczególnie o specjalnych zastosowaniach T-SQL.
Znajdź podobne książki Ostatnio czytane w tej kategorii

Darmowy fragment publikacji:

Tytuł oryginału: Pro T-SQL Programmer s Guide, 4th Edition Tłumaczenie: Andrzej Stefański ISBN: 978-83-283-2247-9 Original edition copyright © 2015 by Miguel Cebollero, Jay Natarajan, and Michael Coles All rights reserved. Polish edition copyright © 2016 by HELION SA. All rights reserved. All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording or by any information storage retrieval system, without permission from the Publisher. Wszelkie prawa zastrzeżone. Nieautoryzowane rozpowszechnianie całości lub fragmentu niniejszej publikacji w jakiejkolwiek postaci jest zabronione. Wykonywanie kopii metodą kserograficzną, fotograficzną, a także kopiowanie książki na nośniku filmowym, magnetycznym lub innym powoduje naruszenie praw autorskich niniejszej publikacji. Wszystkie znaki występujące w tekście są zastrzeżonymi znakami firmowymi bądź towarowymi ich właścicieli. Autor oraz Wydawnictwo HELION dołożyli wszelkich starań, by zawarte w tej książce informacje były kompletne i rzetelne. Nie biorą jednak żadnej odpowiedzialności ani za ich wykorzystanie, ani za związane z tym ewentualne naruszenie praw patentowych lub autorskich. Autor oraz Wydawnictwo HELION nie ponoszą również żadnej odpowiedzialności za ewentualne szkody wynikłe z wykorzystania informacji zawartych w książce. Wydawnictwo HELION ul. Kościuszki 1c, 44-100 GLIWICE tel. 32 231 22 19, 32 230 98 63 e-mail: helion@helion.pl WWW: http://helion.pl (księgarnia internetowa, katalog książek) Drogi Czytelniku! Jeżeli chcesz ocenić tę książkę, zajrzyj pod adres http://helion.pl/user/opinie/tsqlz4 Możesz tam wpisać swoje uwagi, spostrzeżenia, recenzję. Printed in Poland. • Kup książkę • Poleć książkę • Oceń książkę • Księgarnia internetowa • Lubię to! » Nasza społeczność Spis tre(cid:258)ci O autorach ..................................................................................................11 O korektorze merytorycznym ......................................................................12 Podzi(cid:218)kowania ............................................................................................13 Wprowadzenie ............................................................................................15 Rozdzia(cid:239) 1. Podstawy T-SQL ..........................................................................................21 Krótka historia T-SQL ..........................................................................................................21 Języki imperatywne i deklaratywne ....................................................................................22 Podstawy SQL ........................................................................................................................23 Wyrażenia .................................................................................................................................23 Bazy danych ..............................................................................................................................25 Logi transakcyjne .....................................................................................................................26 Schematy ...................................................................................................................................26 Tabele ........................................................................................................................................27 Widoki .......................................................................................................................................28 Indeksy ......................................................................................................................................29 Procedury składowane ............................................................................................................30 Funkcje użytkownika ..............................................................................................................30 Moduły SQL CLR ....................................................................................................................30 Podstawy stylu ........................................................................................................................31 Białe znaki .................................................................................................................................31 Konwencje nazewnictwa .........................................................................................................32 Jedno wejście, jedno wyjście ...................................................................................................33 Programowanie defensywne ..................................................................................................35 Wyrażenie SELECT * ..............................................................................................................36 Inicjalizacja zmiennych ...........................................................................................................37 Podsumowanie .......................................................................................................................37 Poleć książkęKup książkę SPIS TRE(cid:165)CI Rozdzia(cid:239) 2. Narz(cid:218)dzia ....................................................................................................39 SQL Server Management Studio .........................................................................................39 IntelliSense ................................................................................................................................40 Fragmenty kodu .......................................................................................................................41 Schematy skrótów klawiszowych ..........................................................................................43 Debugowanie T-SQL ...............................................................................................................43 Opcje edycji w SSMS ...............................................................................................................45 Pomoc kontekstowa ................................................................................................................46 Graficzna reprezentacja planów wykonania zapytań .........................................................48 Opcje do zarządzania projektami ..........................................................................................48 Eksplorator obiektów ..............................................................................................................50 Narzędzie SQLCMD .............................................................................................................52 SQL Server Data Tools ..........................................................................................................54 SQL Profiler ............................................................................................................................54 Extended Events ....................................................................................................................57 SQL Server Integration Services ..........................................................................................58 BCP ..........................................................................................................................................58 SQL Server 2014 Books Online ...........................................................................................60 Przykładowa baza danych AdventureWorks ....................................................................61 Podsumowanie .......................................................................................................................61 Rozdzia(cid:239) 3. Kod proceduralny ........................................................................................63 Logika trójwartościowa .........................................................................................................63 Przepływ sterowania .............................................................................................................65 Słowa kluczowe BEGIN i END ..............................................................................................65 Wyrażenie IF ... ELSE ..............................................................................................................66 Wyrażenia WHILE, BREAK i CONTINUE ........................................................................68 Wyrażenie GOTO ....................................................................................................................69 Wyrażenie WAITFOR ............................................................................................................70 Wyrażenie RETURN ...............................................................................................................71 Wyrażenie CASE ...................................................................................................................72 Proste wyrażenie CASE ...........................................................................................................72 Przeszukiwane wyrażenie CASE ............................................................................................73 CASE i tabele przestawne .......................................................................................................75 Wyrażenie IIF ...........................................................................................................................79 CHOOSE ...................................................................................................................................80 COALESCE i NULLIF ............................................................................................................81 Kursory ...................................................................................................................................82 Podsumowanie .......................................................................................................................89 Rozdzia(cid:239) 4. Funkcje u(cid:285)ytkownika ...................................................................................91 Funkcje skalarne ....................................................................................................................91 Rekurencja w skalarnych UDF ..............................................................................................93 Kod proceduralny w funkcjach użytkownika ......................................................................96 Wielowyrażeniowe funkcje zwracające tabelę .................................................................104 Wbudowane funkcje zwracające tabelę ............................................................................112 Ograniczenia funkcji definiowanych przez użytkownika ..............................................115 Funkcje niedeterministyczne ...............................................................................................115 Stan bazy danych ....................................................................................................................116 Podsumowanie .....................................................................................................................117 4 Poleć książkęKup książkę SPIS TRE(cid:165)CI Rozdzia(cid:239) 5. Procedury sk(cid:239)adowane .............................................................................. 119 Wprowadzenie .....................................................................................................................119 Odkrywanie metadanych ...................................................................................................121 Natywnie kompilowane procedury składowane .............................................................122 Zarządzanie procedurami składowanymi ........................................................................124 Najlepsze praktyki związane z tworzeniem procedur składowanych ..........................125 Przykład procedury składowanej ......................................................................................127 Rekurencja w procedurach składowanych .......................................................................132 Parametry tabelaryczne ......................................................................................................140 Tymczasowe procedury składowane ................................................................................142 Rekompilacja i pamięć podręczna .....................................................................................143 Statystyki procedur składowanych ......................................................................................143 Przechwytywanie parametrów .............................................................................................145 Rekompilacja ..........................................................................................................................148 Podsumowanie .....................................................................................................................151 Rozdzia(cid:239) 6. Obiekty pami(cid:218)ciowe ................................................................................. 153 Czynniki napędzające technologie pamięciowe ..............................................................153 Trendy sprzętowe ................................................................................................................154 Podstawy obiektów pamięciowych ...................................................................................155 Krok 1. Dodanie nowej grupy plików optymalizowanej do operacji pamięciowych ......156 Krok 2. Dodanie nowego kontenera optymalizowanego do operacji pamięciowych .....157 Krok 3. Utwórz nową tabelę optymalizowaną do operacji pamięciowych ......................159 Ograniczenia tabel pamięciowych .......................................................................................163 Indeksy tabel pamięciowych OLTP ..................................................................................164 Indeksy typu hash ..................................................................................................................165 Indeksy zakresu ......................................................................................................................167 Natywnie kompilowane procedury składowane .............................................................169 Rozdzia(cid:239) 7. Wyzwalacze .............................................................................................. 173 Wyzwalacze DML ................................................................................................................173 Wiele wyzwalaczy ..................................................................................................................174 Kiedy używać wyzwalaczy DML ..........................................................................................175 Wirtualne tabele inserted i deleted ......................................................................................177 Przeglądanie za pomocą wyzwalaczy DML .......................................................................178 Wykorzystanie zapisywania modyfikacji danych ...........................................................178 Udostępnianie danych wyzwalaczom .................................................................................183 Wyzwalacze zagnieżdżone i rekurencyjne .........................................................................183 Funkcje UPDATE() i COLUMNS_UPDATED() .............................................................184 Wyzwalacze na widokach ...................................................................................................188 Wyzwalacze DDL ................................................................................................................191 Typy zdarzeń DDL i grupy zdarzeń ....................................................................................191 Wyzwalacze logowania .......................................................................................................195 Podsumowanie .....................................................................................................................197 Rozdzia(cid:239) 8. Szyfrowanie .............................................................................................. 199 Hierarchia szyfrowania .......................................................................................................199 Klucze główne usługi ..........................................................................................................200 Klucze główne bazy danych ...............................................................................................201 Certyfikaty ............................................................................................................................203 5 Poleć książkęKup książkę SPIS TRE(cid:165)CI Ograniczenia szyfrowania asymetrycznego .....................................................................205 Klucze asymetryczne ...........................................................................................................207 Kopie zapasowe klucza asymetrycznego ............................................................................210 Klucze symetryczne .............................................................................................................210 Tymczasowe klucze symetryczne ........................................................................................212 Sól i uwierzytelnianie ..........................................................................................................216 Szyfrowanie bez kluczy .......................................................................................................217 Tworzenie skrótów ................................................................................................................217 Rozszerzone zarządzanie kluczami ...................................................................................218 Przeźroczyste szyfrowanie danych ....................................................................................219 Podsumowanie .....................................................................................................................221 Rozdzia(cid:239) 9. Wyra(cid:285)enia nazwane i funkcje okna ........................................................... 223 Wyrażenia nazwane ............................................................................................................223 Wiele zapytań nazwanych ....................................................................................................225 Zwiększona czytelność CTE .................................................................................................226 Rekurencyjne zapytania nazwane .....................................................................................227 Funkcje okna ........................................................................................................................231 Funkcja ROW_NUMBER ....................................................................................................231 Stronicowanie zapytania za pomocą OFFSET/FETCH ...................................................233 Funkcje RANK i DENSE_RANK ........................................................................................234 Funkcja NTILE .......................................................................................................................239 Funkcje agregacyjne, analityczne i klauzula OVER ........................................................241 Przykłady funkcji analitycznych ........................................................................................245 CUME_DIST i PERCENT_RANK .....................................................................................245 PERCENTILE_CONT i PERCENTILE_DISC ..................................................................247 LAG i LEAD ...........................................................................................................................248 FIRST_VALUE i LAST_VALUE .........................................................................................250 Podsumowanie .....................................................................................................................251 Rozdzia(cid:239) 10. Typy danych i zaawansowane typy danych .............................................. 253 Podstawowe typy danych ...................................................................................................253 Znaki ........................................................................................................................................253 Typy danych max ...................................................................................................................254 Wartości numeryczne ...........................................................................................................256 Typy danych dla daty i czasu ...............................................................................................258 UTC i czas wojskowy ............................................................................................................261 Funkcje daty i czasu ...............................................................................................................262 Przesunięcia a strefy czasowe ...............................................................................................265 Typ danych uniqueidentifier .............................................................................................265 Typ danych hierarchyid ......................................................................................................267 Reprezentacja danych hierarchicznych ..............................................................................267 Przykład hierarchyid .............................................................................................................269 Metody hierarchyid ...............................................................................................................273 Przestrzenne typy danych ...................................................................................................274 (X, Y) czy (szerokość, długość) ............................................................................................277 Półkula i orientacja ................................................................................................................278 Michigan i wielkie jeziora .....................................................................................................279 6 Poleć książkęKup książkę SPIS TRE(cid:165)CI Obsługa FILESTREAM .......................................................................................................281 Włączanie obsługi FILESTREAM .......................................................................................282 Tworzenie grup plików FILESTREAM ..............................................................................283 Tabele korzystające z FILESTREAM ..................................................................................284 Korzystanie z danych FILESTREAM ..................................................................................285 Obsługa FileTable ..................................................................................................................286 Funkcje filetable .....................................................................................................................290 Wyzwalacze na filetable ........................................................................................................294 Podsumowanie .....................................................................................................................295 Rozdzia(cid:239) 11. Wyszukiwanie pe(cid:239)notekstowe ................................................................... 297 Architektura FTS .................................................................................................................297 Tworzenie katalogów pełnotekstowych i indeksów .........................................................298 Tworzenie katalogów pełnotekstowych .............................................................................298 Tworzenie indeksów pełnotekstowych ...............................................................................300 Zapytania pełnotekstowe ......................................................................................................305 Predykat FREETEXT .............................................................................................................306 Optymalizacja wydajności FTS ............................................................................................307 Predykat CONTAINS ...........................................................................................................308 Funkcje FREETEXTTABLE i CONTAINSTABLE ..........................................................311 Tezaurusy i stoplisty ..............................................................................................................313 Procedury składowane, dynamiczne widoki zarządcze i funkcje ...................................317 Semantyka statystyczna .........................................................................................................318 Podsumowanie .....................................................................................................................320 Rozdzia(cid:239) 12. XML ........................................................................................................... 321 XML dawniej ........................................................................................................................321 OPENXML ...........................................................................................................................321 Formaty generowane przez OPENXML ..........................................................................325 Klauzula FOR XML .............................................................................................................327 FOR XML RAW ..................................................................................................................327 FOR XML AUTO ................................................................................................................329 FOR XML EXPLICIT ..........................................................................................................331 FOR XML PATH .................................................................................................................332 Typ danych xml ...................................................................................................................334 Niestrukturyzowany xml ....................................................................................................334 Ustrukturyzowany xml .......................................................................................................335 Metody typu danych xml ....................................................................................................337 Metoda query .........................................................................................................................338 Metoda value ..........................................................................................................................339 Metoda exist ...........................................................................................................................339 Metoda nodes .........................................................................................................................340 Metoda modify .......................................................................................................................341 Indeksy XML ........................................................................................................................343 Przekształcenia XSL ............................................................................................................347 Ustawienia związane z bezpieczeństwem SQL CLR .......................................................350 Podsumowanie .....................................................................................................................352 7 Poleć książkęKup książkę SPIS TRE(cid:165)CI Rozdzia(cid:239) 13. XQuery i XPath .......................................................................................... 353 XPath i FOR XML PATH ...................................................................................................353 Atrybuty XPath ......................................................................................................................355 Kolumny bez nazw i wieloznaczniki ...................................................................................356 Grupowanie elementów ........................................................................................................356 Funkcja data ............................................................................................................................357 Testowanie węzłów i funkcje ................................................................................................358 XPath i NULL .........................................................................................................................359 Klauzula WITH XMLNAMESPACES ................................................................................360 Testowanie węzłów ................................................................................................................360 XQuery i typ danych xml ...................................................................................................361 Wyrażenia i sekwencje ..........................................................................................................361 Metoda query .........................................................................................................................363 Ścieżki określające lokalizację ..............................................................................................364 Testowanie węzłów ................................................................................................................366 Przestrzenie nazw ..................................................................................................................367 Oznaczenia osi ........................................................................................................................369 Dynamiczne generowanie XML ..........................................................................................371 Komentarze XQuery .............................................................................................................373 Typy danych ...........................................................................................................................373 Predykaty ................................................................................................................................374 Operatory porównujące wartości ........................................................................................374 Operatory porównań ogólnych ...........................................................................................376 Format daty XQuery .............................................................................................................377 Porównania węzłów ...............................................................................................................378 Wyrażenia warunkowe (if…then…else) ............................................................................379 Wyrażenia arytmetyczne ......................................................................................................379 Dzielenie liczb całkowitych w XQuery ...............................................................................380 Funkcje w XQuery .................................................................................................................380 Konstruktory i rzutowanie ...................................................................................................383 Wyrażenia FLWOR ...............................................................................................................384 Słowa kluczowe for i return ..................................................................................................384 Słowo kluczowe where ..........................................................................................................387 Słowa kluczowe order by ......................................................................................................387 Słowo kluczowe let .................................................................................................................388 Obsługa UTF-16 ....................................................................................................................389 Podsumowanie .....................................................................................................................391 Rozdzia(cid:239) 14. Widoki katalogowe i dynamiczne widoki zarz(cid:200)dcze ................................. 393 Widoki katalogowe ..............................................................................................................393 Metadane tabel i kolumn ......................................................................................................394 Odpytywanie o pozwolenia ..................................................................................................395 Dynamiczne widoki zarządcze i funkcje ..........................................................................397 Metadane indeksowe .............................................................................................................398 Informacje o sesji ...................................................................................................................402 Informacje o połączeniu .......................................................................................................403 Aktualnie wykonywany SQL ................................................................................................403 Pamięciowe widoki systemowe ............................................................................................405 Najdroższe zapytania .............................................................................................................406 8 Poleć książkęKup książkę SPIS TRE(cid:165)CI Przestrzeń tempdb .................................................................................................................407 Zasoby serwera .......................................................................................................................409 Niewykorzystywane indeksy ................................................................................................411 Statystyki oczekiwania ..........................................................................................................413 Widoki INFORMATION_SCHEMA ...............................................................................413 Podsumowanie .....................................................................................................................416 Rozdzia(cid:239) 15. Programowanie klienta .NET ..................................................................... 417 ADO.NET .............................................................................................................................417 Klient SQL .NET ..................................................................................................................419 Połączenie ze źródłem danych ...........................................................................................419 Odłączone zbiory danych ...................................................................................................423 Zapytania parametryzowane ..............................................................................................424 Zapytania niezwracające wyników, skalarne i XML .......................................................428 SqlBulkCopy .........................................................................................................................431 Wiele aktywnych zbiorów wyników .................................................................................436 LINQ to SQL ........................................................................................................................439 Designer ................................................................................................................................440 Zapytania z LINQ to SQL ...................................................................................................442 Podstawy zapytań LINQ to SQL .......................................................................................442 Klauzula where .......................................................................................................................444 Klauzula orderby ....................................................................................................................445 Klauzula join ...........................................................................................................................446 Odroczone wykonanie zapytania ......................................................................................447 Od LINQ do Entity Framework ........................................................................................448 Odpytywanie elementów ....................................................................................................452 Podsumowanie .....................................................................................................................456 Rozdzia(cid:239) 16. Programowanie z CLR ............................................................................... 459 Stary sposób ..........................................................................................................................459 Rozwiązania ze zintegrowanym CLR ...............................................................................460 Pakiety CLR ..........................................................................................................................461 Funkcje użytkownika ..........................................................................................................465 Procedury składowane ........................................................................................................472 Funkcje agregujące użytkownika ......................................................................................476 Tworzenie prostej funkcji agregującej ................................................................................476 Tworzenie zaawansowanych UDA .....................................................................................479 Typy użytkownika w zintegrowanych CLR .....................................................................484 Wyzwalacze ..........................................................................................................................491 Podsumowanie .....................................................................................................................495 Rozdzia(cid:239) 17. Us(cid:239)ugi danych ........................................................................................... 497 LocalDB z SQL Server 2014 Express .................................................................................498 Programowanie asynchroniczne z ADO.NET 4.5 ..........................................................502 ODBC dla Linuksa ...............................................................................................................503 JDBC ......................................................................................................................................508 Architektura SOA i usługi danych WCF .........................................................................510 Tworzenie WCF Data Service ............................................................................................512 Definiowanie źródła danych ..............................................................................................512 9 Poleć książkęKup książkę SPIS TRE(cid:165)CI Tworzenie usługi danych ....................................................................................................514 Tworzenie odbiornika WCF Data Service .......................................................................518 Podsumowanie .....................................................................................................................522 Rozdzia(cid:239) 18. Obs(cid:239)uga b(cid:239)(cid:218)dów i dynamiczny SQL ........................................................... 523 Obsługa błędów ...................................................................................................................523 Stare metody obsługi błędów .............................................................................................523 Wyrażenie RAISERROR .....................................................................................................525 Obsługa wyjątków TRY...CATCH ....................................................................................526 TRY_PARSE, TRY_CONVERT i TRY_CAST .................................................................528 Wyrażenie THROW ............................................................................................................529 Narzędzia do debugowania ................................................................................................530 Debugowanie za pomocą wyrażenia PRINT .....................................................................530 Flagi śledzenia ........................................................................................................................531 Debugger zintegrowany z SSMS ..........................................................................................532 Debugger T-SQL w Visual Studio .......................................................................................533 Dynamiczny SQL .................................................................................................................536 Wyrażenie EXECUTE ...........................................................................................................536 Wstrzykiwanie SQL i dynamiczny SQL .............................................................................537 Usuwanie problemów z dynamicznym SQL .....................................................................539 Procedura składowana sp_executesql ...............................................................................540 Dynamiczny SQL i zasięg .....................................................................................................540 Parametryzacja po stronie klienta .......................................................................................541 Podsumowanie .....................................................................................................................542 Rozdzia(cid:239) 19. Poprawianie wydajno(cid:258)ci ........................................................................... 543 Pamięć masowa w SQL Server ...........................................................................................543 Pliki i grupy plików ...............................................................................................................543 Alokacja przestrzeni ..............................................................................................................544 Partycje ....................................................................................................................................549 Kompresja danych .................................................................................................................550 Kolumny rzadkie ....................................................................................................................555 Indeksy ..................................................................................................................................559 Sterty ........................................................................................................................................559 Indeksy klastrowe ..................................................................................................................560 Indeksy nieklastrowe .............................................................................................................562 Indeksy filtrowane .................................................................................................................565 Optymalizacja zapytań ..........................................................................................................566 Czytanie planów zapytań ......................................................................................................566 Metodologia ............................................................................................................................570 Oczekiwanie ............................................................................................................................571 Extended Events .....................................................................................................................573 Podsumowanie .....................................................................................................................577 Dodatek A Odpowiedzi do (cid:202)wicze(cid:241) ............................................................................ 579 Dodatek B Typy danych XQuery ................................................................................. 589 Dodatek C S(cid:239)owniczek ................................................................................................ 595 Dodatek D Krótki przewodnik po SQLCMD ................................................................. 609 Skorowidz ................................................................................................. 617 10 Poleć książkęKup książkę R O Z D Z I A (cid:146) 9 (cid:132) (cid:132) (cid:132) Wyra(cid:285)enia nazwane i funkcje okna SQL Server 2014 kontynuuje obsługę wyjątkowo użytecznych wyrażeń nazwanych, które po raz pierwszy pojawiły się w SQL Server 2005. CTE mogą uprościć Twoje zapytania, co sprawi, że staną się łatwiejsze do analizy i utrzymania. SQL Server obsługuje też odwołania do tego samego CTE, co pozwala na tworzenie bardzo potężnych zapytań rekurencyjnych. Dodatkowo SQL Server obsługuje funkcje okna, które pozwalają na dzielenie wyników i dodawanie numerowania oraz wartości rankingowych do wierszy w uzyskanych za ich pomocą zbiorach wyników. Rozdział ten zaczyna się od omówienia możliwości i korzyści powiązanych ze stosowania CTE, a kończy opisem dostępnych w SQL Server funkcji okna. Wyra(cid:285)enia nazwane Wyrażenia nazwane (CTE — ang. common table expressions) są użytecznym dodatkiem do SQL Server. CTE to coś w rodzaju tymczasowej tabeli zawierającej nazwany zbiór wyników, dostępny jedynie dla pojedynczego zapytania lub wyrażenia DML albo do jawnego usunięcia. CTE tworzy się w tym samym wierszu kodu, co wyrażenie SELECT lub wyrażenie DML, które z niego korzystają, podczas gdy budowanie i użycie tabeli tymczasowej to zazwyczaj dwuetapowy proces. W porównaniu z trwałymi tabelami i widokami CTE mają kilka zalet. Oto one. (cid:120) CTE są tymczasowe i istnieją jedynie w czasie działania pojedynczego zapytania lub wyrażenia DML. Oznacza to, że nie musisz tworzyć trwałych obiektów bazodanowych, takich jak widoki. (cid:120) Do pojedynczego CTE można odwoływać się wiele razy za pomocą nazwy w pojedynczym zapytaniu lub wyrażeniu DML, co czyni Twój kod łatwiejszym do zarządzania. Tabele pomocnicze muszą być przepisywane w całości przy każdym odwołaniu do nich. (cid:120) Można korzystać z CTE do grupowania za pomocą kolumn tworzonych ze zbioru skalarnego lub za pomocą funkcji niedeterministycznej. (cid:120) CTE mogą odwoływać się do siebie, co jest użytecznym mechanizmem rekurencyjnym. (cid:120) Zapytania odwołujące się do CTE można wykorzystywać do definiowania kursora. CTE mogą być bardzo różne — od prostych do bardzo skomplikowanych konstrukcji. Wszystkie CTE rozpoczynają się od słowa kluczowego WITH, po którym umieszczana jest nazwa CTE i lista zwracanych przez zapytanie kolumn. Następnie umieszczane jest słowo kluczowe AS i treść CTE, którą tworzy zapytanie lub wyrażenie DML zakończone średnikiem w przypadku ciągu złożonego z większej liczby wyrażeń. Na listingu 9.1 pokazany jest bardzo prosty przykład służący do demonstracji podstawowej składni. Poleć książkęKup książkę T-SQL DLA ZAAWANSOWANYCH. PRZEWODNIK PROGRAMISTY Listing 9.1. Proste CTE WITH PobierzNazwiskaCTE ( BusinessEntityID, FirstName, MiddleName, LastName ) AS ( SELECT BusinessEntityID, FirstName, MiddleName, LastName FROM Person.Person ) SELECT BusinessEntityID, FirstName, MiddleName, LastName FROM PobierzNazwiskaCTE; Na listingu 9.1 jest definiowane CTE o nazwie PobierzNazwiskaCTE, które zwraca kolumny BusinessEntityID, FirstName, MiddleName i LastName. Treść CTE zawiera proste zapytanie do tabeli Person.Person bazy danych AdventureWorks2014. Bezpośrednio po CTE znajduje się związane z nim wyrażenie SELECT. Wyrażenie SELECT odwołuje się do CTE w klauzuli FROM. Prze(cid:239)adowane WITH S(cid:239)owo kluczowe WITH jest w SQL Server prze(cid:239)adowane, co oznacza, (cid:285)e wykorzystywane jest na kilka ró(cid:285)nych sposobów w odmiennych zadaniach w T-SQL. Wykorzystywane jest mi(cid:218)dzy innymi przy okre(cid:258)laniu dodatkowych opcji w wyra(cid:285)eniach DDL CREATE, przy dodawaniu podpowiedzi do zapyta(cid:241) i wyra(cid:285)e(cid:241) DML oraz przy deklarowaniu przestrzeni nazw XML w klauzuli WITH XMLNAMESPACES. Obecnie jest te(cid:285) wykorzystywane jako s(cid:239)owo kluczowe rozpoczynaj(cid:200)ce definicj(cid:218) CTE. Je(cid:258)li zatem CTE nie jest pierwszym wyra(cid:285)eniem w ci(cid:200)gu, poprzednie wyra(cid:285)enie musi by(cid:202) zako(cid:241)czone (cid:258)rednikiem. Jest to jeden z powodów tego, (cid:285)e zdecydowanie nale(cid:285)y korzysta(cid:202) ze (cid:258)rednika ko(cid:241)cz(cid:200)cego wyra(cid:285)enie w tworzonym kodzie. Proste CTE mają kilka ograniczeń przy definiowaniu i deklarowaniu. (cid:120) Do CTE muszą być dołączone pojedyncze wyrażenia INSERT, DELETE, UPDATE lub SELECT. (cid:120) Wszystkie kolumny zwracane przez CTE muszą mieć unikalne nazwy. Jeśli wszystkie kolumny zwracane przez zapytanie w treści CTE mają unikalne nazwy, możesz pominąć listę kolumn w deklaracji CTE. (cid:120) CTE może odwoływać się do innego, wcześniej zdefiniowanego CTE w tej samej klauzuli WITH, ale nie może odwoływać się do CTE zdefiniowanego później (nazywa się to odwołaniem do przodu). (cid:120) W CTE nie można korzystać ze słów kluczowych, klauzul i opcji, takich jak COMPUTE, COMPUTE BY, FOR BROWSE, INTO i OPTION (podpowiedź do zapytania). Nie możesz też korzystać z ORDER BY, jeśli nie użyjesz klauzuli TOP. (cid:120) W nierekurencyjnym CTE można zdefiniować wiele CTE. Wszystkie definicje muszą być połączone za pomocą jednego z operatorów działających na zbiorach; są to UNION ALL, UNION, INTERSECT, EXCEPT. (cid:120) Jak wspomniano w ramce „Przeładowane WITH”, jeśli CTE nie jest pierwszym wyrażeniem w ciągu, poprzedzające je wyrażenie musi być zakończone średnikiem. Pamiętaj o tych ograniczeniach podczas tworzenia CTE. 224 Poleć książkęKup książkę ROZDZIA(cid:146) 9. (cid:132) WYRA(cid:191)ENIA NAZWANE I FUNKCJE OKNA Wiele zapytań nazwanych Możesz definiować wiele CTE dla pojedynczego zapytania lub wyrażenia DML, oddzielając definicje CTE za pomocą przecinków. Głównym powodem, by to robić, jest możliwość uproszczenia kodu, co ułatwia jego analizę i zarządzanie. Wyrażenia nazwane oferują sposoby wizualnego rozdzielenia kodów na mniejsze bloki funkcjonalne, co upraszcza jego tworzenie i debugowanie. Zapytanie z listingu 9.2 zawiera wiele CTE, przy czym drugie CTE odwołuje się do pierwszego. Wyniki jego działania zostały pokazane na rysunku 9.1. Listing 9.2. Wiele CTE WITH PobierzNazwiskaCTE ( BusinessEntityID, FirstName, MiddleName,LastName ) AS ( SELECT BusinessEntityID, FirstName, MiddleName, LastName FROM Person.Person ), PobierzKontaktCTE ( BusinessEntityID, FirstName, MiddleName, LastName, Email, HomePhoneNumber ) AS ( SELECT gn.BusinessEntityID, gn.FirstName , gn.MiddleName, gn.LastName , ea.EmailAddress, pp.PhoneNumber FROM PobierzNazwiskaCTE gn LEFT JOIN Person.EmailAddress ea ON gn.BusinessEntityID = ea.BusinessEntityID LEFT JOIN Person.PersonPhone pp ON gn.BusinessEntityID = pp.BusinessEntityID AND pp.PhoneNumberTypeID = 2 ) SELECT BusinessEntityID, FirstName , MiddleName, LastName , Email, HomePhoneNumber FROM PobierzKontaktCTE; Rysunek 9.1. Część wyniku zapytania z wieloma CTE 225 Poleć książkęKup książkę T-SQL DLA ZAAWANSOWANYCH. PRZEWODNIK PROGRAMISTY Zwiększona czytelność CTE Dzięki wykorzystaniu CTE możesz tworzyć bardziej czytelne zapytania niż przy użyciu zagnieżdżonych podzapytań. Dla celów demonstracyjnych poniższe zapytanie służy do uzyskania takich samych wyników jak zapytanie korzystające z CTE pokazane na listingu 9.2, ale za pomocą zagnieżdżonych podzapytań. SELECT gn.BusinessEntityID, gn.FirstName, gn.MiddleName, gn.LastName, gn.EmailAddress, gn.HomePhoneNumber FROM ( SELECT p.BusinessEntityID, p.FirstName, p.MiddleName, p.LastName, ea.EmailAddress, ea.HomePhoneNumber FROM Person.Person p LEFT JOIN ( SELECT ea.BusinessEntityID, ea.EmailAddress, pp.HomePhoneNumber FROM Person.EmailAddress ea LEFT JOIN ( SELECT pp.BusinessEntityID, pp.PhoneNumber AS HomePhoneNumber, pp.PhoneNumberTypeID FROM Person.PersonPhone pp ) pp ON ea.BusinessEntityID = pp.BusinessEntityID AND pp.PhoneNumberTypeID = 2 ) ea ON p.BusinessEntityID = ea.BusinessEntityID ) gn Korzystająca z CTE wersja tego zapytania pokazana na listingu 9.2 upraszcza i hermetyzuje kod zapytania oraz jest dużo prostsza do czytania i zrozumienia niż wersja korzystająca z zagnieżdżonych podzapytań, co sprawia, że jest łatwiejsza do debugowania i utrzymania w długim horyzoncie czasowym. Przykład z listingu 9.2 zawiera dwa CTE o nazwach PobierzNazwiskaCTE i PobierzKontaktCTE. Zapytanie PobierzNazwiskaCTE zapożyczone jest z listingu 9.1; pobiera ono nazwiska z tabeli Person.Person. WITH PobierzNazwiskaCTE ( BusinessEntityID, FirstName, MiddleName,LastName ) AS ( SELECT BusinessEntityID, FirstName, MiddleName, LastName FROM Person.Person ), Drugie CTE, PobierzKontaktCTE, łączy wyniki PobierzNazwiskaCTE z tabelami Person.EmailAddress i Person.PersonPhone. PobierzKontaktCTE ( BusinessEntityID, FirstName, MiddleName, LastName, Email, HomePhoneNumber ) AS ( SELECT gn.BusinessEntityID, gn.FirstName , gn.MiddleName, gn.LastName , ea.EmailAddress, pp.PhoneNumber 226 Poleć książkęKup książkę ROZDZIA(cid:146) 9. (cid:132) WYRA(cid:191)ENIA NAZWANE I FUNKCJE OKNA FROM PobierzNazwiskaCTE gn LEFT JOIN Person.EmailAddress ea ON gn.BusinessEntityID = ea.BusinessEntityID LEFT JOIN Person.PersonPhone pp ON gn.BusinessEntityID = pp.BusinessEntityID AND pp.PhoneNumberTypeID = 2 ) Zauważ, że słowo kluczowe WITH wykorzystywane jest tylko raz na początku całego wyrażenia. Druga deklaracja CTE jest oddzielona od pierwszej za pomocą przecinka i nie wymaga użycia słowa kluczowego WITH. W końcu warto zauważyć jak proste i czytelne jest zapytanie SELECT powiązane z CTE dzięki temu, że złączenia zostały przeniesione do CTE. SELECT BusinessEntityID, FirstName , MiddleName, LastName , Email, HomePhoneNumber FROM PobierzKontaktCTE; (cid:132) Wskazówka Do CTE mo(cid:285)esz odwo(cid:239)a(cid:202) si(cid:218) z tre(cid:258)ci innego CTE, z powi(cid:200)zanego zapytania lub wyra(cid:285)enia DML. Oba typy odwo(cid:239)a(cid:241) CTE pokazane s(cid:200) na listingu 9.2 — PobierzNazwiskaCTE wywo(cid:239)ywane jest przez PobierzKontaktCTE, a PobierzKontaktCTE jest wywo(cid:239)ywane przez zapytanie powi(cid:200)zane z CTE. Rekurencyjne zapytania nazwane Rekurencyjne CTE to takie wyrażenie, które wywołuje samo siebie wielokrotnie, by ustalić podzbiór danych aż do momentu uzyskania pełnego wyniku. CTE może odwołać się do samego siebie, co jest użytecznym mechanizmem do odpytywania o dane hierarchiczne. Rekurencyjne CTE są podobne do nierekurencyjnych CTE, ale w treści CTE znajduje się wiele zbiorów zapytań generujących zbiory wyników, w których wiersze są połączone za pomocą operatora zbiorów UNION ALL. Przynajmniej jedno z zapytań z treści rekurencyjnego CTE nie może odwoływać się do CTE — zapytanie takie nazywane jest zapytaniem kotwiczącym (ang. anchor query). Rekurencyjne CTE zawierają też jedno lub więcej rekurencyjnych zapytań odwołujących się do CTE. Rekurencyjne zapytania są połączone ze sobą za pomocą zapytania kotwiczącego (lub zapytań kotwiczących) w treści CTE. Wymagają operatora UNION ALL najwyższego poziomu łączącego rekurencyjne i nierekurencyjne zapytania. Wiele zapytań kotwiczących można łączyć za pomocą UNION ALL. Rekurencja kończy się, gdy zapytanie nie zwróci żadnych wierszy. Na listingu 9.3 znajduje się proste rekurencyjne CTE pobierające zbiór wyników zawierający liczby od 1 do 10. Listing 9.3. Proste rekurencyjne CTE WITH Liczby (n) AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM Liczby WHERE n 10 ) SELECT n FROM Liczby; CTE z listingu 9.3 zaczyna się od deklaracji definiującej nazwę CTE i zwracane kolumny. WITH Liczby (n) 227 Poleć książkęKup książkę T-SQL DLA ZAAWANSOWANYCH. PRZEWODNIK PROGRAMISTY Treść CTE zawiera pojedyncze zapytanie kotwiczące zwracające pojedynczy wiersz z liczbą 1 w kolumnie n. SELECT 1 AS n Zapytanie kotwiczące jest łączone z zapytaniem rekurencyjnym za pomocą operatora zbiorów UNION ALL. Zapytanie rekurencyjne zawiera odwołanie wewnętrzne do CTE Liczby, które dodaje 1 do kolumny n przy każdym rekurencyjnym wywołaniu. Klauzula WHERE ogranicza zbiór wyników do pierwszych dziesięciu liczb. SELECT n + 1 FROM Liczby WHERE n 10 Rekurencyjne CTE mają domyślnie ustawioną maksymalną głębokość rekurencji 100. Oznacza to, że zapytanie rekurencyjne w treści CTE może wywołać samo siebie maksymalnie 100 razy. Możesz użyć opcji MAXRECURSION, by zwiększyć maksymalną wartość dla każdego CTE osobno. Na listingu 9.4 znajduje się CTE z listingu 9.3 zmodyfikowane w taki sposób, by zwracało liczby od 1 do 1000. Zmodyfikowane zapytanie korzysta z opcji MAXRECURSION zwiększającej maksymalny poziom rekurencji. Bez opcji MAXRECURSION to CTE zwróciłoby błąd po 100 poziomach rekurencji. Listing 9.4. Rekurencyjne CTE z opcją MAXRECURSION WITH Liczby (n) AS ( SELECT 0 AS n UNION ALL SELECT n + 1 FROM Liczby WHERE n 1000 ) SELECT n FROM Liczby OPTION (MAXRECURSION 1000); Wartość MAXRECURSION musi być z zakresu od 0 do 32767. SQL Server wyrzuca wyjątek, jeśli zostanie przekroczony limit MAXRECURSION. Wartość 0 w zmiennej MAXRECURSION oznacza brak ograniczeń poziomów rekurencji dla CTE. Opcji tej należy używać ostrożnie — jeśli nie ograniczysz odpowiednio wyników w klauzuli WHERE zapytania, możesz znaleźć się w nieskończonej pętli. (cid:132) Wskazówka Utworzenie trwa(cid:239)ej tabeli z kolejnymi liczbami mo(cid:285)e by(cid:202) wydajniejsze ni(cid:285) korzystanie z rekurencyjnego CTE do generowania liczb szczególnie wtedy, kiedy cz(cid:218)sto b(cid:218)dziesz generowa(cid:239) liczby w ten sposób. Rekurencyjne CTE są użyteczne przy odpytywaniu o dane zapisane w postaci rozgałęzionego grafu. Tabela Production.BillOfMaterials z bazy danych AdventureWorks zawiera przykład takich danych. W tabeli tej znajdują się dwie ważne kolumny ComponentID i ProductAssemblyID, które odwzorowują strukturę hierarchiczną. ComponentID to unikalna liczba identyfikująca każdy z komponentów wykorzystywanych przez AdventureWorks przy tworzeniu swoich produktów. ProductAssemblyID oznacza bardziej złożony komponent utworzony z jednego lub większej liczby komponentów z listy. Na rysunku 9.2 pokazano relacje pomiędzy komponentami i bardziej złożonymi produktami w bazie danych AdventureWorks. Rekurencyjne CTE pokazane na listingu 9.5 pobiera pełną listę części (BOM, ang. bills of materials) dla wskazanego komponentu. Komponent wykorzystany w przykładzie to srebrny 48-calowy rower Mountain-100 mający w bazie AdventureWorks identyfikator ComponentID równy 774. Część wyników pokazana jest na rysunku 9.3. 228 Poleć książkęKup książkę ROZDZIA(cid:146) 9. (cid:132) WYRA(cid:191)ENIA NAZWANE I FUNKCJE OKNA Rysunek 9.2. Zależności pomiędzy komponentami i produktami Listing 9.5. Rekurencyjne CTE zwracające BOM DECLARE @ComponentID int = 774; WITH BillOfMaterialsCTE ( BillOfMaterialsID, ProductAssemblyID, ComponentID, Quantity, Level ) AS ( SELECT bom.BillOfMaterialsID, bom.ProductAssemblyID, bom.ComponentID, bom.PerAssemblyQty AS Quantity, 0 AS Level FROM Production.BillOfMaterials bom WHERE bom.ComponentID = @ComponentID UNION ALL SELECT bom.BillOfMaterialsID, bom.ProductAssemblyID, bom.ComponentID, bom.PerAssemblyQty, Level + 1 FROM Production.BillOfMaterials bom INNER JOIN BillOfMaterialsCTE bomcte ON bom.ProductAssemblyID = bomcte.ComponentID WHERE bom.EndDate IS NULL ) SELECT bomcte.ProductAssemblyID, p.ProductID, p.ProductNumber, p.Name, p.Color, bomcte.Quantity, bomcte.Level FROM BillOfMaterialsCTE bomcte INNER JOIN Production.Product p ON bomcte.ComponentID = p.ProductID ORDER BY bomcte.Level; 229 Poleć książkęKup książkę T-SQL DLA ZAAWANSOWANYCH. PRZEWODNIK PROGRAMISTY Rysunek 9.3. Część wyników rekurencyjnego CTE generującego BOM Tak jak wcześniejsze przykłady CTE, listing 9.3 rozpoczyna się od nazwy CTE i deklaracji listy kolumn. WITH BillOfMaterialsCTE ( BillOfMaterialsID, ProductAssemblyID, ComponentID, Quantity, Level ) Zapytanie kotwiczące po prostu pobiera z tabeli wiersz, dla którego CoponentID jest równy wskazanemu identyfikatorowi. Jest to komponent najwyższego poziomu z BOM, w tym przypadku 774. Zauważ, że CTE może odwoływać się do zmiennych T-SQL tak, jak @ComponentID w przykładzie. SELECT bom.BillOfMaterialsID, bom.ProductAssemblyID, bom.ComponentID, bom.PerAssemblyQty AS Quantity, 0 AS Level FROM Production.BillOfMaterials bom WHERE bom.ComponentID = @ComponentID Rekurencyjne zapytania pobierają kolejne poziomy BOM z CTE, gdzie ProductAssemblyID każdego wiersza ma taką samą wartość jak ComponentID wierszy wyższego poziomu. Chodzi o to, że rekurencyjne zapytanie CTE pobiera wiersze niższych poziomów z hierarchii, korzystając z zależności hierarchicznych pokazanych wcześniej na rysunku 9.2. SELECT bom.BillOfMaterialsID, bom.ProductAssemblyID, bom.ComponentID, bom.PerAssemblyQty, Level + 1 FROM Production.BillOfMaterials bom INNER JOIN BillOfMaterialsCTE bomcte ON bom.ProductAssemblyID = bomcte.ComponentID WHERE bom.EndDate IS NULL 230 Poleć książkęKup książkę ROZDZIA(cid:146) 9. (cid:132) WYRA(cid:191)ENIA NAZW
Pobierz darmowy fragment (pdf)

Gdzie kupić całą publikację:

T-SQL dla zaawansowanych. Przewodnik programisty. Wydanie IV
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ą: