dotNETportal.cz

V předchozím dílu jsem vysvětloval, že jedním z hlavních nastavení sloupců je jejich datový typ. Určuje formát uložených dat. Pochybuji, že by mělo smysl popisovat význam datových typů jako takových. Obecně se snažíme vybírat typ, jenž zabere v databázi nejméně místa a zároveň splní naše požadavky. Tedy je například očividně jasným plýtváním pro uložení čísla využít textový formát. Na druhou stranu je hloupost uvažovat o co nejúspornějším typu na úkor rizika možného překročení rozsahu v budoucnu. Nám to pak leda přidělá dost práce jej měnit na všech místech v databázi a aplikaci. A to naprosto zbytečně. Vždy bych tedy volil typ, se kterým neplýtváme místem, ani si nesvazujeme ruce do budoucna.

Jak rozdělujeme datové typy?

Systémové datové typy dodávané a podporované SQL Serverem je možné si prohlédnout v každé databázi v okně Object Explorer z Microsoft SQL Server Management Studia. Rozbalte si větev Databases \ “Libovolná databáze” \ Programmability \ Types \ System Data Types. Zde máte několik kategorií datových typů. Pod každým z nich pak nalezneme konkrétní seznam typů. My si je probereme postupně podle kategorií.

Legenda:

• jsou datové typy dostupné až od verze SQL Server 2008 (verze 10)

Datové typy podle kategorií

Exact numerics - přesné číselné typy

Typy jasně definující rozsah a přesnost. Vhodné pro operace s čísly, kde je ztráta přesnosti nepřípustná (například finanční operace) a zároveň operace, kde postačí celočíselná hodnota.

1. BIT - 1 bit (rozsah 0-1)
2. TINYINT - 1 byte (rozsah 0-255)
3. SMALLINT - 2 byty (rozsah -32,768 až 32,767)
4. INT - 4 byty (rozsah -2^31 až 2^31-1)
5. BIGINT - 8 bytů (rozsah -2^63 až 2^63-1)
6. DECIMAL(p,s) - číslo s desetinnou čárkou (p je maximum cifer celkem a s je maximální počet desetiných mít, takže 0<=s<=p, rozsah p je 1 až 38). Zabírá celkem 5 až 17 bytů podle rozsahu. Pokud neuvedeme rozsah, jedná se o ekvivalent pro DECIMAL(18,0) – tedy žádná desetinná místa.
7. NUMERIC(p,s) – ekvivalent pro DECIMAL.
8. SMALLMONEY - také desetinné číslo na desetitisíciny - 4 byty (- 214,748.3648 to 214,748.3647).
9. MONEY - desetinné číslo na desetitisíciny - 8 bytů (-922,337,203,685,477.5808 to 922,337,203,685,477.5807).

Approximate numerics - přibližné číselné typy

Přibližné datové typy mají obrovský rozsah na úkor přesnosti. Čím větší číslo bude, tím více se plovoucí desetinná čárka posunuje a tím větší šance je ztráty přesnosti na nižších řádech čísla. Matematické operace často a rády končí nepřesnostmi typu čísel 199999.99875 a podobně. Proto využijeme typ právě pro operace s tolerancí k výpočtu.

1. FLOAT(n) - číslo s plovoucí desetinnou čárkou, kde n (1-53) určuje počet bitů - přesnost čísla je přibližná a přímo ovlivněná celkovým počtem cifer - celkem vždy zabírá 4 nebo 8 bytů
2. REAL - je to samé jako FLOAT(24)

Date and time - datum a čas

Tady nebude moc co vysvětlovat. Datové typy pro uchování informace o času nebo datu. Microsoft vyslyšel přání vývojářů a přidal ve verzi SQL 2008 nové typy pro uchování data a času.

