Typy liczbowe w SQL Server

Artykuł ten jest częścią serii Od 0 do TSQL którą znajdziesz tutaj.

W poprzednim poście omówiliśmy typy tekstowe wbudowane w SQL Server. Oczywiście to nie jedyna grupa typów omawianym przez nas środowisku bazodanowym – dziś omówimy typy przechowujące wartości liczbowe. Jest ich kilka i każdy z nich posiada odmienne zastosowania. Poniżej prezentujemy listę typów liczbowych, które będziemy kolejno omawiać:

• int
• tinyint
• smallint
• bigint
• bit
• decimal
• numeric
• money
• smallmoney
• real
• float

Pierwszym grupą typów liczbowych są typy całkowitoliczbowe. Jak sama nazwa wskazuje są to typy jakie możemy nadawać obiektom takim jak np. kolumny czy też zmienne, które w zamierzeniu przechowywać wartości całkowitoliczbowe. W SQL Server mamy do dyspozycji 4 tego rodzaju typy, a mianowicie INT, TINYINT, SMALLINT, BIGINT różniące się przede wszystkim rozmiarem, a co za tym idzie zakresem wartości jakie mogą przechowywać. Podstawową jednostką jest typ INT czyli INTEGER, który zajmuje 4 bajty miejsca i pozwala przechowywać wartości całkowite z zakresu od -2,147,483,648 do 2,147,483,647. Jak widać zakres ten jest bardzo szeroki i niekoniecznie jest on najlepszym wyborem dla pól, które posiadają kilka do kilkudziesięciu różnych wartości. Lepiej wtedy użyć typu tinyint, który zajmuje tylko 1 bajt i może pomieścić wartości od 0 do 255. Jeżeli ten zakres nie jest dla nas wystarczający, a INT nadal wydaje się za duży możemy również użyć smallint pozwalający przechowywać wartości od -32,768 do 32,767 zajmując przy tym 2 bajty.  Jak widać mamy całkiem duże możliwości definiowania typów liczbowych, jednak czasem potrzebujemy składować naprawdę gigantyczne wartości, które wychodzą poza zakres int, wtedy oczywiście mamy wyjście awaryjne w postaci typu bigint, który jest od niego 2 razy większy (tj. zajmuje 8 bajtów) i pozwala na przechowywanie wartości od -9,223,372,036,854,775,808 do 9,223,372,036,854,775,807. W tym momencie może pojawić się myśl o tym aby zawsze używać bigint dla kolumn całkowitoliczbowych – jednak tak jak wspominałem w poprzednim poście jest to błąd, który w późniejszym czasie może powodować problemy m.in z wydajnością.

Zobrazujmy to sobie na prostym przykładzie. Na samym początku stwórzmy w bazie tempdb cztery tabele, które posłużą nam do testów.

USE tempdb
GO

CREATE TABLE tiny_integers
(
id_tinyint TINYINT
)

CREATE TABLE small_integers
(
id_smallint SMALLINT
)

CREATE TABLE int_integers
(
id_int INT
)

CREATE TABLE big_integers
(
id_bigint bigINT
)

Jak widać każda z tabel zawiera tylko jedną kolumnę będącą identyfikatorem o określonym typie całkowitoliczbowym. Następnie do każdej z tabel wrzućmy 100 tysięcy liczb o wartości 1.

INSERT INTO 
	tiny_integers(id_tinyint) 
VALUES
(
	1
)
GO 100000

INSERT INTO 
	small_integers(id_smallint) 
VALUES
(
	1
)
GO 100000

INSERT INTO 
	int_integers(id_int) 
VALUES
(
	1
)
GO 100000

INSERT INTO 
	big_integers(id_bigint) 
VALUES
(
	1
)
GO 100000

Następnie sprawdźmy graficzny plan wykonania zapytań agregujących dane z powyższych tabel. Aby włączyć plan należy z menu Management Studio wybrać Query, a następnie Include Actual Execution Plan lub użyć skrótu klawiaturowego Ctrl+M

Następnie wykonajmy poniższe zapytania zaznaczając je wszystkie i klikając F5 – pozwoli nam to na ich całościowe porównanie.

SELECT 
	SUM(id_tinyint)   
FROM
	dbo.tiny_integers

SELECT
	SUM(id_smallint)
FROM
	dbo.small_integers	

SELECT
	SUM(id_int)
FROM
	dbo.int_integers	

SELECT
	SUM(id_bigint)
FROM
	dbo.big_integers	

Gdy zapytanie się wykona możemy podejrzeć plany wykonania, na czerwono zaznaczyłem ile procent całości zajmowało wykonanie każdego z zapytań – widać, iż najdłużej trwało wykonanie największych typów danych tj. INT i BIGINT. Pamiętajmy, że nasze zapytania testujące są bardzo proste, a my już jesteśmy w stanie zauważyć kilkuprocentowe wahania wydajności. Zawsze należy mieć na uwadze fakt, iż odczyt danych z dysku jest zazwyczaj wąskim gardłem naszych zapytań, dlatego też warto zwracać uwagę aby pobierać z dysku jak najmniejsze porcje danych – mamy na to wpływ między innymi poprzez implementację jak najmniejszych typów danych.

Istnieje w ramach SQL Server również typ specjalny bit, który pozwala na przechowywanie tylko dwóch wartości TRUE/FALSE lub ewentualnie wartości niezidentyfikowanej NULL, o której powiemy sobie w dalszej części kursu. Warto go stosować dla kolumn mogących przyjmować tylko dwa stany np. kolumna sprawdzająca czy faktura jest zapłacona może przyjmować tylko dwie wartości i właśnie typ danych bit będzie w takim wypadku najbardziej odpowiedni.

