Rozdział 9.
Typy danych i tworzenie tabel

Poprzedni rozdział omawiał odtwarzanie baz danych z kopii bezpieczeństwa. Omówione zostały różne mechanizmy odzyskiwania używane przez SQL Server, włączając w to odtwarzania ręczne i automatyczne. Został również omówiony sposób odtwarzania pojedynczej bazy danych, jak również stosowanie kopii dziennika transakcji aby doprowadzić do odtworzenia bazy do stanu z określonego punktu w czasie. Teraz, kiedy już wiadomo, jak tworzyć plany odtwarzania i obsługiwać kopie bezpieczeństwa, zostanie przedstawione tworzenie tabel i dodawanie danych do bazy.

Niniejszy rozdział omawia dodawanie tabel do bazy danych. Po pierwsze, zostanie zaprezentowany przegląd tabel oraz różne typy danych używane do tworzenia tabel. Następnie, zostaną podsumowane informacje na temat tworzenia i modyfikacji tabel w bazie danych przy pomocy kodu. Następnie zostanie omówione jak firma Microsoft uprościła proces kodowania poprzez umożliwienie graficznego tworzenia tabel przy pomocy SQL Server Enterprise Managera.

Tabele

Tabele przechowują wszystkie dane w bazie danych i są zorganizowane w postaci wierszy i kolumn (rekordy i pola). Każda kolumna w tabeli może przechowywać określony typ informacji lub typ danych.

Pojedyncza tabela reprezentuje jednostkę w bazie danych. Każdy wiersz w tabeli reprezentuje wystąpienie tej jednostki w bazie. Kolumny w tabeli opisują atrybuty logicznego elementu jakim jest tabela. Przykładowo, należy stworzyć tabelę pracowników (w której pracownicy są logicznymi jednostkami). Pojedynczy wiersz w tabeli pracowników reprezentuje pojedyncze wystąpienie danego pracownika. Kolumny, tworzące tabelę opisują pracownika. Niektóre kolumny mogą zawierać EmployeeID (identyfikator pracownika), FirstName (imię), LastName (nazwisko) oraz SSN — Social Security Number (numer ubezpieczenia).

Kolumny

Każda kolumna (pole) w tabeli musi mieć przypisaną nazwę, typ danych, długość (opcjonalnie), sposób kodowania (opcjonalnie) i status (dopuszczalność lub nie) wartości NULL. W definicji tabeli można umieścić kolumny w dowolnej kolejności. Każda kolumna musi mieć również w obrębie tabeli unikalną nazwę i spełniać warunki identyfikatorów SQL Servera (opisane w następnej sekcji).

Pojedyncza baza danych może zawierać do 2 bilionów tabel, z których każda może mieć 1,024 kolumn. Maksymalna długość wiersza to 8,192 bajty, minus narzuty jakie wynikają z fizycznego przechowywania wierszy w SQL Serverze. Narzut jest różny i zależy od wielu czynników. Omówienie tej kwestii nie jest objęte zakresem tej książki. Rzecz jasna, maksymalna wielkość przestrzeni danych wynosi 8,060 bajtów dla wiersza.

Maksymalna długość dla pojedynczej kolumny wynosi 8,000 bajtów, i można umieścić do 1,024 kolumn w wierszu; jednakże, wiersz nie może przekroczyć wielkości strony danych — 8,192 bajty dla danych wraz z narzutem. Wyjątkiem są dane typu text i image, które pozwalają na przechowywanie do 2 GB informacji tekstowej lub binarnej. Nie są one fizycznie przechowywane jako część wiersza tabeli, czyli nie naruszają wymagania, aby wiersz danych pasował do pojedynczej strony danych. Więcej szczegółów na temat długości kolumn oraz narzutów tabel i wierszy zostanie podanych w dalszej części rozdziału.

Maksymalna wielkość przestrzeni pamięci w stronie danych dla danych wynosi 8,060 bajtów. Inaczej mówiąc, minimalna wielkość narzutu na stronę danych wynosi 132 bajty.

Identyfikatory SQL Servera

Wszystkie nazwy tabel SQL Servera, jak również nazwy kolumn i wszelkie inne nazwy obiektów muszą spełniać reguły dotyczące identyfikatorów SQL Servera:

• Identyfikatory mogą mieć długość od 1 do 128 znaków Unicode, włączając w to litery, symbole i liczby.

• Pierwszy znak musi być literą lub jednym z następujących symboli: @,# lub _ (podkreślenie), @ i # mają specjalne znaczenie w SQL Serverze.

• Po pierwszym znaku można używać następujących symboli: #, $ oraz _.

• Identyfikatory rozpoczynające się od symbolu @ są używane jako zmienne lokalne. Symbol @ może się pojawiać jedynie jako pierwszy znak identyfikatora.

• Identyfikatory, rozpoczynające się symbolem # są używane do oznaczania obiektów tymczasowych i użytkownik może się do nich odwołać podczas sesji.

• Identyfikatory rozpoczynające się dwoma symbolami ## oznaczają obiekty, które zostały utworzone jako tymczasowe obiekty globalne i mogą się do nich odwoływać wszyscy użytkownicy w bazie danych.

W identyfikatorach można używać pustej przestrzeni (spacji), ale należy się do nich odwołać biorąc nazwę w podwójny cudzysłów lub w nawiasy kwadratowe. Przykładowo, do tabeli w bazie danych nazwanej Employee Pension można się odwołać w ten sposób: "Employee Pension" lub w ten sposób: [Employee Pension]. Preferowane jest używanie składni z nawiasami kwadratowymi (jeżeli muszą być koniecznie używane spacje w nazwie identyfikatorów) aby nazwy identyfikatorów SQL Servera nie były mylnie traktowane jako łańcuchy stałych znakowych.

Pomimo tego, że w SQL Serverze dozwolone jest używanie symboli i spacji w nazwach identyfikatorów, większość projektantów unika tego, ponieważ może to prowadzić do mylnej interpretacji wyrażeń SQL.

Tabela 9.1 pokazuje przykłady prawidłowych i nieprawidłowych identyfikatorów.

Tabela 9.1 Prawidłowe i nieprawidłowe identyfikatory.