1. SMALLDATETIME – 4 byty a rozsah od 1900-01-01 do 2079-06-06 (přesnost 1 minuta) – pro kompaktní uchování data a času blízké minulosti
2. DATETIME – 8 bytů a rozsah od 1753-01-01 do 9999-12-31 (přesnost 3.33 milisekund) – pro uchování běžného data a času (bohužel až od roku 1753)
3. DATE – 3 byty a rozsah 0001-01-01 až 9999-12-31 (přesnost 1 den) – pro uchování pouze data (výhoda plného rozsahu roků od 0001 až 9999)
4. TIME(n) - 3 až 5 bytů podle uvedené přesnosti – uchovává pouze čas, rozsah 00:00:00 0000000 až 23:59:59 9999999. Přesnost n je v rozsahu 0 až 7 – uvádí počet desetinných míst vteřin. Pokud n neuvedeme, bude přesnost maximální – tedy 7 desetinných míst. Pokud n uvedeme jako 0, bude mí datový typ nejnižší přesnost - 1 vteřinu.
5. DATETIME2(n)  - 6 až 8 bytů podle uvedené přesnosti – nastavitelný a přesnější nástupce typ DATETIME. Uchovává datum a čas v rozsahu 0001-01-01 00:00:00.0000000 až 9999-12-31 23:59:59.9999999. Nastavování přesnosti je naprosto identické jako u času TIME – tedy určuje počet desetinných míst vteřin (rozsah 0 až 7, při neuvedeném n je 7).
6. DATETIMEOFFSET(n)  - 8 až 10 bytů podle uvedené přesnosti. Vychází z datového typu DATETIME2. Je rozšířen o uchování časového pásma (-14 hodin až +14 hodin). Přesnost ovlivňuje naprosto identicky (jako u TIME a DATETIME2) desetinná místa vteřin.

Character strings - běžné textové řetězce (1 byte na znak)

Typy textových hodnot. Každý znak má v paměti přesně 1 byte. Nedoporučuje se používat na kterékoliv texty, co mohou obsahovat nestandardní znaky.

1. CHAR(n) - řetězec s pevnou délkou n znaků (1 až 8000) - nepoužité znaky budou mít nulovou hodnotu
2. VARCHAR(n) - řetězec s variabilní délkou, kde n udává maximální délku (opět 1 až 8000) - pokud se místo n použije klíčové slovo MAX - tedy VARCHAR(MAX) - maximální délka je v tomto případě 2^31-1 bytů.
3. TEXT – maximální délka textu - místo tohoto datového typu se doporučuje používat ekvivalent VARCHAR(MAX)

Unicode character strings - unicode textové řetězce (2 byty na znak)

Typy pro ukládání řetězců s kódováním UNICODE (1 znak = 2 byty) – ideální pro všechen text, obsahující diakritiku, či jiné nestandardní znaky

1. NCHAR(n) - viz CHAR(n), jen zabírá 2x víc místa v paměti (unicode kódovaní) a dokáže tak lépe zpracovat například diakritiku nebo obecně nestandardní znaky – kvůli dvojnásobku zabraného místa je rozsah n zmenšen na 1-4000.
2. NVARCHAR(n) - viz VARCHAR(n), jen zabírá 2x víc místa v paměti (unicode kódovaní) a dokáže tak lépe zpracovat například diakritiku nebo obecně nestandardní znaky – kvůli dvojnásobku zabraného místa je rozsah n zmenšen na 1-4000.
3. NTEXT- maximální délka textu - místo tohoto datového typu se doporučuje používat ekvivalent NVARCHAR(MAX)

Poznámka: Všimněte si počátečního písmene N. To vyjadřuje zkratku slova national, což je označení právě textů s možným výskytem nestandardních znaků podle norem ISO pro SQL.

Binary strings - binární formáty

Typy pro ukládání binárních informací. Řešení pro ukládání souborů, či serializovaných objektů.

1. BINARY(n) - datový typ s pevnou délkou n bytů (rozsah 1 až 8000) pro ukládání binárních dat (souborů, obrázků, atp.)
2. VARBINARY(n) - datový typ s variabilní délkou, kde n udává maximální kapacitu v bytech (rozsah opět 1 až 8000) - stejně jako u VARCHAR lze použít klíčové slovo MAX, které zaručí nastavení maximálního počtu bytů na 2^31-1
3. IMAGE - místo tohoto datového typu se doporučuje používat VARBINARY(MAX)

CLR Data Types – datové typy importované z knihoven .NET

CLR (Common Language Runtime) je způsob jak využívat knihoven .NETu přímo v SQL Serveru. Tato možnost je tu již od SQL Server 2005, ale až ve verzi 2008 přišel Microsoft se svým prvním oficiálním typem zaintegrovaným přímo do SQL Serveru – hierarchyid.

