Typy i konwersje danych

Typy i konwersje danych

Autor: Krzysztof Kapustka

Opublikowano: 4/12/2017, 12:00 AM

Liczba odsłon: 16014

W systemie SQL Server typ danych określa rodzaj informacji, jakie mogą być przechowywane w kolumnie tabeli, zmiennej, wyrażeniu czy parametrze, co z kolei pośrednio wyznaczać będzie dopuszczalne operacje, jakie mogą być na tych danych wykonywane. Ilekroć zechcemy zaprojektować nową tabelę do przechowywania nowych danych, dla każdej kolumny tej tabeli będziemy musieli jawnie określić rodzaj gromadzonych w niej informacji. Bez jawnego podawania typów dla przechowywanych wartości system bazy danych nie tylko nie byłby w stanie osiągnąć odpowiedniego poziomu wydajności działania, ale przede wszystkim nie mógłby realizować podstawowych założeń z zakresu integralności danych, takich jak sprawdzanie spójności i poprawności wprowadzonych informacji.

Typy danych języka SQL są niemal całkowicie uzależnione od wykorzystywanej platformy, co oznacza, że typy dostępne w ramach jednego systemu zarządzania bazą danych często różnią się od typów danych oferowanych przez podobny system innego producenta. Pomiędzy poszczególnymi platformami będą zazwyczaj występować także różnice na poziomie funkcji numerycznych, które operują na danych typu numerycznego. Czasem różnice te będą małe i pomijalne, a czasem duże i wymagające dogłębnego zapoznania się z dokumentacją techniczną, w celu zrozumienia sposobu działania takiej funkcji. Choć spora część omówionych tu typów danych należy do standardu SQL i dostępna jest w niezmienionej lub zbliżonej postaci w innych systemach RDBMS, będziemy tu zakładać, że typy te dostępne są wyłącznie w systemie SQL Server i w taki sposób będziemy je przedstawiać.

Podział typów danych

SQL Server oferuje nam nie tylko szereg typów wbudowanych, dla których możemy tworzyć swoje własne aliasy niestandardowe uwzględniające określone ograniczenia, ale również umożliwia nam tworzenie naszych własnych typów danych, definiowanych w ramach kodu zarządzanego środowiska Common Language Runtime (CLR).

Wśród udostępnionych nam typów wbudowanych, których to łącznie mamy do dyspozycji ponad 30, znajdują się dwie kategorie typów numerycznych, typy do przechowywania daty i godziny, dwie rodziny typów znakowych, typy binarne oraz kilka typów dodatkowych, nazywanych niekiedy typami specjalnymi. Myśląc o poszczególnych typach danych w systemie SQL Server mamy zazwyczaj na myśli wbudowane typy danych, które wbudowane, rozpoznawane i obsługiwane są bezpośrednio przez silnik bazy danych. Do typów tych zaliczamy takie typy, jak numeryczne typy całkowite i rzeczywiste, typy tekstowe i znakowe, czy typy danych do przechowywania daty i godziny.

Typy numeryczne

W większości systemów zarządzania bazą danych, w tym również w systemie SQL Server, istnieją dwa podstawowe rodzaje typów danych numerycznych: całkowite oraz rzeczywiste, które umożliwiają odpowiednio przechowywanie liczb należących do zbiorów liczb całkowitego oraz rzeczywistego. Wszystkie dostępne typy całkowite należą do tzw. precyzyjnych typów numerycznych (ang. exact numerics), czyli typów zdolnych do przechowywania dokładnych wartości liczbowych, pozbawionych jakichkolwiek przybliżeń czy zaokrągleń. W systemie SQL Server liczby całkowite możemy przechowywać w ramach następujących typów danych:

  • TINYINT - 8-bitowy (1-bajtowy) typ wartości całkowitych z przedziału od 0 do 255.
  • SMALLINT - 16-bitowy (2-bajtowy) typ wartości całkowitych z przedziału od -32 768 do 32 767.
  • INT - 32-bitowy (4-bajtowy) typ wartości całkowitych z przedziału od -2 147 483 658 do -2 147 483 657
  • BIGINT - 64-bitowy (8-bajtowy) typ wartości całkowitych z przedziału od -263 do 263-1