Identyfikator	Komentarz
1001ArabianNights	Nieprawidłowy identyfikator. Nazwy identyfikatorów muszą zaczynać się literą.
@Address	Ten identyfikator jest prawidłowy jedynie do tworzenia zmiennej.
Table@Address	Nieprawidłowy identyfikator. Symbol @ może występować jedynie jako pierwszy znak w nazwie identyfikatora.
#tblCities	Ten identyfikator jest prawidłowy jedynie przy tworzeniu lokalnego obiektu tymczasowego. Lokalny obiekt tymczasowy jest dostępny jedynie dla użytkownika, który go stworzył.
##tblCities	Ten identyfikator jest prawidłowy przy tworzeniu globalnego obiektu tymczasowego, dostępnego dla wszystkich użytkowników.
tblEmployee	Prawidłowy identyfikator. Pomimo tego, że nie ma potrzeby stosowania prefiksów identyfikatorów (tbl — skrót od table), wyrażenia SQL są łatwiejsze do analizowania ponieważ prefiks określa co reprezentuje identyfikator. Inne popularne identyfikatory to qry od query, tr — trigger, sp — dla systemowych procedur składowych i wiele innych. .
Titles, Author_Review, AuthorReview	Te identyfikatory są prawidłowe.

Typy danych

Typy danych określają jakiego rodzaju informacje (liczbowe, znakowe i inne) zostaną umieszczone w danej kolumnie i jaką ilość przestrzeni zajmą w tej kolumnie. Niektóre typy danych mają zmienną długość, podczas gdy inne mają długość ustaloną. Typy danych SQL Servera można podzielić na następujące grupy: łańcuchy (string), dane Unicode, binarne, całkowite (integer), liczbowe przybliżone i dokładne, specjalne, rodzaju: data i czas, waluta, automatycznie przyrastające, synonimy, zdefiniowane przez użytkownika i wyliczeniowe (computed column). Tabela 9.2. przedstawia typy danych dozwolone w SQL Serverze 2000.

Tabela 9.2. Typy danych obsługiwane przez SQL Server 2000

Kategoria	Typ danych	Komentarz
Łańcuchowy	char(n), varchar(n)	Przechowuje łańcuchy znaków.
Binarny	binary(n), varbinary(n)	Przechowuje informacje binarne w parach po 2 bajty.
Całkowity	int, smallint, bigint,tinyint	Przechowuje wartości całkowite.
Przybliżony numeryczny	float, real	Przechowuje przybliżone wartości liczbowe.
Dokładny numeryczny	decimal, numeric	Przechowuje dokładne informacje liczbowe.
Specjalny	bit, text, image, sql_variant, table	Przechowuje pojedynczy bit, informacje znakowe większe niż 8000 bajtów lub dane obrazu. sql_variant prawie nic nie przechowuje a typ table przechowuje tabele.
Data i Czas	datetime, smalldatetime	Przechowuje wartości daty i czasu.
Waluta	money, smallmoney	Przechowuje wartości walutowe.
Automatycznie zwiększany	rowversion (formalnie timestamp)	Przechowuje wartości typów danych zwiększanych automatycznie lub ustawionych przez SQL Server.
Synonimy	(Zobacz tabela 9.9)	Mapuje typ danych ANSI do typów danych SQL Servera.
Dane Unicode	nchar, ntest, nvarchar	Przechowuje dane w formacie Unicode (podwójny bajt na znak).
Zdefiniowany przez użytkownika		Można tworzyć własne typy danych do przechowywania informacji.
Wyliczeniowy		Przechowuje wyrażenie służące do wyliczenia wartości kolumny. Nie przechowuje danych, jedynie wyliczenia służące do utworzenia tych danych.

Nullability

Nullability (dopuszczanie w kolumnie wartości NULL) odnosi się do tego czy wpis w danej kolumnie jest wymagany. Jeżeli kolumna ma zgłaszać, ze wpisana wartość jest nieznana, należy ustawić wartość NULL. Jeżeli założeniem jest, że każdy wiersz musi mieć wpis w tej kolumnie, należy określić kolumnę jako NOT NULL. Jeżeli nie zostanie określona wartość NULL lub NOT NULL, używana jest domyślna wartość przypisana do bazy danych (lub do połączenia z SQL Serverem). Po pierwszym zainstalowaniu SQL Servera, domyślnie dla każdej tworzonej w bazie danych kolumny ustawiona jest opcja NOT NULL. Jak w przypadku większości opcji w SQL Serverze, domyślna wartość może być modyfikowana, nawet na poziomie poszczególnych połączeń. Dlatego, dobrze jest zawsze określać czy dana kolumna ma mieć ustawiony atrybut NULL lub NOT NULL.

Collate

Można również określić sposób kodowania (collate) dla indywidualnych kolumn danych w tabeli. Jeżeli nie zostanie określona nazwa sposobu kodowania, SQL Server będzie używał domyślnego sposobu notacji bazy danych.

Łańcuchy

Łańcuchy zawierają dane znakowe utworzone z liter, liczb i symboli. Można przechowywać dane znakowe w formacie o ustalonej lub zmiennej długości pola przy pomocy słów kluczowych char(n) lub varchar(n). Można przechowywać maksymalnie 8000 znaków w danych tych typów.

Po utworzeniu pola o ustalonej długości, należy określić, że pole to będzie zawsze zawierało n bajtów informacji. Jeżeli dane wpisane w to pole zawierają mniej niż n informacji, pole zostanie dopełnione spacjami do wartości n bajtów. Przy próbie wpisywania do pola więcej niż n bajtów danych, dane są „obcinane”. Tabela 9.3. pokazuje niektóre przykłady wpisywania danych w pole zadeklarowane jako sFname char(8). (Symbol * oznacza spacje w przykładach).

Tabela 9.3. Pola znakowe o określonej długości

Wpisywane dane	Zawartość sFname
Lawrence	Lawrence
Mark Anthony	Mark Ant
Denise	Denise**

Gdy używane są pola o zmiennej długości, określa się maksymalną długość pola. Ale przeciwnie jak w przypadku pól o ustalonej długości, te pola nie są dopełniane spacjami. Używanie tego typu pól może powodować efektywniejsze wykorzystanie pamięci przez bazę danych, ale wpływa nieznacznie na obniżenie wydajności. Kiedy pole jest zadeklarowane ze zmienną długością, SQL Server musi określić gdzie kończy się dane pole i zaczyna się następne. Z polami tego typu związany jest dodatkowy narzut w formie bajtów dodawanych do wierszy w tabeli. Typ varchar jest użyteczny w przypadku, gdy przewiduje się w polach dane różnego rozmiaru lub gdy zamierza się zezwolić na wartości nieokreślone (null).