Po wartościach całkowitoliczbowych możemy przejść do wartości zmiennoprzecinkowych. Na samym początku powiemy parę słów o typie DECIMAL oraz NUMERIC. Typ decimal charakteryzuje się dwoma właściwościami, a mianowicie precyzją oraz skalą i właśnie te dwie własności sobie omówimy ze względu na ich kluczowe znaczenie w tym typie.

Precyzja (ang. precision) – jest to maksymalna ilość cyfr w liczbie zmiennoprzecinkowej zarówno przed jak i po przecinku. Maksymalnie typ decimal może mieć 38 liczb.

Skala (ang. scale) – jest to maksymalna ilość cyfr w liczbie zmiennoprzecinkowej po przecinku. Maksymalnie skala może być równa precyzji.

Podajmy kilka przykładów:

• Liczba 18.667 posiada precyzję równą 5 i skalę równą 3
• Liczba 776378 posiada precyzję równą 6 i skalę równą 0
• Liczba 0.12387654321 posiada precyzję równą 11 i skalę równą 11

Typ decimal definiujemy w następujący sposób decimal(p,s) gdzie p to precyzja, a s to skala. W przypadku gdy nie podamy tych wartości to domyślnie zostanie przedstawiona wartość decimal(18,0). Możliwe jest zadeklarowanie obiektu typu decimal bez podawania skali ale z podaniem precyzji – tak więc zapis decimal(20) jest równoznaczny z zapisem decimal(20,0). Nie jest możliwe natomiast wskazanie skali bez podania precyzji – tak więc taki zapis decimal(,2) jest błędny.

Może powstawać teraz pytanie ile bajtów zajmuje typ decimal, odpowiedź nie jest jednoznaczna. Wszystko zależy od wspomnianej precyzji.

Precyzja	Bajty
od 1 do 9	5
od 10 do 19	9
od 20 do 28	13
od 29 do 38	17

Tak więc po wyborze tego typu musimy zastanowić się jakie wartości chcemy tam przechowywać bo w niektórych przypadkach typ ten może być bardzo “ciężki” i zajmować aż 17 bajtów.

Wspominałem również o drugiem typie zmiennoprzecinkowym o nazwie numeric. W tym przypadku jednak nie będziemy musieli spędzić nad nim dużo czasu – dlaczego? bo jest to dokładnie ten sam typ co decimal można je traktować jako synonimy.

Podobnie możemy traktować typy pieniężne tj. money oraz smallmoney.  Money jest typem przystosowanym do przechowywania danych pieniężnych i ma ustaloną precyzję na 19 a skalę na 4 – nie ma możliwości zmienić tych ustawień. Typ ten zajmuje 8 bajtów, a więc jeden bajt mniej niż odpowiadający decimal(19,4) – dzieje się tak dlatego, że money obejmuje wartości od -922,337,203,685,477.5808 do 922,337,203,685,477.5807, a więc mniej od decimal(19,4) można to zobrazować wykonując poniższe zapytania:

DECLARE @moneyVariable MONEY
SET @moneyVariable=922337203685477.5808
PRINT @moneyVariable

w rezultacie otrzymujemy błąd

Msg 8115, Level 16, State 4, Line 2
Arithmetic overflow error converting numeric to data type money.

Natomiast gdy przypiszemy tę samą wartość dla zmiennej typu DECIMAL(19,4)

DECLARE @decimalVariable DECIMAL(19,4)
SET @decimalVariable=922337203685477.5808
PRINT @decimalVariable

Otrzymujemy prawidłową wartość zmiennej

922337203685477.5808

Istnieje również mniejsza forma money nazwana smallmoney – w użyciu niczym się ona nie różni oprócz tego, że zajmuje 4 bajty i może przechowywać wartości od – 214,748.3648 do 214,748.3647.

Kiedy więc stosować typy money, a kiedy decimal? Jeżeli nasza liczba posiada 9 cyfr to na pewno decimal będzie zajmował mniej miejsca, natomiast dla liczb z większą ilością cyfr i precyzją do 4 miejsc po przecinku money będzie odpowiedniejszy (chyba, że potrzebujemy liczb spoza zakresu tego typu).

Ostatnimi typami omawianymi w ramach niniejszego artykułu są typy o zmiennej dokładności tj. real oraz float(n) gdzie n  to ilość cyfr służąca do zapisania mantysy czyli znormalizowanej części ułamkowej. Są to typy przybliżone o zmiennej precyzji, a co za tym idzie przechowują wartości w ramach jednej kolumny o różnych precyzjach np.

Wartości 9.12 i 9.123  w float będą przechowane właśnie w takiej formie, natomiast decimal ma stałą precyzję i będzie przechowywało te wartości jako 9.120 i 9.123.

Rozmiar typu zależy od n jeżeli należy on do przedziału od 1 do 24 to zajmuje 4 bajty, jeżeli natomiast 25-53 to 8 bajtów.

REAL jest synonimem typu float(24). Typy te powinniśmy stosować tylko wtedy gdy chcemy przechowywać wartości o zmiennej precyzji i tylko gdy jest to dla nas istotne. Powinniśmy tak robić ze względu na to, iż typy o zmiennej precyzji zgodnie ze swoją naturą są ciężej porównywalne tak więc ich użycie w klauzuli WHERE wraz z operatorem równości bądź nierówności może spowodować degradację wydajności.

To by było na tyle jeśli chodzi o typy liczbowe wbudowane w SQL Server. W następnym artykule powiemy sobie o typach daty i czasu.Zapraszam!