W przeciwieństwie do typów całkowitych, typy danych przeznaczone do przechowywania liczb rzeczywistych podzielone są na dwie odrębne kategorie: typy precyzyjne (ang. exact numerics) oraz typy przybliżone (ang. approximate numerics). Do precyzyjnych typów zmiennoprzecinkowych zaliczamy typy:

  • DECIMAL(precyzja, skala) - typ wartości o zadanej precyzji (całkowitej liczbie cyfr) oraz zadanej skali (liczbie cyfr po przecinku)
  • NUMERIC - typ tożsamy z typem decimal
  • SMALLMONEY - 32-bitowy typ wartości zmiennoprzecinkowych o stałej skali (od -214 748,3648 do 214 748,3647)
  • MONEY - 64-bitowy typ wartości zmiennoprzecinkowych o stałej skali (od -922 337 203 685 477,5808 do 922 337 203 685 477,5807)

Należy tu zaznaczyć, że typy smallmoney oraz money dostępne są wyłącznie w systemie SQL Server i nie mają one swoich bezpośrednich odpowiedników na pozostałych platformach. Z uwagi na ten brak zgodności ich stosowanie nie jest więc zalecane.

Z kolei przybliżone typy wartości zmiennoprzecinkowych są następujące:

  • FLOAT(n) - typ danych dla przybliżonych wartości zmiennoprzecinkowych o mantysie n (z przedziału od 1 do 53). Jeśli liczba ta zawiera się w przedziale 1-24, wówczas dane przechwywane są na 4 bajtach (precyzja 7 cyfr). W pozostałych przypadkach dane przechowywane są na 8 bajtach (precyzja 15 cyfr).
  • REAL - 4-bajtowy typ danych dla wartości zmiennoprzecinkowych o przybliżonej wartości (stanowi połowę typu float).
Typy przybliżone przechowują wartości z wykorzystaniem notacji naukowej, z podziałem na cechę i mantysę. Z uwagi na charakter powyższych typów przybliżonych i możliwe zaokrąglenia przechowywanych wartości nie należy ich stosować w obliczeniach finansowych.

Typy znakowe

  • CHAR - typ danych do przechowywania łańcuchów znakowych o stałej długości (ascii, non-unicode, maksymalnie 8000 znaków)
  • VARCHAR - typ danych do przechowywania łańcuchów znakowych o zmiennej długości (ascii, non-unicode, maksymalnie 8000 znaków lub max wynoszące 2 GB danych, domyślnie 1)
  • VARCHAR(max) - typ danych do przechowywania olbrzymich łańcuchów znakowych o zmiennej długości (ascii, non-unicode, maksymalnie 2 miliardy znaków)
  • TEXT - typ danych do przechowywania zmiennej liczby znaków jednobajtowych (ascii, non-unicode, typ przestarzały i powoli wypierany przez typy nvarchar(max), varchar(max) oraz varbinary(max))

Każdy z powyższych typów ma swój odpowiednik w rozszerzonym systemie Unicode, umożliwiający przechowywanie dwubajtowych wartości znakowych Unicode (co w porównaniu z typami znakowymi operującymi na jednobajtowych znakach ASCII wiąże się z dwukrotnie mniejszą maksymalną liczbą znaków). Za pomocą znakó Unicode możliwa jest reprezentacja wielu znaków niestandardowych, takich jak znaki alfabetu japońskiego czy chińskiego. Nazwy typów znakowych dla systemu kodowania Unicode poprzedzone są literą n i prezentują się następująco: nchar, nvarchar, nvarchar(max) oraz ntext (typ przestarzały).

Typy daty i godziny

  • DATE - 3-bajtowy typ danych do przechowywania daty w formacie DD-MM-RRRR (od 1 stycznia 1 do 31 grudnia 9999)
  • DATETIME - 8-bajtowy typ daty i godziny w systemie 24-godzinnym (wartości od 1 stycznia 1753 do 31 grudnia 9999)
  • DATETIME2 - rozszerzenie typu datetime o możliwości typu time
  • DATETIMEOFFSET - typ daty i godziny uwzględniający różnice na poziomie stref czasowych, od -14:00 do +14:00
  • SMALLDATETIME - 4-bajtowy typ datetime bez ułamków sekund (wartości od 1 stycznia 1900 do 6 czerwca 2079)
  • TIME - czas oparty na zegarze 24-godzinnym (od 00:00:00.0000000 do 23:59:59.9999999) precyzją na poziomie ułamków sekund

Specjalne typy danych