Wpisując w SQL Serverze dane znakowe, należy objąć je cudzysłowem pojedynczym lub podwójnym. Pojedynczy cudzysłów jest preferowany, ponieważ nie powoduje pomyłek w rozróżnieniu stałych znakowych i identyfikatorów SQL Servera. Aby wpisać NULL w polu SQL Servera, należy użyć słowa kluczowego NULL bez cudzysłowia.

Wydruk 9.1 pokazuje przykład tworzenia tabeli korzystającej ze słów kluczowych char i varchar.

Wydruk 9.1. Używanie char i varchar

Create TABLE tblCustomers

(

strCustID char(8) NOT NULL,

strCustName varchar(30) NOT NULL,

strEmail varchar(50) NULL

)

Autor zaleca prefiksowanie nazw kolumn literami, reprezentującymi typ danych w kolumnie. Konwencja ta pozwala projektantom aplikacji na rozpoznanie, jakiego typu dane zawiera pole w kolumnie. Przykładowo, widząc kolumnę nazwaną SocialSecurityName nie wiadomo, czy pole zawiera dane typu liczbowego czy znakowego. Jeżeli zostanie użyty, pisany małymi literami prefiks str, oznaczający dane znakowe (string — łańcuch znaków) — char, varchar, nvarchar, następujące pole może reprezentować kolumnę zawierającą łańcuchy znaków: strSocialSecurityNumber.

Dane binarne

Typ binarny przechowuje dane binarne. Dane binarne są przechowywane seriami zer i jedynek, które są reprezentowane na wejściu i wyjściu jako pary heksadecymalne. Te heksadecymalne pary są tworzone ze znaków od 0 do 9 i od A do F. Przykładowo, tworząc pole binSomeData binary(20), określa się, że w tym polu ma być przechowywane 20 bajtów znaków binarnych.

Jak w przypadku danych typu łańcuchowego, można określić maksymalnie 8000 bajtów dla danych typu binary(n) i varbinary(n). W przypadku używania typu binary(n), wpisana informacja jest uzupełniana spacjami (0x20). W przypadku danych varbinary(n) dane nie są dopełniane spacjami. Przy próbie wpisywania danych dłuższych niż określona maksymalna długość, dane są obcinane.

Aby wpisać dane w typie binarnym, należy poprzedzić łańcuch wpisaniem 0x. Przykładowo, aby wpisać wartość 10 w pole binarne, należy wpisać go w postaci 0x10.

Przykładowe wyrażenia:

MyIcons varbinary(255)

MyCursors binary(200)

binTinywav varbinary(255)

Liczby całkowite

Istnieją cztery rodzaje danych typu całkowitego (integer), które mogą przechowywać dokładne, skalarne wartości: bigint, int, smallint i tinyint. Różnica pomiędzy typami danych całkowitych występuje w ilości przestrzeni pamięci, której wymagają oraz w zakresie wartości jakie mogą przechowywać. Tabela 9.4 pokazuje typy danych całkowitych (integer) i ich zakresy.

Tabela 9.4. Typy danych całkowitych

Typ danych	Długość	Zakres
tinyint	1	0 - 255
smallint	2	+-32,767
int	4	+- 2,147,483,657
bigint	8	+-2^63

Przykładowe wyrażenia:

bytEmployessAge tinyint NULL

intEmployeeID smallint NOT NULL

lngcustomerID int NOT NULL

bigWebOrderID bigint NOT NULL

--> W powyższej [Author:AK] liście, została użyta następująca konwencja nazewnictwa zmiennych:

• byt dla danych typu tinyint (byt jest skrótem od byte).

• int dla danych typu smallint. Jest to standardowy typ całkowity 2-bajtowy.

• lng dla danych typu int. Ponieważ dane 4-bajtowe są nazywane long integers w wielu językach programowania, czytelne jest używanie lng dla danych całkowitych 4-bajtowych.

• big dla danych typu bigint.

Oczywiście, prefiksowanie nazw kolumn nie jest obligatoryjne. Wiele z baz danych nie posiada prefiksowanych typów danych.

--> Prefiksowanie[Author:AK] jest pójściem w nowym kierunku. Pomimo tego, że wygląda jakby SQL Server miał więcej działań do wykonania, faktycznie tak nie jest. SQL Server zastępuje wszystkie te nazwy kolumn znacznikami, które spełniają wewnętrzny format i są silnie zoptymalizowane.

Typy danych całkowitych mają lepszą wydajność (w znaczeniu przechowywania, odtwarzania i kalkulacji matematycznych) niż inne typy danych. Jeżeli można użyć typu całkowitego, to należy z niego skorzystać. SQL Server obsługuje domyślną maksymalną precyzję na poziomie 28 cyfr aby zapewnić zgodność z limitami narzędzi programistycznych, takimi jak Microsoft Visual Basic i PowerBuilder. Możliwa jest modyfikacja tej wartości. Jeżeli SQL Server zostanie uruchomiony z opcją /p, domyślną precyzją będzie 38 cyfr.

Przybliżone i dokładne liczbowe typy danych

SQL Server zezwala na obydwa przybliżone typy danych (float i real), jak również na dokładne typy danych liczbowych (decimal i numeric). Jeżeli zostaną zadeklarowane przybliżone typy danych, określa się precyzję, która jest maksymalną ilością cyfr po obydwu stronach przecinka dziesiętnego. Jeżeli zostają zadeklarowane dane typu dokładnego, należy również określić scale, co oznacza całkowitą ilość cyfr jaka może wystąpić po przecinku.

Tabela 9.5. pokazuje wartości precyzji dostępne do dokładnych i przybliżonych liczbowych typów danych oraz ilość bajtów potrzebnych na ich przechowywanie.

Tabela 9.5. Wymagania precyzji i przechowywania

Precyzja	Przechowywanie
1-9	5
10-19	9
20-19	13
30-38	17

Typy danych liczbowych przybliżonych

