Darmowy fragment publikacji:
Tytuł oryginału: Beginning SQL Server for Developers, 4th Edition
Tłumaczenie: Andrzej Watrak
ISBN: 978-83-283-1267-8
Original edition copyright © 2015 by Robin Dewson.
All rights reserved.
Polish edition copyright © 2015 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/sqlsws
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
Rozdzia(cid:239) 1.
O autorze ........................................................................................................ 13
O korektorze merytorycznym .......................................................................... 14
Podzi(cid:218)kowania ................................................................................................ 15
Wprowadzenie ................................................................................................ 16
Informacje ogólne i instalacja oprogramowania ............................................. 17
Dlaczego powinienem używać oprogramowania SQL Server? ............................................ 18
Wersje oprogramowania SQL Server ...................................................................................... 19
Przykład ....................................................................................................................................... 20
Instalacja ...................................................................................................................................... 20
Instalacja ........................................................................................................................................ 21
Opcje instalacji .............................................................................................................................. 24
Wybór instalowanych funkcjonalności ..................................................................................... 24
Nadanie nazwy instancji serwera ............................................................................................... 27
Konta usług .................................................................................................................................... 28
Tryb uwierzytelniania .................................................................................................................. 30
Określenie folderów danych ........................................................................................................ 31
Opcje FILESTREAM .................................................................................................................... 32
Tworzenie bazy dla usług Reporting Services .......................................................................... 32
Opcje odtwarzania rozproszonego ............................................................................................. 33
Bezpieczeństwo ........................................................................................................................... 34
Konta usług .................................................................................................................................... 34
Tryby uwierzytelniania ................................................................................................................ 36
Konto sa .......................................................................................................................................... 40
Podsumowanie ............................................................................................................................ 41
Rozdzia(cid:239) 2. SQL Server Management Studio ...................................................................... 43
Krótki przegląd aplikacji SSMS ................................................................................................ 43
Edytor zapytań ............................................................................................................................ 55
Narzędzie sqlcmd ....................................................................................................................... 57
Podsumowanie ............................................................................................................................ 57
Poleć książkęKup książkęSPIS TRE(cid:165)CI
Rozdzia(cid:239) 3. Projektowanie i tworzenie bazy danych ......................................................... 59
Definiowanie bazy danych ........................................................................................................ 60
Wbudowane bazy danych serwera SQL Server ...................................................................... 61
Baza master .................................................................................................................................... 61
Baza tempdb .................................................................................................................................. 62
Baza model ..................................................................................................................................... 63
Baza msdb ...................................................................................................................................... 63
Bazy AdventureWorks/AdventureWorksDW ......................................................................... 64
Wybór systemu bazy .................................................................................................................. 64
System OLTP ................................................................................................................................. 64
Systemy OLAP/BI ......................................................................................................................... 65
Wybór systemu dla przykładowej bazy ..................................................................................... 66
Zbieranie danych ........................................................................................................................ 66
Zdefiniowanie informacji przechowywanych w bazie danych ............................................ 68
Tabela Produkty finansowe ......................................................................................................... 69
Tabela Klienci ................................................................................................................................ 69
Tabela Adresy klientów ................................................................................................................ 69
Tabela Akcje .................................................................................................................................. 70
Tabela Transakcje ......................................................................................................................... 70
Dodatkowe i pominięte informacje ......................................................................................... 70
Tworzenie relacji ........................................................................................................................ 70
Klucze ............................................................................................................................................. 71
Tworzenie relacji ........................................................................................................................... 72
Dodatkowe informacje o kluczach obcych ............................................................................... 75
Normalizacja bazy danych ........................................................................................................ 76
Każdej jednostce należy przypisać unikatowy identyfikator .................................................. 77
Należy zapisywać tylko informacje bezpośrednio związane z daną jednostką .................... 77
Należy unikać powtarzających się wartości i kolumn ............................................................. 78
Postaci normalne bazy danych ................................................................................................... 78
Denormalizacja bazy danych ...................................................................................................... 80
Tworzenie przykładowej bazy danych ..................................................................................... 80
Tworzenie bazy danych w aplikacji SQL Server Management Studio .................................. 81
Usuwanie bazy danych za pomocą aplikacji SQL Server Management Studio ................... 97
Tworzenie bazy danych w edytorze zapytań .......................................................................... 100
Podsumowanie .......................................................................................................................... 102
Rozdzia(cid:239) 4. Bezpiecze(cid:241)stwo i zgodno(cid:258)(cid:202) baz danych ze standardami .............................. 103
Konta użytkowników ............................................................................................................... 104
Konta na serwerze i nazwy użytkowników ........................................................................... 113
Role ............................................................................................................................................. 113
Role serwerowe ........................................................................................................................... 113
Role bazodanowe ........................................................................................................................ 115
Role aplikacyjne .......................................................................................................................... 115
Schematy .................................................................................................................................... 118
Zanim pójdziesz dalej .............................................................................................................. 119
System Declarative Management Framework ...................................................................... 123
Szyfrowanie danych ................................................................................................................. 129
Podsumowanie .......................................................................................................................... 130
6
Poleć książkęKup książkęSPIS TRE(cid:165)CI
Rozdzia(cid:239) 5. Definiowanie tabel ........................................................................................ 131
Czym jest tabela? ...................................................................................................................... 132
Typy danych w serwerze SQL Server ..................................................................................... 133
Typy danych w tabelach ............................................................................................................. 134
Typy danych w programach ...................................................................................................... 138
Kolumny są czymś więcej niż zwykłymi repozytoriami danych ....................................... 139
Wartości domyślne ..................................................................................................................... 139
Generowanie wartości IDENTITY ........................................................................................... 139
Unikatowe identyfikatory danych ............................................................................................ 140
Sekwencje zamiast opcji IDENTITY ..................................................................................... 140
Zastosowanie wartości NULL ................................................................................................... 141
Tworzenie tabel za pomocą aplikacji SQL Server Management Studio ........................... 142
Tworzenie tabeli za pomocą edytora zapytań ...................................................................... 150
Tworzenie tabeli za pomocą narzędzia sqlcmd .................................................................... 152
Instrukcja ALTER TABLE ...................................................................................................... 154
Tworzenie pozostałych tabel ................................................................................................... 155
Definiowanie klucza podstawowego ...................................................................................... 156
Tworzenie relacji ...................................................................................................................... 157
Opcja Check Existing Data on Creation ................................................................................. 161
Opcja Enforce Foreign Key Constraints .................................................................................. 161
Wybór reguł usuwania i zmieniania danych .......................................................................... 162
Tworzenie relacji za pomocą kodu T-SQL ........................................................................... 162
Kontrola spójności danych: baza danych czy aplikacja? ..................................................... 164
Partycjonowanie danych ......................................................................................................... 164
Podsumowanie .......................................................................................................................... 166
Rozdzia(cid:239) 6. Tworzenie indeksów i diagramu bazy danych .............................................. 167
Czym jest indeks? ..................................................................................................................... 167
Rodzaje indeksów ....................................................................................................................... 168
Unikatowość indeksu ................................................................................................................. 170
Cechy dobrego indeksu ........................................................................................................... 170
Kolumny rzadko zmieniane ...................................................................................................... 171
Klucze podstawowe i obce ......................................................................................................... 171
Wyszukiwanie określonych rekordów danych ....................................................................... 172
Indeks pokrywający .................................................................................................................... 172
Przeszukiwanie zakresu danych ............................................................................................... 173
Uporządkowane dane ................................................................................................................ 173
Cechy złego indeksu ................................................................................................................. 173
Wybór nieodpowiednich kolumn ............................................................................................ 174
Wybór nieodpowiednich danych ............................................................................................. 174
Wybór zbyt wielu kolumn ......................................................................................................... 174
Zbyt mało rekordów w tabeli .................................................................................................... 174
Weryfikacja wydajności indeksów ......................................................................................... 175
Tworzenie indeksu ................................................................................................................... 175
Tworzenie indeksu za pomocą narzędzia do projektowania tabel ...................................... 175
Indeksy i statystyki ...................................................................................................................... 180
Składnia polecenia CREATE INDEX ...................................................................................... 180
Tworzenie indeksu w edytorze zapytań .................................................................................. 182
7
Poleć książkęKup książkęSPIS TRE(cid:165)CI
Usuwanie indeksu .................................................................................................................... 185
Modyfikowanie indeksu .......................................................................................................... 185
Kolumny dołączone ................................................................................................................. 187
Brak indeksu .............................................................................................................................. 188
Diagram bazy danych .............................................................................................................. 188
Podstawy tworzenia diagramów ............................................................................................... 189
Narzędzie Database Diagram Designer ................................................................................... 189
Domyślny diagram bazy danych .............................................................................................. 190
Pasek narzędzi do tworzenia diagramu bazy danych ............................................................ 192
Modyfikowanie bazy danych za pomocą narzędzia Database Diagram Designer ............ 194
Podsumowanie .......................................................................................................................... 194
Rozdzia(cid:239) 7. Tabele przechowywane w pami(cid:218)ci ............................................................... 195
Przetwarzanie danych uzależnione od kosztów operacji .................................................... 196
Czym jest tabela przechowywana w pamięci? ...................................................................... 197
Różnice w stosunku do tabeli zapisanej na dysku .................................................................. 198
Indeksy przechowywane w pamięci i indeksy kodowane ................................................... 199
Grupy kodów ............................................................................................................................... 200
Ogólne uwagi do indeksów ....................................................................................................... 201
Wymagania dotyczące systemu operacyjnego i sprzętu ..................................................... 201
Wymagania dla bazy danych przed utworzeniem tabeli .................................................... 201
Tworzenie tabeli przechowywanej w pamięci za pomocą kodu T-SQL ........................... 202
Przenoszenie tabeli do pamięci ............................................................................................... 203
Podsumowanie .......................................................................................................................... 209
Rozdzia(cid:239) 8. Tworzenie i odtwarzanie kopii zapasowych bazy danych ............................. 211
Strategie tworzenia zapasowych kopii bazy danych ............................................................ 212
Modele odtwarzania danych ..................................................................................................... 212
Rodzaje kopii zapasowych ......................................................................................................... 213
Decyzja o wyborze strategii tworzenia kopii zapasowych .................................................... 213
Dziennik transakcji .................................................................................................................. 214
Kiedy mogą pojawić się problemy? ........................................................................................ 216
Tworzenie i odtwarzanie kopii zapasowych tabel przechowywanych w pamięci ........... 216
Tworzenie kopii zapasowych i dezaktywacja bazy danych ................................................ 216
Tworzenie zapasowych kopii danych .................................................................................... 218
Uwagi dotyczące tworzenia kopii zapasowych ....................................................................... 218
Ręczne tworzenie zapasowej kopii bazy danych .................................................................... 220
Tworzenie kopii zapasowej za pomocą kodu T-SQL ............................................................ 225
Tworzenie kopii zapasowej dziennika transakcji za pomocą kodu T-SQL ........................ 231
Odtwarzanie bazy danych ....................................................................................................... 234
Odtwarzanie bazy za pomocą aplikacji SQL Server Management Studio .......................... 234
Odtwarzanie bazy za pomocą kodu T-SQL ............................................................................ 239
Odtwarzanie bazy do stanu z określonej chwili ..................................................................... 242
Odłączanie i podłączanie bazy danych .................................................................................. 246
Odłączanie i podłączanie bazy danych za pomocą aplikacji
SQL Server Management Studio ............................................................................................ 246
Odłączanie i podłączanie bazy za pomocą kodu T-SQL ....................................................... 250
Generowanie skryptu tworzącego bazę danych ................................................................... 253
Podsumowanie .......................................................................................................................... 258
8
Poleć książkęKup książkęSPIS TRE(cid:165)CI
Rozdzia(cid:239) 9. Utrzymanie bazy danych ............................................................................... 259
Tworzenie planu utrzymaniowego bazy danych ................................................................. 259
Wykonanie planu utrzymaniowego ....................................................................................... 273
Konfiguracja usługi Database Mail ........................................................................................ 278
Modyfikowanie planu utrzymaniowego ............................................................................... 286
Podsumowanie .......................................................................................................................... 289
Rozdzia(cid:239) 10. Wpisywanie i usuwanie danych
oraz realizacja transakcji na tabelach dyskowych ......................................... 291
Wpisywanie danych do tabel .................................................................................................. 292
Składnia polecenia INSERT ...................................................................................................... 292
Polecenie INSERT ....................................................................................................................... 293
Polecenie DBCC CHECKIDENT ............................................................................................. 302
Ograniczenia kolumn ................................................................................................................. 303
Wpisywanie wielu rekordów jednocześnie ............................................................................. 309
Transakcje .................................................................................................................................. 310
Podstawy transakcji .................................................................................................................... 310
Polecenia transakcji .................................................................................................................... 311
Blokowanie danych ..................................................................................................................... 312
Usuwanie danych ..................................................................................................................... 312
Składnia polecenia DELETE ..................................................................................................... 313
Przed zademonstrowaniem polecenia DELETE .................................................................... 313
Użycie polecenia DELETE ......................................................................................................... 313
Przycinanie tabeli ........................................................................................................................ 317
Usuwanie tabeli ........................................................................................................................... 318
Podsumowanie .......................................................................................................................... 319
Rozdzia(cid:239) 11. Odczytywanie i zmienianie danych w tabelach dyskowych .......................... 321
Odczytywanie danych .............................................................................................................. 322
Odczytywanie danych za pomocą aplikacji SQL Server Management Studio .................. 322
Odczytywanie danych za pomocą polecenia SELECT .......................................................... 324
Ograniczenie wyników zapytania — klauzula WHERE ..................................................... 330
Klauzula TOP (n) ........................................................................................................................ 333
Klauzula TOP (n) PERCENT .................................................................................................... 334
Opcja SET ROWCOUNT n ...................................................................................................... 334
Funkcje tekstowe ...................................................................................................................... 336
Kolejność! Kolejność! ............................................................................................................... 338
Operator LIKE .......................................................................................................................... 340
Tworzenie danych — polecenie SELECT INTO .................................................................. 343
Zmienianie danych ................................................................................................................... 344
Polecenie UPDATE .................................................................................................................... 345
Zmienianie danych za pomocą edytora zapytań .................................................................... 346
Zmienianie danych — transakcje ............................................................................................. 349
Transakcje zagnieżdżone ........................................................................................................... 351
Przetwarzanie danych z kilku tabel ........................................................................................ 352
Podsumowanie .......................................................................................................................... 358
9
Poleć książkęKup książkęSPIS TRE(cid:165)CI
Rozdzia(cid:239) 12. Tabele przechowywane w pami(cid:218)ci ............................................................... 359
Polecenia INSERT, UPDATE, DELETE i SELECT ............................................................. 359
Poziomy blokowania danych i izolacji transakcji ................................................................ 360
Polecenie MERGE .................................................................................................................... 369
Funkcjonalności niedostępne w przypadku tabel przechowywanych w pamięci ........... 371
Podsumowanie .......................................................................................................................... 371
Rozdzia(cid:239) 13. Tworzenie widoków ...................................................................................... 373
Po co tworzyć widoki? ............................................................................................................. 374
Zastosowanie widoków do ochrony danych ........................................................................... 374
Szyfrowanie definicji widoków ................................................................................................. 375
Widoki i tabele przechowywane w pamięci .......................................................................... 376
Tworzenie widoku za pomocą SQL Server Management Studio ...................................... 376
Tworzenie widoku na podstawie innego widoku ................................................................ 383
Tworzenie widoku za pomocą kodu T-SQL ......................................................................... 386
Składnia polecenia CREATE VIEW ........................................................................................ 386
Tworzenie widoku za pomocą poleceń CREATE VIEW i SELECT ................................... 387
Wiązanie kolumn widoku za pomocą opcji SCHEMABINDING ...................................... 389
Indeksowanie widoku .............................................................................................................. 390
Podsumowanie .......................................................................................................................... 393
Rozdzia(cid:239) 14. Procedury sk(cid:239)adowane, funkcje i bezpiecze(cid:241)stwo danych ............................ 395
Czym jest procedura składowana? ......................................................................................... 396
Tworzenie procedury składowanej ........................................................................................ 397
Polecenie CREATE PROCEDURE .......................................................................................... 397
Zwracanie zestawu wierszy ........................................................................................................ 399
Tworzenie procedury składowanej
za pomocą aplikacji SQL Server Management Studio ........................................................ 400
Metody uruchamiania procedury składowanej ...................................................................... 404
Zwracanie wyników ................................................................................................................. 404
Polecenie RETURN .................................................................................................................... 404
Zwracanie kilku zestawów wyników ........................................................................................ 408
Sterowanie wykonywaniem kodu .......................................................................................... 409
Instrukcja IF...ELSE .................................................................................................................... 410
Instrukcja BEGIN...END ........................................................................................................... 410
Instrukcja WHILE...BREAK ..................................................................................................... 411
Instrukcja CASE .......................................................................................................................... 413
Wszystko razem ........................................................................................................................ 415
Bezpieczeństwo ......................................................................................................................... 416
Metody zabezpieczania bazy ..................................................................................................... 417
Nadawanie i odbieranie uprawnień ......................................................................................... 420
Funkcje użytkownika ............................................................................................................... 427
Funkcje skalarne ......................................................................................................................... 427
Funkcje tabelaryczne .................................................................................................................. 427
Uwagi dotyczące tworzenia funkcji .......................................................................................... 428
Podsumowanie .......................................................................................................................... 430
10
Poleć książkęKup książkęSPIS TRE(cid:165)CI
Rozdzia(cid:239) 15. Natywnie skompilowane procedury sk(cid:239)adowane ......................................... 431
Czym jest natywnie skompilowana procedura składowana? ............................................. 431
Polecenie CREATE PROCEDURE ........................................................................................ 432
Podsumowanie .......................................................................................................................... 436
Rozdzia(cid:239) 16. Podstawy skutecznego kodowania ............................................................... 437
Zmienne ..................................................................................................................................... 437
Tabele tymczasowe ................................................................................................................... 439
Agregacja danych ...................................................................................................................... 442
Funkcje COUNT() i COUNT_BIG() ...................................................................................... 442
Funkcja SUM() ............................................................................................................................ 443
Funkcje MAX() i MIN() ............................................................................................................ 443
Funkcja AVG() ............................................................................................................................ 444
Grupowanie danych ................................................................................................................. 445
Klauzula HAVING ................................................................................................................... 446
Wartości unikatowe ................................................................................................................. 447
Funkcje ....................................................................................................................................... 448
Funkcje daty i czasu .................................................................................................................... 449
Funkcje tekstowe ......................................................................................................................... 456
Funkcje systemowe ..................................................................................................................... 462
Polecenie RAISERROR ............................................................................................................ 470
Obsługa błędów ........................................................................................................................ 473
Zmienna @@ERROR .................................................................................................................. 474
Instrukcje TRY...CATCH oraz THROW ................................................................................ 475
Podsumowanie .......................................................................................................................... 480
Rozdzia(cid:239) 17. Zaawansowane programowanie i diagnostyka kodu T-SQL ......................... 481
Tworzenie sekwencji ................................................................................................................ 481
Podzapytania ............................................................................................................................. 486
Zastosowanie podzapytań ......................................................................................................... 486
Klauzula IN .................................................................................................................................. 487
Klauzula EXISTS ......................................................................................................................... 488
Zapięcie ostatniego guzika ......................................................................................................... 489
Klauzula APPLY ....................................................................................................................... 490
Klauzula CROSS APPLY ........................................................................................................... 490
Klauzula OUTER APPLY .......................................................................................................... 491
Wspólne wyrażenia tabelowe .................................................................................................. 492
Zastosowanie wspólnych wyrażeń tabelowych ...................................................................... 492
Rekursywne wyrażenia CTE ..................................................................................................... 494
Transponowanie danych ......................................................................................................... 496
Klauzula PIVOT .......................................................................................................................... 496
Klauzula UNPIVOT ................................................................................................................... 497
Zmienne tabelowe .................................................................................................................... 498
Funkcje klasyfikujące ............................................................................................................... 500
Funkcja ROW_NUMBER() ...................................................................................................... 501
Funkcja RANK() ......................................................................................................................... 503
Funkcja DENSE_RANK() ......................................................................................................... 504
Funkcja NTILE() ......................................................................................................................... 504
11
Poleć książkęKup książkęSPIS TRE(cid:165)CI
Kursory ...................................................................................................................................... 505
Diagnostyka kodu ..................................................................................................................... 511
Okna diagnostyczne ................................................................................................................... 512
Polecenia diagnostyczne ............................................................................................................ 513
Podręczne polecenia diagnostyczne ......................................................................................... 514
Podsumowanie .......................................................................................................................... 519
Rozdzia(cid:239) 18. Wyzwalacze .................................................................................................. 521
Czym jest wyzwalacz? .............................................................................................................. 521
Wyzwalacze DML ..................................................................................................................... 522
Polecenie CREATE TRIGGER do tworzenia wyzwalacza DML ......................................... 523
Czy nie lepiej stosować ograniczenia? ..................................................................................... 524
Tabele logiczne DELETED i INSERTED ................................................................................ 525
Tworzenie wyzwalacza DML typu AFTER ............................................................................. 526
Sprawdzanie wybranych kolumn ............................................................................................. 529
Wyzwalacze DDL ..................................................................................................................... 537
Zdarzenia uruchamiające wyzwalacze DDL ........................................................................... 537
Usuwanie wyzwalacza DDL ...................................................................................................... 539
Funkcja EVENTDATA() ........................................................................................................... 539
Podsumowanie .......................................................................................................................... 542
Rozdzia(cid:239) 19. Wykorzystanie bazy danych w programach .................................................. 543
Podstawy bezpieczeństwa aplikacji ........................................................................................ 544
Korzystanie z bazy za pomocą programu Excel i języka VBA
(Visual Basic for Applications) ............................................................................................ 545
Program Excel i karta Dane ....................................................................................................... 546
Program Excel i język VBA ....................................................................................................... 554
Środowisko Visual Studio ....................................................................................................... 560
Terminologia ............................................................................................................................... 560
Język VB.NET .............................................................................................................................. 561
Język C# ........................................................................................................................................ 567
Język Java ................................................................................................................................... 571
Podsumowanie .......................................................................................................................... 581
Skorowidz ..................................................................................................... 583
12
Poleć książkęKup książkęR O Z D Z I A (cid:146) 5
(cid:132) (cid:132) (cid:132)
Definiowanie tabel
Twoja utworzona właśnie baza danych musi w jakiś sposób przechowywać informacje. Bo inaczej jaki
byłby z niej pożytek? Pierwszą rzeczą, którą trzeba się zająć, jest zdefiniowanie tabel.
Aby baza mogła funkcjonować, musi zawierać przynajmniej jedną tabelę. Może ich zawierać wiele,
a w zależności od tworzonego rozwiązania liczba tabel może być bardzo duża. Dlatego dla Ciebie,
jako programisty, bardzo ważna jest jak najlepsza znajomość tabel, ich struktur i zawartości. Celem
niniejszego rozdziału jest przekazanie Ci solidnej podstawowej wiedzy niezbędnej do pracy z tabelami.
Dzięki niej będziesz mógł tworzyć inne obiekty związane z tabelami, takie jak widoki, wyzwalacze itp.
Projekt tabeli jest bardzo ważny. Każda tabela musi zawierać w swoich kolumnach odpowiednie
informacje, niezbędne do utworzenia poprawnych relacji. Jedną z cech dobrego programisty czy
administratora bazy danych jest umiejętność sprawdzenia, czy ostateczny projekt bazy przedstawia
poprawne rozwiązanie, aby w przyszłości podczas wdrażania go uniknąć uciążliwego wprowadzania
poprawek. Jeżeli na przykład zaprojektujesz system, w którym tabele będą zawierały podstawowy błąd
i trzeba będzie przenosić kolumny między tabelami, będziesz musiał zweryfikować kod każdej aplikacji,
która z tych tabel korzysta. Taka operacja może wymagać włożenia mnóstwa pracy. W rozdziale 3.
dowiedziałeś się, jak projektować bazy danych, a teraz będziesz tworzył bazę zawierającą tabele.
Wiesz już, jakie tabele będą Ci potrzebne i jakie informacje muszą zawierać.
Aby tworzyć poprawne tabele, musisz poznać następujące zagadnienia opisane w tym rozdziale:
(cid:120) Jak tworzyć tabele zapisywane na dysku i w pamięci?
(cid:120) Jakie są typy danych, które można przechowywać?
(cid:120) W jaki sposób i gdzie zapisywane są tabele na dysku i w pamięci?
(cid:120) Jak tworzyć tabele za pomocą aplikacji SQL Server Management Studio i edytora zapytań?
(cid:120) Jak tworzyć bardziej zaawansowane tabele, np.:
(cid:120) zawierające unikatowe wartości,
(cid:120) obsługujące specjalne stany danych?
(cid:120) Jak przetwarzać duże ilości danych tekstowych i binarnych?
Poleć książkęKup książkęSQL SERVER. WST(cid:125)P DLA PROGRAMISTÓW
Czym jest tabela?
Tabela to repozytorium danych, w którym jednostki informacji są rozmieszczone w jednej lub wielu
kolumnach. Tabele mogą zawierać wiele wierszy albo nie zawierać żadnych. Arkusz programu Excel
można traktować jak tabelę, aczkolwiek jest ona bardzo prosta, ponieważ nie obowiązują w niej żadne
reguły dotyczące zawartych danych albo jest ich niewiele. Gdy spojrzysz na rysunek 5.1, zauważysz,
że w pierwszych trzech kolumnach znajdują się takie informacje, jak imię, nazwisko i data urodzenia,
natomiast dane w czwartej kolumnie mają dowolny format — jest to np. numer pokoju, numer domu
i numer mieszkania. Dane nie są jednolite. W rzeczywistości kolumny w Excelu mogą zawierać
dowolne dane.
Rysunek 5.1. Arkusz programu Excel zawierający dane adresowe
Tabele w bazie danych serwera SQL Server odróżnia od innych tabel konieczność określenia
konkretnego typu danych przechowywanych w każdej kolumnie. Wybranego na początku typu danych
dla danej kolumny nie da się zmienić bez naruszenia danych we wszystkich wierszach tabeli. W przypadku
Excela jeden wiersz może zawierać tekst, drugi liczbę, kolejny walutę itd. W tabeli w bazie danych jest to
niemożliwe. Można w niej przechowywać wszystkie wymienione wartości, ale zapisane jako tekst, dlatego
zastosowanie bazy danych w opisanym przypadku jest bezzasadne.
Podczas tworzenia tabeli dla każdej kolumny należy wybrać określony typ danych. Dlatego tworzenie
tabeli wymaga dokładnych przemyśleń, aby kolumny zawierały dane najbardziej odpowiedniego typu.
Nie ma powodu, aby wybierać ogólny typ danych (na przykład tekst), dopuszczający wszystkie możliwe
wartości, ponieważ później trzeba będzie zweryfikować projekt takiej tabeli. Wybranie typu tekstowego
dla wszystkich kolumn może być przyczyną problemów z użyciem operacji matematycznych, na przykład
przy dodawaniu podatku do ceny lub dodawaniu liczby dni do daty zamówienia w celu określenia daty
dostawy towaru.
Zadaniem tabeli jest przechowywanie określonych informacji. Tabela musi mieć opisową nazwę
i jedną lub wiele kolumn, z których każda również musi mieć zrozumiałą nazwę i określony typ danych.
Aby zacząć tworzyć tabele, musisz połączyć się z serwerem SQL Server, używając konta z przypisanymi
właściwymi rolami serwerową i bazodanową, na przykład sysadmin lub db_ddladmin, umożliwiającymi
tworzenie tabel. Tabela po utworzeniu musi być umieszczona w bazie danych, co osiąga się przez przypisanie
tabeli do schematu. Jak pamiętasz, w rozdziale 4. opisane zostało grupowanie obiektów, stanowiące
podstawę ich bezpieczeństwa. Tobie, jako programiście, umieszczenie obiektów w schematach pozwoli
utworzyć bazę o bardziej logicznej strukturze i skróci czas programowania, ponieważ obiekty będą
uporządkowane w określony sposób.
Niektóre typy danych mają na stałe określoną ilość zajmowanego przez nie miejsca, natomiast
w przypadku innych typów Ty musisz zadecydować, ile maksymalnie znaków mogą zajmować.
Kolumna, która będzie zawierać nazwiska, musi przechowywać dane tekstowe. Nie ma sensu ustalać
maksymalnej długości danych w tej kolumnie na 10 znaków, ponieważ wiele nazwisk jest dłuższych.
Podobnie nie można konfigurować maksymalnej długości na 1000 znaków. Musisz wybrać rozsądny
kompromis na podstawie analizy danych, którą przeprowadziłeś w rozdziale 3. Ustawienia dotyczące
wielkości danych można określić na podstawie istniejącej bazy danych, którą będziesz rozbudowywał,
albo innej, zawierającej podobne dane. Na przykład, jeżeli projektujesz bazę danych do obsługi sprzedaży,
ilość miejsca dla numeru produktu można określić na podstawie magazynowej bazy danych, zawierającej
wszystkie szczegółowe informacje o produktach, w tym ich numery.
132
Poleć książkęKup książkęROZDZIA(cid:146) 5. (cid:132) DEFINIOWANIE TABEL
(cid:132) Uwaga Nawet je(cid:285)eli u(cid:285)ywasz danych o zmiennej d(cid:239)ugo(cid:258)ci, dla których ustala si(cid:218) maksymaln(cid:200) liczb(cid:218) znaków,
serwer SQL Server i tak zapisuje tylko faktycznie wprowadzone dane. Mo(cid:285)esz zatem utworzy(cid:202) kolumn(cid:218) na
nazwiska, mog(cid:200)c(cid:200) pomie(cid:258)ci(cid:202) maksymalnie 1000 znaków. Miejsce i tak nie b(cid:218)dzie tracone, poniewa(cid:285) wi(cid:218)kszo(cid:258)(cid:202)
nazwisk jest znacznie krótsza. Musisz jednak zachowa(cid:202) rozs(cid:200)dn(cid:200) i przemy(cid:258)lan(cid:200) równowag(cid:218). Nazwisko o d(cid:239)ugo(cid:258)ci
1000 znaków j(cid:200) zak(cid:239)óca.
Wiersze danych przechowywanych w tabeli powinny być ze sobą logicznie powiązane. Jeżeli
definiowana tabela ma przechowywać informacje o klientach, to nie powinna zawierać żadnych innych
danych. Absolutnie nie powinieneś umieszczać w niej informacji niezwiązanych z klientami. Byłoby
logicznie nieuzasadnione umieszczanie w takiej tabeli na przykład danych o zamówieniach klientów.
Podczas definiowania kolumny nie ulegaj pokusie umieszczania w niej więcej niż jednego rodzaju danych.
Na przykład kolumna, która ma zawierać datę złożenia zamówienia, może również zawierać datę jego
realizacji, ale pojawi się wtedy problem określenia, co oznacza określona data. Dzięki utworzeniu osobnych
kolumn dla obu rodzajów dat sprawa będzie zupełnie jasna.
(cid:132) Uwaga S(cid:200) dwa rodzaje tabel — tabele systemowe i tabele u(cid:285)ytkownika. Z tabel systemowych nie mo(cid:285)na
korzysta(cid:202) bezpo(cid:258)rednio, niektóre zawarte w nich informacje s(cid:200) dost(cid:218)pne za pomoc(cid:200) widoków systemowych.
Innym typem tabeli dostępnym dla programistów w serwerze SQL Server jest tabela plikowa (FileTable).
Jest to tabela specjalnego typu, umożliwiająca przechowywanie plików o nieokreślonej strukturze,
na przykład dokumentów programu Word czy arkuszy Excel. Dzięki technologii FILESTREAM można
w serwerze SQL Server przeszukiwać takie pliki. W tej książce opisane są tylko zwykłe tabele użytkownika.
Jest jeszcze trzeci typ tabel — przechowywanych w pamięci. Są one opisane w osobnym rozdziale 7.,
ponieważ tabele tego typu pod wieloma względami różnią się od typowych tabel zapisywanych na dysku.
Różnice obejmują stosowane typy danych, wymagania, jakie musi spełnić baza i tabela, oraz aspekty
wydajnościowe. Na razie pamiętaj jedynie, że tabele i zawarte w nich dane mogą być umieszczane
na dysku lub w pamięci.
(cid:132) Uwaga Cho(cid:202) ten rozdzia(cid:239) jest po(cid:258)wi(cid:218)cony tabelom zapisywanym na dysku, wiele informacji tu zawartych dotyczy
równie(cid:285) tabel zapisywanych w pami(cid:218)ci.
Typy danych w serwerze SQL Server
W poprzednich rozdziałach wiele dowiedziałeś się o serwerze SQL Server, jeszcze przed utworzeniem
swojej pierwszej tabeli. Jednak uzyskanie tych informacji przed utworzeniem tabeli i zabezpieczeniem
bazy jest bardzo ważne, ponieważ pozwala uniknąć nieprzyjemnych konsekwencji, gdy coś zacznie iść
źle. Wiesz również, dlaczego należy być ostrożnym, nadając użytkownikom uprawnienia do tworzenia
tabel. W tej części rozdziału poznasz typy danych dostępne w serwerze SQL Server. Niektóre z nich
mogą być przypisane do kolumn w tabeli, a inne użyte w kodzie T-SQL.
Tabele można definiować za pomocą aplikacji SQL Server Management Studio, edytora zapytań
Query Editor lub narzędzia do projektowania baz danych w serwerze SQL Server. Możesz również użyć
innych metod, na przykład narzędzia sqlcmd, narzędzi programistycznych czy języków programowania,
jednak w tej książce skupimy się na trzech wymienionych wcześniej metodach. Pierwszą tabelę utworzysz
za pomocą aplikacji SQL Server Management Studio. Będzie to tabela Klienci, zawierająca dane o wszystkich
klientach. Zanim jednak to zrobisz, musisz poznać różne typy danych, które można przechowywać w tabeli.
133
Poleć książkęKup książkęSQL SERVER. WST(cid:125)P DLA PROGRAMISTÓW
Typy danych w tabelach
Serwer SQL Server oferuje wiele typów danych, które można określić dla każdej kolumny. Ta część
rozdziału zawiera opis różnych typów i pomoc w wyborze właściwego typu dla każdej kolumny tabeli.
Opisane typy nazywane są typami podstawowymi. Cała książka skupia się tylko na typach podstawowych.
Możesz jednak tworzyć własne niestandardowe typy, co ma swoje zalety. Jeżeli chcesz mieć spójne dane
w określonej kolumnie w różnych tabelach, zazwyczaj na tym samym serwerze, ale nie jest to konieczność,
możesz utworzyć własny typ danych i nadać mu nazwę. Taki typ może być następnie wykorzystany
podczas definiowania tabeli. Przykładem mogą być numery NIP, które muszą mieć taki sam format.
Możesz utworzyć własny typ danych oparty na typie podstawowym i nadać mu nazwę. Następnie możesz
utworzyć kolumnę zawierającą numery NIP, wykorzystując własny typ danych zamiast podstawowego.
Zademonstruję ten sposób po opisaniu typów podstawowych.
(cid:132) Uwaga Do tworzenia bardziej z(cid:239)o(cid:285)onych typów danych i uzyskania dodatkowych funkcjonalno(cid:258)ci mo(cid:285)esz
wykorzysta(cid:202) technologi(cid:218) .NET.
Niektóre typy danych mogą wydawać Ci się podobne, jednak pamiętaj, że każdy ma swoje określone
przeznaczenie. Na przykład, jeżeli nie musisz przechowywać danych zapisanych w formacie Unicode,
nie używaj typów o nazwach zaczynających się na literę n. Tekst zapisany w formacie Unicode zajmuje
więcej miejsca, ponieważ format ten obejmuje większy zbiór znaków, który może być obsługiwany przez
serwer SQL Server. Ponadto, jeżeli największa liczba zapisana w kolumnie będzie równa 100, nie używaj
typu, który pozwala na zapisanie największej możliwej liczby, ponieważ byłaby to strata miejsca na dysku.
Przyjrzyjmy się dostępnym typom podstawowym, których możesz użyć w tabeli. Później poznasz
typy stosowane w programach. We wszystkich przykładach opisanych w tej książce są stosowane różne
opisane niżej typy danych.
Typ char/nchar
Dane typu char mają stałą długość, określaną podczas definiowania kolumny, i mogą zawierać
maksymalnie 256 znaków. Jeżeli zdefiniujesz kolumnę zawierającą dane o długości 20 znaków, taka
liczba znaków będzie zapisywana. Jeżeli dane będą miały mniej znaków, dodatkowe miejsca zostaną
wypełnione spacjami. Jeżeli typ kolumny będzie zdefiniowany jako char(10), ciąg aaa zostanie zapisany
jako aaa . Stosuj ten typ danych wtedy, gdy dane w kolumnie muszą mieć stałą długość, jak
na przykład identyfikatory klientów lub numery kont. Jeżeli zdefiniujesz kolumnę bez podania długości
danych, zostanie przyjęta wartość 1. Nie jest to jednak dobrą praktyką; dla przejrzystości wszystkie
kolumny powinny mieć ustaloną długość.
Typ varchar/nvarchar
Typ varchar służy do przechowywania danych alfanumerycznych, podobnie jak typ char. Różnica polega
na tym, że każdy wiersz może zawierać różną liczbę znaków, do maksymalnej określonej dla danej kolumny.
Jeżeli kolumna ma zdefiniowany typ varchar(50), dane w tej kolumnie mogą mieć maksymalną długość
50 znaków. Jeżeli jednak zostanie wpisany ciąg składający się tylko z trzech znaków, zostaną zajęte tylko
trzy miejsca na dysku. Ten typ doskonale nadaje się w sytuacjach, gdy dane nie mają określonej długości,
na przykład nazwiska lub opisy. Maksymalna długość danych typu varchar jest równa 8000 znaków. Jeżeli
zdefiniujesz kolumnę, nie podając długości, tj. stosując typ varchar(), zostanie przyjęta domyślna długość 1.
Stosując w definicji typu stałą max, możesz ominąć ograniczenie długości do 8000 znaków. Użyj tej
opcji, jeżeli wiesz, że dane przynajmniej w jednym wierszu przekroczą długość 8000 znaków. Ponadto
stałej max możesz używać zamiast typu text do przechowywania dużych bloków tekstu.
134
Poleć książkęKup książkęROZDZIA(cid:146) 5. (cid:132) DEFINIOWANIE TABEL
Typ text/ntext
Typ text służy do przechowywania danych, których długość przekracza 8000 znaków. Nie stosuj jednak
tego typu, ponieważ jest on nieaktualny i zostanie wycofany. Zamiast niego stosuj typ varchar(max).
Typ image
Typ image jest bardzo podobny do typu text, z tym wyjątkiem, że służy do przechowywania danych
binarnych. Jednakże typ ten, podobnie jak text i ntext, będzie wycofany i zamiast niego należy
stosować typ varbinary(max).
Typ int
Typ int, lub integer, służy do przechowywania wartości liczbowych bez miejsc po przecinku
(liczby całkowite). Zakres liczb jest ograniczony, mogą to być wartości od –2 147 483 648 do 2 147 483 647.
Typ bigint
Typ bigint, lub big integer, jest podobny do typu int, jednak umożliwia przechowywanie znacznie
większych liczb, z zakresu od –9 223 372 036 854 775 808 do 9 223 372 036 854 775 807.
Typ smallint
Typ smallint, lub small integer, służy do przechowywania małych liczb całkowitych, z zakresu
od –32 768 do 32 767. Zachowaj ostrożność podczas definiowania kolumn tego typu i upewnij się, że
na pewno przechowywane liczby nie przekroczą dopuszczalnych wartości. Podczas tworzenia kolumn
zawierających ten typ danych zawsze istnieje niebezpieczeństwo, że kiedyś będziesz musiał wrócić
do definicji kolumny i zmienić jej typ. Dlatego na wszelki wypadek stosuj typ int.
Typ tinyint
Typ tinyint, lub tiny integer, służy do przechowywania jeszcze mniejszych liczb niż typ smallint, tj.
z zakresu od 0 do 255. Typ ten może być na przykład stosowany do przechowywania numerów województw.
Typ decimal/numeric
Oba powyższe typy służą do przechowywania liczb z tego samego zakresu wartości i o tej samej precyzji.
Zakres wartości obejmuje liczby od –1038+1 do 1038–1. Precyzja zawiera się w przedziale od
0,00000000000000000000000000000000000001 do 10 000 000 000 000 000 000 000 000 000. Bądź jednak
ostrożny, ponieważ nie możesz przechowywać liczb zawierających po 38 cyfr zarówno przed przecinkiem,
jak i po nim. Liczby mogą składać się w sumie z maksymalnie 38 cyfr. Zatem im większa jest wymagana
dokładność, czyli liczba cyfr po przecinku, tym mniejsza może być liczba cyfr przed przecinkiem.
Typ float
Moim zdaniem typy float i real są dla początkującego programisty najbardziej niebezpiecznymi
typami danych. Powinny być stosowane jedynie w sytuacjach wyjątkowych i należy ich unikać, jeżeli
wymagane jest zachowanie pełnej dokładności liczb. Typ float jest stosowany do przechowywania
wartości o zmiennej liczbie cyfr po przecinku, z zakresu od –1,79E+308 do 1,79E+308. Muszę Cię
jednak ostrzec: to nie są wartości w 100 precyzyjne, ponieważ liczby, ze względu na sposób zapisu
liczby w formacie binarnym, mogą być różnie zaokrąglane. Mogą pojawić się problemy z liczbami
zawierającymi cyfrę 3, 6 lub 7 po przecinku. Liczby te są zaokrąglane, ponieważ czasami zawierają
więcej cyfr po przecinku, niż można zapisać. Typowym przykładem jest liczba pi.
135
Poleć książkęKup książkęSQL SERVER. WST(cid:125)P DLA PROGRAMISTÓW
Typ real
Typ real jest bardzo podobny do typu float, z tym wyjątkiem, że służy do przechowywania liczb z zakresu
jedynie od –3,40E+38 do 3,40E+38. Są to również przybliżone wartości. Ze względu na zakres wartości typy
real i float wydają się idealne. Jeżeli jednak musisz przechowywać dokładne wartości, sprawdź inne typy.
Typ money
Typ money służy do przechowywania liczb z maksymalnie czterema cyframi po przecinku. Jeżeli potrzebna
jest większa dokładność, musisz użyć innego typu, na przykład decimal. Typ money nie zawiera symbolu
waluty, dlatego nie można go stosować do przechowywania wartości wyrażonych w różnych walutach.
Typ ten obejmuje wartości z zakresu od –922 337 203 685 477,5808 do 922 337 203 685 477,5807. Jeżeli
musisz zapisać symbol waluty ($ dla dolara, € dla euro itp.), musisz go przechowywać w osobnym miejscu.
Typ smallmoney
Typ podobny do money, ale przeznaczony do przechowywania wartości z zakresu od –214 748,3648
do 214 748,3647.
Typ date
Typ date służy do przechowywania dat od 1 stycznia 1 r. do 31 grudnia 9999 r. Stosowany jest format
zapisu RRRR-MM-DD. W wersjach oprogramowania SQL Server 2008 i starszych nie można było stosować
osobnych typów do przechowywania daty i czasu, chyba że utworzyło się je za pomocą technologii .NET.
W efekcie trzeba było zapisywać niepotrzebne dane. Na przykład często zdarza się, że kolumna typu
datetime zawiera tylko daty. Wprowadzenie typu date jest dużym usprawnieniem oprogramowania,
ponieważ dzięki niemu możliwość popełnienia pomyłki jest mniejsza i uzyskuje się właściwe wyobrażenie
o danych przechowywanych w kolumnie.
Typ datetime
Typ datetime służy do przechowywania daty i czasu z zakresu od 1 stycznia 1753 r. do 31 grudnia 9999 r.
Jednak wartości tego typu mogą zawierać nie tylko datę, ale również oznaczenia czasu. Jeżeli do kolumny
typu datetime wpiszesz tyl
Pobierz darmowy fragment (pdf)