1. HIERARCHYID – uchovává hierarchickou adresu v binární podobě (například /1/14/67.3/24/). Výhodou je možnost binárního třídění a optimalizací prohledávání. Tento typ elegantním způsobem řeší problémy se zápisem hierarchické struktury v databázích a rozhodně si proto zaslouží vlastní článek.

Spatial Data Types – prostorové datové typy

Slouží pro uchování geometrických a geografických objektů (poloh, čar, polygonů). Jsou novinkou v SQL Serveru 2008.

1. GEOMETRY - dokáže uchovávat pozice a geometrické obrazce na rovném souřadnicovém systému
2. GEOGRAPHY  - slouží (podobně jako GEOMETRY) pro uchovávání pozice a geometrických obrazců – tentokrát však na souřadnicovém systému země (mínění, že země je placka snad už pominulo) - navigace v rámci zakřivení povrchu není úplně banální a převody z různých souřadnicových systémů mohou být příjemným usnadněním při práci s mapou

Other data types - ostatní datové typy

Aneb co se jinam nevešlo. Jsou to typy, jejichž využití je tak specifické, že je nelze kategorizovat. Rozhodně se ale nejedná o zbytečné typy.

1. CURSOR – reprezentuje dotaz, jehož výsledky můžeme postupně číst přímo v T-SQL jazyce - budu popisovat samostatnou kapitolou, nelze využít jako datový typ sloupce tabulky
2. TABLE - slouží k dočasnému uložení datové tabulky (například výstup funkcí a předávání tabulek do procedur) - opět nelze použít jako typ sloupce
3. SQL_VARIANT - dokáže obsahovat různé datové typy (BIT, INT, DECIMAL, CHAR, BINARY, NCHAR, UNIQUEIDENTIFIER…) - použitý formát se určuje automaticky podle vstupu. Nedokáže obsahovat velké objekty (typy s označením velikostí MAX), CLR datové typy a uživatelské typy. SQL_VARIANT nedoporučuji používat, pokud máte možnost znát typ dat.
4. TIMESTAMP - název mate, protože tento 8mi bytový binární typ datum ani čas neukládá - sloupec typu TIMESTAMP může být v tabulce jen jeden a automaticky se inkrementuje při každém vložení nebo změně řádku - slouží tím pádem pro verzovací účely
5. UNIQUEIDENTIFIER - jedná se o 16ti bytový unikátní identifikátor ve tvaru xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, kde každý znak je hexadecimální znak (0-9 nebo A-F) – přes jeho název se může se v rámci řádků tabulky opakovat, pokud unikátnost nevynutíte nějakým omezením – tento lze vygenerovat náhodně funkcí NEWID()
6. XML - tento datový typ dokáže ukládat XML struktury a to buď volně nebo podle schématu - dále je možné uložit buď celý XML dokument i s hlavičkou nebo jen jeho část (větev)

Vysvětlení hodnoty MAX u řetězcových a binárních typů

V popisu typů pro uchování řetězců a binárních hodnot jsem uvedl, že v závorce určujeme buď velikost číslem (1-8000 bytů, resp 1-4000 znaků u typů s unicode kódování) nebo slovem MAX, které reprezentuje maximální délku 2^31-1 bytů. Můžete se ptát, proč je tedy určení velikosti omezeno na 8000 bytů, když přitom maximální délka se slovem MAX je v přepočtu 2147483647 bytů.

Důvodem je fyzické ukládání dat. Pokud budete vědět, že text nebo binární pole má menší počet bytů (1 až 8000), uvedeme jeho velikost a SQL Server se dokáže lépe vypořádat s jeho uložením, jelikož se vejde do interně používaných 8kB stránek. Dokáže s maximální velikostí plánovat a vnitřně lépe optimalizovat příslušné operace. Oproti tomu větší bloky (8000 bytů +) již SQL Server zpracovává neoptimalizovaným způsobem a je mu v zásadě již jedno, jak veliká data to doopravdy mohou být.

Závěr

Volba správného datového typu a popřípadě jeho velikosti je základ dobře navržené databáze. Není to ani nic složitého, jen se musí člověk trochu zamyslet. Často se totiž setkávám s problémem, že programátoři znají jen pár datových typů a navíc je používají špatně. Nikdo neříká, že se musíte naučit velikosti jednotlivých typů v bytech (nejsme přece ve škole, abychom cpali do hlavy věci násilím). Pouze stačí, když budete mít třeba tento přehled někde po ruce a on se vám pomalu dostane do krve při používání.