Przybliżonymi typami danych liczbowych są float i real. Liczby przechowywane przy pomocy tych typów danych składają się z dwóch części: mantysy i wykładnika. Algorytm używany do określenia takiej pary nie jest szczególnie precyzyjny. Inaczej mówiąc, można nie otrzymać z powrotem tego, co zostało wprowadzone. Staje się to problemem jedynie gdy precyzja przechowywanej liczby osiągnie precyzję określoną przy typ danych. Przykładowo, precyzja zezwala na liczby zmiennoprzecinkowe o wielkości do 38 cyfr. Liczby zmiennoprzecinkowe i rzeczywiste są przydatne dla danych naukowych i statystycznych, dla których absolutna dokładność nie jest niezbędna, ale potrzebny jest zakres wartości od bardzo małych do ogromnie dużych liczb.

Liczby rzeczywiste mają precyzję 7 cyfr i wymagają 4 bajtów na przechowywanie danych. Jeżeli zostanie zadeklarowana liczba typu float o precyzji mniejszej niż 7, naprawdę zostanie utworzona dana typu rzeczywistego (real). Liczby zmiennoprzecinkowe mogą mieć dokładność od 1 do 38. Domyślnie, liczba zmiennoprzecinkowa ma precyzję 15 cyfr jeżeli nie zostało określone inaczej. Na liczbach typu float można wykonywać wszystkie operacje z wyjątkiem modulo (która zwraca resztę całkowitą z dzielenia całkowitego). Przyjmijmy, ze został utworzony następujący typ danych:

sngSomeVal real

Można w nim przechowywać liczby 188,445.2 lub 1,884.452 ale nie można przechowywać wartości 188,445,27 lub 1,884.4527 ponieważ są one dłuższe niż domyślna dokładność czyli 7 cyfr. Aby przetrzymywać te większe wartości, należy utworzyć zmienną typu float z dokładnością wystarczająco dużą, aby przetrzymać wszystkie cyfry. Ponieważ wymagane jest 8 bajtów na przechowywanie, deklaracja takiego typu danych wygląda następująco:

dblSomeVal float(8)

Autor używa prefiksu sng dla typu real. sng jest skrótem od single-precision floating-point variable. Do reprezentacji liczb typu float, autor stosuje skrót dbl, oznaczający double-precision floating-point variable. Prefiksy te odnoszą się do ilości przestrzeni pamięci potrzebnej do przechowywania tych typów danych. W Visual Basic, liczby pojedynczej precyzji wymagają 4 bajtów, a podwójnej precyzji 8 bajtów przestrzeni.

Dokładne typy danych liczbowych

Dokładne dane typów liczbowych to decimal i numeric. Dokładność jest utrzymywana do najmniej znaczącej cyfry. Jeżeli zostaje zadeklarowany dokładny typ danych numerycznych, należy określić dwa składniki precyzję i skalę. Jeżeli nie zostaną one określone, SQL Server używa domyślnych wartości 18 i 0.

Jeżeli jako scale zostanie określona wartość 0, zostaje utworzony odpowiednik danych typu całkowitego ponieważ 0 cyfr pojawi się po przecinku dziesiętnym. Używane skali 0 jest użyteczne ponieważ można mieć bardzo wiele dużych, bardzo szczegółowych wartości liczbowych, które są większe niż +-2 biliony. Własność IDENTITY, która automatycznie generuje nową wartość po dodaniu rekordu, wymaga danych typu całkowitego (integer). Jeżeli planuje się więcej niż 2 biliony rekordów, można skorzystać z typu danych exact ze skalą 0.

Jeżeli kolumna jest zadeklarowana jako decimal(7,2), może przechowywać liczby 1000.55 i 11000.55, ale nie może przechowywać liczb 11110000.55 ani 1100.5678. Przy próbie wpisania liczby większej od dozwolonej przez precyzję i skalę, liczba jest obcinana. Wydruk 9.2 pokazuje przykłady używania danych typu dokładnego.

Wydruk 9.2 Używanie liczbowych typów danych

CREATE TABLE tblGold

(

decAtomicWeight decimal(8,4)

numMolePerOunce numeric(12,6)

numDensity numeric(5,4)

)

Autor stosuje prefiksy dec i num dla danych typu decimal i numeric.

--> Generalnie[Author:AK] należy unikać używania danych typu float i real. Jeżeli możliwe jest przechowywanie potrzebnych informacji w typach danych decimal i numeric, należy je stosować. Kalkulacje przy użyciu float i real mogą prowadzić do interesujących ale często wprowadzających w błąd wyników.

Specjalne typy danych

Kilka typów danych nie pasuje dobrze do żadnej z kategorii. Zostały tutaj dodane w sekcji „Specjalne typy danych”. Zostaną omówione typy danych: bit, dwa typy BLOB (binary large object), text, image oraz RowGUID (często wymawiany jako ROW-goo-id).

bit

Typ danych bit jest logicznym typem danych używanym do przechowywania informacji Boolean. Typ danych Boolean jest używany jako flaga aby oznaczyć zdarzenia typu włączone/wyłączone, prawda/fałsz lub tak/nie. Wartości przechowywane przez ten typ to 0 i 1.

Kolumny typu bit mogą mieć wartość NULL (nieznaną) ale nie mogą być indeksowane. Typy danych bit wymagają pojedynczego bajtu pamięci na przechowywanie.

Poniżej przedstawiono przykłady wykorzystania typu danych bit:

fGender bit NOT NULL

flgPaid bit NULL

boolPrinted bit NOT NULL

Autor stosuje f (flag) jako prefiks danych typu Boolean. Można również spotkać prefiksy flg lub bool.

text i image

Można użyć typów danych text i image jeżeli wymagania przechowywania przekroczą 8000 znaków — co jest limitem kolumny. Często odwołuje się do tych typów danych jako BLOB. Typy danych text i image mogą przechowywać do 2 GB danych binarnych lub tekstowych na deklarację.

Jeżeli zostają zadeklarowane dane typu text lub image, do wiersza dodawany jest 16-bajtowy wskaźnik. Ten 16-bajtowy wskaźnik wskazuje na osobne 8KB strony danych, gdzie są przechowywane dodatkowe informacje na temat danych. Jeżeli dane przekroczą 8KB nas stronę danych, tworzone są wskaźniki do dodatkowych stron danych BLOB.

Przechowywanie i pobieranie danych typu text i image może ograniczać wydajność bazy danych, ponieważ duże ilości danych są wpisywane do dzienników transakcji podczas dodawania danych, modyfikacji i usuwania. Można rozwiązać ten problem używając polecenia WRITETEXT, ponieważ stosuje ono zmiany danych bez tworzenia odpowiedniego wpisu w dzienniku transakcji.