W systemie SQL Server dostępnych jest także wiele specjalnych typów danych, z których każdy przeznaczony jest do przechowywania innego rodzaju informacji. Typy te są następujące:

  • BINARY - Typ BINARY służy do przechowywania wartości binarnych o stałej długości. Dane binarne to dane pozbawione standardowej struktury, takie jak dokumenty, zdjęcia czy pliki multimedialne. Kolumna lub zmienna o wartości BINARY zdolna jest przechować równowartość 2 GB danych. Przy tak dużych rozmiarach danych warto rozważyć wyniesienie danych niestrukturalnych poza bazę danych bezpośrednio na system plików. Będziemy o tym rozmawiać przy okazji omawiania funkcji FILESTREAM. Odpowiednikiem typu BINARY o zmiennej długości jest typ VARBINARY.
  • BIT - Typ danych BIT jest jednobajtowym typem logicznym z możliwymi wartościami 0, 1 lub NULL. Za pomocą typu BIT możemy zareprezentować wartości logiczne Prawda/Fałsz lub Tak/Nie, jak również spełnienie lub niespełnienie określonych warunków. Zastosowań dla tego typu danych jest naprawdę wiele, bo nadaje się on wszędzie tam, gdzie możemy zachować jedną z dwóch możliwych wartości.
  • XML - Typ danych XML umożliwia przechowywanie w kolumnie tabeli lub zmiennej wartości w formacie języka XML. Maksymalny rozmiar takiej wartości wynosi 2 GB.
  • UNIQUEIDENTIFIER - Typ danych UNIQUEIDENTIFIER umożliwia przechowywanie wartości będących 128-bitowymi identyfikatorami GUID. Pojedynczy identyfikator GUID zajmuje więc 16 bajtów (cztery razy więcej niż typ całkowity INT).
  • SQL_VARIANT - Universalny typ danych do przechowywania w jednej kolumnie wartości różnego typu.
  • CURSOR - Typ danych do wykorzystania w zmiennych, który umożliwi nam wykonywanie operacji na zasadzie wiersz po wierszu. Typ CURSOR przechowuje więc referencję do kursora.
  • TABLE - Typ danych do wykorzystania w zmiennych, który umożliwi nam zachowanie w zmiennej określonej tabeli. Typ TABLE przechowuje więc referencję do tabeli.
  • HIERARCHYID - Typ danych umożliwiający przechowywanie określonej pozycji (węzła) w strukturze hierarchicznej.

Typy przestrzenne

  • GEOGRAPHY - Typ danych do przechowywania wartości reprezentujących długość i szerokość geograficzną
  • GEOMETRY - Typ danych do przechowywania wartości reprezentujących określone kształty

Konwersje danych

Z uwagi na obecność w systemie bazy danych wielu rodzajów danych o dość zbliżonej charakterystyce, w przypadku przenoszenia, porównywania lub łączenia ze sobą danych przechowywanych w różnych obiektach może zajść potrzeba dokonania ich konwersji z jednego typu na drugi. Zasady dokonywania konwersji pomiędzy różnymi typami danych są mocno zbliżone do zasad znanych z popularnych języków programowania, takich jak C++ czy C#. Choć dokładne kroki podejmowane przez system bazy danych w celu dokonania takiej konwersji są dosyć rozbudowane i często złożone, to w ogólnym przypadku system będzie dążył do stanu, w którym konwersja nie spowoduje żadnej utraty danych lub spowoduje utratę jedynie najmniej znaczącej ich części. Przykładowo jeśli zachodzi konieczność porównania liczby typu INT z liczbą typu BIGINT, wówczas zamiast obcinać większą liczbę w taki sposób, aby dało się ją zareprezentować typem INT, system skonwertuje liczbę typu INT na większy typ BIGINT, wypełniając ją przy tym wiodącymi zerami, co w żaden sposób nie wpłynie na wartość tej liczby.

W systemie SQL Server istnieją dwa typy konwersji: jawne (ang. explicit) oraz niejawne (ang. implicit).

Konwersje niejawne wykonywane są przez silnik bazy danych automatycznie, bez udziału i wiedzy użytkownika. Przykładowo gdy wartość typu SMALLINT porównywana jest z wartością typu INT, w celu porównania wartość SMALLINT zostanie uprzednio skonwertowana na typ INT.

Konwersje jawne wykonywane są na wyraźne polecenie użytkownika za pośrednictwem wywołanej przez niego funkcji CAST lub CONVERT, np. CAST ( 120 AS VARCHAR(10) ).

Jedna z podstawowych zasad projektowania baz danych głosi, że konwersji należy wystrzegać się w ogóle, przy czym jeśli już jakaś konwersja jest nieunikniona, warto zadbać, aby była ona konwersją jawną, zdefiniowaną przez nas samych.