W przypadku korzystania z nie rejestrowanych operacji, należy natychmiast utworzyć kopię bezpieczeństwa bazy danych ponieważ możliwość odtworzenia bazy danych jest bardzo istotną sprawą (jak zostało omówione w rozdziale 7).

Jak alternatywa do wymagań dużej ilości przestrzeni pamięci dla danych typu text i image można przechowywać te dane jako osobne pliki a następnie przechowywać w bazie danych ścieżkę do tych plików.

Przykładowe deklaracje danych typu text i image:

imgEmployeePhoto image

imgScannedContracts image

strDescription text

strComments text

Autor stosuje odpowiednio prefiksy img i str (string) dla danych typu text i image. Można również stosować prefiks txt dla danych tekstowych; jednak jest to popularny prefiks używany w wielu aplikacjach do reprezentowania pola tekstowego.

RowGUID

Jeżeli korzysta się z replikacji łączonej, omówionej w rozdziale 16 oraz rozdziale 17, każda kolumna w replikowanych tabelach musi mieć unikalny identyfikator. Można tego dokonać przez utworzenie kolumny w każdej replikowanej tabeli jako kolumny typu uniqueidentifier. Ten typ danych uniqueidentifier ma własność zwaną ROWGUIDCOL. Kiedy własność ta jest włączona, globalnie unikalny identyfikator (GUID) może być przypisany do kolumny. W ten sposób kolumny w jednej wersji replikowanej tabeli mogą mieć taki sam identyfikator GUID jak w innej wersji tej samej tabeli. Jeżeli zostanie wykonana zmiana w wierszu w replikowanej tabeli z ROWGUIDCOL, ROWGUIDCOL jest modyfikowany przez SQL Server. W ten sposób replikowane wiersze z dwóch baz danych mogą być śledzone odrębnie.

Można zainicjalizować GUID na dwa sposoby:

• Używając funkcji NEWID.

• Dokonując konwersji stałej typu string na liczbę heksadecymalną w formie xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx. Warto rozważyć przykład poprawnego RowGUID: 8FE17A24-B1AA-23DA-C790-2749A3E09AA2.

Można używać następujących operatorów porównania z danymi typu uniqueidentifier: =, <>, IS NULL oraz IS NOT NULL.

sql_variant

Typ danych sql_variant jest podobny do typu danych Visual Basic variant. Typ sql_variant pozwala na przechowywanie w nim prawie każdego innego podstawowego typu danych SQL Server. Wyjątki stanowią ntext, timestamp i sql_variant.

Można używać tych typów danych w kolumnie, jako zmiennych w poleceniu DECLARE, jako parametrów oraz jako wyników funkcji zdefiniowanych przez użytkownika. (Funkcje zdefiniowane przez użytkownika zostaną omówione w rozdziale 15.

Niektóre specjalne reguły mają zastosowanie gdy porównuje się dane typu sql_variant. Każdy typ danych w SQL Serverze jest umieszczony w rodzinie danego typu danych, jak pokazano w tabeli 9.6.

Tabela 9.6. Rodziny typów danych

Typ danych	Rodzina
sql_variant	sql_variant
datetime, smalldatetime	datetime
float, real	liczbowy przybliżony
bigint, bit, decimal, int, money, smallint, smallmoney, tinyint	liczbowy dokładny
chat, nchar, nvarchat, varchar	Unicode
binary, varbinary	binarny
uniqueidentifier	identyfikator Unicode

Jedna specjalna reguła stosowana jest do typu danych uniqueidentifier: jeżeli porównuje się dwa typy danych sql_variant z różnych rodzin, rodzina z bazowym typem danych wymieniona wyżej w tabeli 9.6. jest rozważana jako większa wartość. Przykładowo, jeżeli jest przechowywana wartość float i wartość binarna w danej typu sql_variant, po dokonaniu porównania, wartość typu float zawsze zostanie oszacowana jako wyższa niż wartość binarna.

Typ danych sql_variant jest nowy, więc ciężko zdefiniować dokładnie kiedy i gdzie powinien być używany. Wiele reguł i szczegółów związanych z tym typem danych wykracza poza zakres tej książki. Dodatkowe informacje na temat typu danych sql_variant można znaleźć w SQL Server Books Online lub „Microsoft SQL Server 2000 Unleashed” opublikowanej przez Sams Publishing.

table

Typ danych table jest generalnie używany do przechowywania zbioru wyników do późniejszego wykorzystania. Faktyczną zaletą typu danych table jest to, że może być używany jak zwykła tabela lub może być używany jak zmienna lokalna w funkcjach, programach wsadowych i procedurach składowych. Ten nowy typ danych zostanie omówiony bardziej szczegółowo w rozdziale 15.

Typy danych daty i czasu

Dane daty i czasu mogą być przechowywane w typach datetime i smalldatetime. Warto zauważyć, że data i czas są zawsze przechowywane razem, w pojedynczej wartości.

Dane daty i czasu mogą występować w kilku różnych formatach. Można określić miesiąc przy pomocy pełnej nazwy lub jej skrótu. Wielkość liter jest ignorowana, przecinki są opcjonalne.

Warto rozważyć poniższe przykłady używające formatów alpha dla daty — 15 kwiecień 2001 (April 15, 2001):

"Apr 15 2001"

"Apr 15 01"

"Apr 01 15"

"15 Apr 01"

"2001 April 15"

"2001 15 April"

Można również określić dla miesiąca wartość liczby porządkowej. Liczba porządkowa (ordinal value) danego elementu jest wartością danej pozycji na liście elementów. W poprzednim przykładzie kwiecień jest czwartym miesiącem w roku, czyli można skorzystać z cyfry 4 jako oznaczenia. Następujące przykłady używają liczby porządkowej dla daty April 15, 2001:

4/15/01 (mdy)

4-15-01 (mdy)

4.15.01 (mdy)

4/01/15 (myd)

15/01/04 (dym)

01/15/04 (ymd)

Można również użyć kilku różnych formatów czasu. Oto przykłady:

16:30 (4 hrs, 30 mins)

16:30:20:999 (4 hrs, 30 mins, 22 seconds, 999 miliseconds)

4:30PM

Daty przechowywane w typie datetime są przechowywane z dokładnością do milisekund. W tym przypadku używane jest 8 bajtów — 4 dla liczby dni od 1 stycznia 1900 oraz 4 dla ilości sekund po północy. (Daty wcześniejsze niż ten przedział są przechowywane jako liczby ujemne, tworząc zakres dat od 1/1/1753 do 12/31/9999). Dokładność tych dat jest w zakresie 3,33 milisekund.

Nie należy używać typu datetime do przechowywania częściowych dat, czyli jedynie miesiąca, dnia lub roku. Jeżeli jedyną potrzebną daną jest rok, lepsze będzie zastosowanie smallint lub tinyint. Jeżeli dane nie są przechowywane w ten sposób trzeba będzie własnoręcznie przeanalizować datę pod kątem składni, za każdym razem, gdy będzie potrzeba dodania, modyfikacji lub innej operacji związanej z tą informacją.

Typ danych smalldatetime używa 4 bajtów. Daty przechowywane tym sposobem są dokładne co do minuty. Wewnętrznie, jedna wartość smallint (2 bajty) jest używana do określenia ilości dni po 1 stycznia 1900; druga wartość smallint jest używana do określenia ilości sekund po północy. Zakresem smalldatetime jest 1/1/1900 do 6/6/2079.

Należy używać smalldatetime dla bieżących dat w bazie danych, szczególnie tych, które są w rzeczywistości chwilowe. Są to daty, które nie będą używane dłużej niż kilka lat.

Tabela 9.7. opisuje typy danych datetime.

Tabela 9.7. Typy danych daty i czasu

Typ danych	Przechowywanie	Zakres
datetime	8	1/1/1753 - 12/31/9999
smalldatetime	4	1/1/1900-6/6/2079

Autor używa prefiksu dt dla typu datetime i sdt dla typu danych smalldatetime.

Waluta

Dwoma typami danych waluty są money i smallmoney. Obydwa mają skalę równą cztery, co oznacza, że mogą przechowywać cztery cyfry po przecinku dziesiętnym. Te typy danych mogą przechowywać informacje inne niż wartości dolarowe, dla użytku międzynarodowego. Nie są dostępne żadne funkcje konwersji walutowej w SQL Serverze. Kiedy zostaną wprowadzone dane walutowe, należy poprzedzić je znakiem dolara. Tabela 9.8 pokazuje typy danych walutowych, ich wymagania i zakres.

Tabela 9.8. Typy danych walutowych

Typ danych	Przechowywanie	Zakres
money	8	+-922,337,203,685,447.5808
smallmoney	4	+-214,748.3647

Jak widać, smallmoney może przechowywać do 10 cyfr ze skalą 4. Typ money jest wystarczająco duży, aby przetrzymywać zadłużenie społeczne U.S., z wartościami setek trylionów.

Przykładowe deklaracje używające typu walutowego:

curAccountsReceivable money

curAccountPayable smallmoney

Autor używa prefiksu cur dla typu danych walutowego, cur jest skrótem od currency —waluta, który jest odpowiednikiem walutowego typu danych w Visual Basic.

Typ danych automatycznie wzrastający rowversion (timestamp)

Rowversion jest nową nazwą dla danych typu timestamp. Dane typu timestamp są nadal obsługiwane w SQL Serverze i zgodne ze standardem ANSI SQL-99. Dla potrzeb pozostałej części tych rozważań będzie używane słowo kluczowe timestamp zamiast rowversion. Za każdym razem, gdy do tabeli dodawany jest nowy rekord z polem rowversion, dodawane są automatycznie wartości czasowe. Jeżeli wiersz zostanie uaktualniony, rowversion zostaje automatycznie uaktualniony.

Typ danych rowversion tworzy generowaną przez SQL Server, unikalną, automatycznie aktualizowaną wartość. Pomimo, że rowversion jest podobny do typu datetime, ma z nim niewiele wspólnego. Dane typu rowversion są przechowywane jako binary(8) dla kolumn NOT NULL lub varbinary(8) jeżeli kolumna zezwala na wartości nieokreślone (null). Dla danego wiersza może istnieć tylko jedna kolumna timestamp.

Typ danych timestamp nie odzwierciedla czasu systemowego. Jest to stale zwiększana wartość licznikowa.

Można używać danych typu timestamp do śledzenia kolejności w jakiej elementy są dodawane lub modyfikowane w tabeli. Poniżej przedstawiono przykłady typu danych timestamp:

timeLastModified timestamp NOT NULL

timPhoneCall timestamp NOT NULL

Autor używa prefiksu tim dla typu danych timestamp i rowversion.

Praca z ANSI i Unicode

Aby zapewnić zgodność typów SQL Servera ze standardem American National Standards Institute (ANSI), można skorzystać z typów danych ANSI w miejsce typów danych SQL Servera. Tabela 9.9. prezentuje typy danych ANSI oraz ich odpowiedniki w SQL Serverze.

Tabela 9.9 Synonimy SQL Servera

Typ danych ANSI	Typ danych SQL Servera
character	char
character(n)	char(n)
char varing	varchar
character varying(n)	varchar(n)
binary varying	vabinary
dec	decimal
ouble precision	float
float(n) n= 1-7	real
timestamp	rowversion
integer	int

Dane Unicode korzystają z zestawu znaków Unicode UCS-2, który jest wielobajtowym zestawem znaków. Gdy są używane zwykłe znaki ANSI, 1 bajt jest wymagany do przechowywania danego znaku. W rezultacie, ANSI jest zwany "wąskim" zestawem. Unicode jest zwany "szerokim" lub wielobajtowym zestawem znaków. Zestaw UCS-2 Unicode używa dwóch bajtów do reprezentowania pojedynczego znaku. Jest szczególnie użyteczny w pracy z bazami danych, które są reprezentowane przez różne języki. Przykładowo, języki angielski i hiszpański mają wystarczająco niedużo liter, aby zestaw znaków o pojedynczym bajcie na znak mógł łatwo reprezentować litery w alfabecie. Ale na przykład język japoński sprawia więcej trudności. Nawet standardowy język japoński (Kana) zawiera więcej niż 1000 znaków. Standardowy 8-bitowy bajt może reprezentować tylko 256 znaków, podczas gdy 2-bajtowy zestaw Unicode może reprezentować 65536 znaków.

Można używać typów danych nchar, nvarchar, i ntext do reprezentowania informacji Unicode. nchar i varchar mają limit 8000 bajtów lub 4000 znaków. Przykładowo, typ nchar(4000) jest prawidłowy, ale nchar(6000) reprezentuje 12 000 znaków i nie pasuje do pojedynczej strony danych. Typ nchar(6000) musi być albo rozdzielony pomiędzy dwa odrębne typy nchar lub zamieniony na pojedynczy typ ntext. Typ ntext może obsługiwać do 2.14 GB danych.

Typ danych Unicode jest niezwykle przydatną własnością SQL Servera 2000. Jeżeli korzysta się z SQL Servera w krajach nie używających standardu U.S. English, warto przechowywać dane przy pomocy zestawu znaków Unicode. Gdy SQL Server stanie się międzynarodowym rozwiązaniem, Unicode stanie się lepszym mechanizmem przechowywania danych niż standardowy mechanizm Windows NT.

Tworzenie typów danych zdefiniowanych przez użytkownika

Można tworzyć typy danych zdefiniowane przez użytkownika dla specyficznej bazy danych lub można umieszczać je w bazie danych model. Należy pamiętać, że baza danych model jest szablonem do tworzenia nowych baz danych. Utworzone przez użytkownika typy danych będą dostępne we wszystkich kolejnych bazach danych.

Aby utworzyć typ danych zdefiniowany przez użytkownika, należy skorzystać z typu danych dostarczonego przez system. Przykładowo, tworząc typ danych zwany EmployeeID i definiując go, należy określić czy jest to typ znakowy lub całkowity. Nie można określić go na podstawie nieistniejącego typu danych np.: column_id.

Należy najpierw utworzyć własny typ danych a dopiero po tym dodać go do tabeli. Aby utworzyć własny typ danych, należy skorzystać z SQL Server Enterprise Managera lub z systemowej procedury składowej sp_addtype. Więcej szczegółów na temat typu danych zdefiniowanego przez użytkownika zostanie przedstawionych w rozdziale 14.

Aby utworzyć nowy, własny typ danych w SQL Serverze, należy wykorzystać systemową procedurę składową sp_addtype. Przykładowo, aby dodać trzy zdefiniowane przez użytkownika typy danych do bazy danych pubs należy uruchomić następujący skrypt SQL:

EXEC sp_addtype strEmpID, 'char(9)', 'NULL'

EXEC sp_addtype strID, 'varchar(11)', 'NULL'

EXEC sp_addtype strTID, 'varchar(6)', 'NULL'

Po zadeklarowaniu typów zdefiniowanych przez użytkownika, można ich używać w bazie danych tak często, jak zachodzi taka potrzeba. Przykładowo, można uruchomić następujące wyrażenie CREATE TABLE:

CREATE TABLE tblEmployee

(

strEmployeeId strEmpID NOT NULL,

strFname char(15) NOT NULL,

strLname char(20) NOT NULL,

strPensionPlan strID NOT NULL

)

Aby dodać do bazy zdefiniowane przez użytkownika typy danych przy pomocy SQL Server Enterprise Managera, należy postępować zgodnie z procedurą:

1. Otworzyć SQL Server Enterprise Managera.

2. Rozwinąć drzewo i przejść do bazy danych pubs.

3. Przejść do foldera User Defined Data Types.

4. Kliknąć prawym klawiszem ten folder i wybrać New User Defined Data Type.

5. Wpisać nazwę, typ danych, długość i wszelkie reguły lub wartości domyślne, które ma zawierać.

6. Kliknąć OK.

Aby usunąć typ zdefiniowany przez użytkownika, można skorzystać z SQL Server Enterprise Managera lub z procedury systemowej sp_droptype. W przypadku SQL Server Enterprise Managera należy postępować wg kroków:

1. Rozwinąć bazę danych i podświetlić folder User Defined Data Types.

2. W prawym panelu, kliknąć prawym klawiszem typ zdefiniowany przez użytkownika, który ma zostać usunięty i kliknąć Delete.

To wszystko. W przypadku Transact-SQL należy uruchomić systemową procedurę składową sp_droptype:

EXEC sp_droptype strEmpID

Typ danych zdefiniowany przez użytkownika nie może być usunięty jeżeli jest używany w tabeli lub jest związany z regułą lub wartością domyślną..

Kolumny wyliczeniowe

Kolumna wyliczeniowa (computed column) jest wspaniałą własnością SQL Servera 2000. Kolumna wyliczeniowa nie przechowuje wyliczonych danych; przechowuje raczej wyrażenie użyte do policzenia wartości danych. Przykładowo, została utworzono kolumna wyliczeniowa nazwana curTotal w tabeli z wyrażeniem curTotal AS curPrice* intQuantity.

Przechowywane wyrażenia mogą być tworzone z nie wyliczeniowych kolumn w tej samej tabeli, stałych, funkcji, zmiennych i nawet nazw. Kolumna wyliczeniowa automatycznie oblicza potrzebne dane, jeżeli jest wywoływana w warunkach SELECT, WHERE lub ORDER BY zapytania (które zostaną omówione w rozdziale 10.). Można również używać tych kolumn w wyrażeniach regularnych.

W przypadku używania kolumn wyliczeniowych należy przestrzegać następujących reguł:

• Kolumny wywoływane w wyrażeniu związanym z kolumną wyliczeniową muszą pochodzić z tej samej tabeli.

• Wyrażenie związane z kolumną wyliczeniową nie może zawierać zapytań zagnieżdżonych.

• Kolumny wyliczeniowe nie mogą być wykorzystywane jako część kluczy lub indeksów; to ograniczenie obejmuje klucze podstawowe i obce, jak również unikalne indeksy.

• Kolumna wyliczeniowa nie może mieć dołączonego powiązania domyślnego (DEFAULT constraint).

• W stosunku do kolumn wyliczeniowych nie mogą być wykonywanie polecenia INSERT i UPDATE.

Oto przykłady wykorzystania kolumn wyliczeniowych:

CREATE TABLE tblOrder (

lngOrdID int NOT NULL,

curPrice money NOT NULL,

intQty smallint NOT NULL,

curTotal AS curPrice * intQty

)

Ta pierwsza tabela posiada kolumnę zwaną curTotal, która korzysta z pól curPrise* intQty.

CREATE TABLE tblPrintInvoice (

lngInvoiceID int NOT NULL,

dtInvDate datetime NOT NULL,

dtPrintDate AS DateAdd(day,30, InvDate)

)

W tym przykładzie, kolumna wyliczeniowa zwana PrintData używa funkcji DateAdd aby dodać 30 dni do kolumny InvDate.

Jeżeli podane są tylko dwie ostatnie cyfry roku, SQL Server interpretuje wartości mniejsze niż 50 jako 20yy, podczas gdy liczby większe lub równe 50 są interpretowane jako 19yy. Przykładowo, 15 kwiecień 03 będzie interpretowany jako 15 kwiecień 2003 roku.

Tworzenie tabel

Teraz, gdy znane są już wszystkie dostępne typy danych w SQL Serverze 2000, nadszedł czas aby zastosować je w poleceniu CREATE TABLE. Jak większość działań w SQL Serverze, również tworzenie tabel można wykonać na dwa sposoby: przy pomocy SQL Server Enterprise Managera lub przy pomocy skryptów Transact-SQL. Na początek zostanie omówione polecenie CREATE TABLE.

Używanie wyrażenia CREATE TABLE

Wyrażenie CREATE TABLE jest używane do tworzenia nowych tabel w SQL Serverze 2000. Tworzenie tabel może być wykonane poprzez uruchomienie odpowiedniego kodu lub z Enterprise Managera. Najpierw zostanie omówione używanie kodu do stworzenia tabeli a następnie jako inna możliwość — korzystanie z Enterprise Managera. Składnia polecenia CREATE TABLE jest następująca:

CREATE TABLE [database, [owner.] table_name

(

column_name datatype [Identity |constraint|NULL|NOT NULL|Collate]

[...]

)

Znaczenie składni:

• table_name jest nazwą tabeli spełniającą warunki określone dla identyfikatorów. Musi być także unikalna w obrębie bazy danych dla jej właściciela. Oznacza to, że jeśli dwaj użytkownicy mają uprawnienia do tworzenia tabel w bazie, tabele te mogą mieć takie same nazwy ale nadal są rozważane jako unikalne ponieważ nazwa użytkownika tworzy część nazwy tabeli.

• column_name jest nazwą kolumny i musi spełniać reguły identyfikatorów.

• datatype jest typem danych dla kolumny.

• ostatni element jest opcjonalny. Można określić własność Identity, więzy pól i dopuszczalność wartości null (nullability).

• Collate jest również parametrem opcjonalnym. Własność ta pozwala na ominięcie kolejności sortowania stosowanego przez bazę danych dla tej kolumny.

Pokazana tutaj składnia polecenia CREATE TABLE jest znacznie uproszczona w porównaniu z tym co można znaleźć w SQL Server Books Online. Wiele z dodatkowych opcji jest specyficzna dla więzów ANSI, które zostaną szczegółowo omówione w rozdziale 14.

Wydruk 9.3. pokazuje przykładowe polecenie CREATE TABLE. Tworzy tabelę pracowników (Employees) z możliwością przechowywania informacji o nazwiskach, adresach pracowników oraz dacie rozpoczęcia pracy.

Wydruk 9.3. Tworzenie tabeli.

CREATE TABLE tblEmployee(bytEmp_id tinyint IDENTITY NOT NULL,strFname char(15),strLname char(20) NOT NULL,strAddress1 varchar(30),strAddress2 varchar(30),strCity varchar(30),strState char(2),strZipCode char(10),dtStartDate datetime)

Tworzenie tabel przy pomocy SQL Server Enterprise Managera

Przy pomocy SQL Server Enterprise Managera można wizualnie tworzyć tabele. Aby to wykonać, należy postępować wg kroków:

1. Zalogować się do SQL Servera, rozwinąć folder Databases, a następnie rozwinąć odpowiednią bazę danych, z której będzie się korzystać. Następnie należy kliknąć folder Tables.

2. Kliknąć prawym przyciskiem prawy panel i wybrać New Table, jak pokazano na rysunku 9.1.

Rysunek 9.1. W folderze Tables należy wybrać bazę danych, do której ma być dodana tabela.

3. Następnie należy dodać informacje do Column Name, Data Type, Length i Allow Nulls. W lewej połowie ekranu, można ustawić własności kolumny, takie jak opis i domyślna skala wartości. Jeżeli mają być tworzone pola identity, należy ustawić własność Identity w zakładce Column na wartość Yes, a następnie określić wartości increment i seed. Więcej informacji na temat Default i IsRowGUID zostanie przedstawionych w rozdziale 14. Jak widać na rysunku 9.2, zostało dodanych klika pól do tabeli tblEmployees.

4. Po zakończeniu, należy kliknąć ikonę Save aby zapisać tabelę i nadać jej nazwę, następnie należy zamknąć okno.

Usuwanie tabel

Można usunąć tabelę z SQL Server Enterprise Managera klikając prawym klawiszem myszy tabelę i wybierając opcję Delete z menu kontekstowego. Aby usunąć tabelę przy pomocy Transact-SQL należy uruchomić polecenie DROP TABLE. Przykładowo, aby usunąć tabelę tblEmployee należy uruchomić polecenie:

DROP TABLE tblEmployee

Rysunek 9.2. Określenie pól i ich własności z wykorzystaniem siatki graficznej (bez używania kodu).

Tabele systemowe nie mogą zostać usunięte.

Aby usunąć tabele przy pomocy SQL Server Enterprise Managera, należy postępować wg kroków:

1. Otworzyć folder Tables i kliknąć prawym klawiszem myszy tabelę, która ma zostać usunięta.

2. Wybrać opcję Delete z menu kontekstowego. Ukaże się okno dialogowe podobne do pokazanego na rysunku 9.3.

Rysunek 9.3. Można usunąć tabele wymienione w tym oknie lub sprawdzić występujące zależności.

3. Aby zobaczyć zależności dotyczące tej tabeli, należy kliknąć przycisk Show Dependencies. Jeżeli tabela nie jest już w relacji klucza podstawowego/obcego, można kliknąć przycisk Drop All aby usunąć tabelę. (Relacje zostały opisane szczegółowo w rozdziale 14).

2 Część I ♦ Podstawy obsługi systemu WhizBang (Nagłówek strony)

1 E:\Praca\Ksiaki\SQL\r09-1.doc

Początek wskazówki

Koniec wskazówki

Nowa wskazówka

---