Datové typy

7. díl - Datové typy

Tomáš Jecha       29. 12. 2009       SQL, Databáze       22075 zobrazení

Je čas se seznámit s dostupnými datovými typy v SQL Serveru. Určitě není na škodu je na jednom místě všechny shrnout, uvést přípustné rozsahy, přesnost, nastavitelné parametry a paměťovou náročnost.

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:

  • SQL 2008 (verze 10) 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 SQL 2008 (verze 10)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) SQL 2008 (verze 10) - 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) SQL 2008 (verze 10) - 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) SQL 2008 (verze 10) - 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 SQL 2008 (verze 10) – 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 SQL 2008 (verze 10) - dokáže uchovávat pozice a geometrické obrazce na rovném souřadnicovém systému
  2. GEOGRAPHY SQL 2008 (verze 10) - 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í.

 

hodnocení článku

0       Hodnotit mohou jen registrované uživatelé.

 

Všechny díly tohoto seriálu

 

Mohlo by vás také zajímat

Začínáme s ASP.NET - díl 5.: Jednoduché diskusní fórum

Po delší odmlce přichází pátý díl oblíbeného seriálu o programování v ASP.NET 2.0. V tomto díle si ukážeme, jak používat komponentu FormView pro přidávání záznamů do databáze. Také si ukážeme nejčastější útoky na webové aplikace a hlavně způsoby, jak jim zabránit. Také si předvedeme, jak validovat a kontrolovat vstupy uživatele jednoduše a spolehlivě.

Automatické generování change scriptů

Pooling v .NET

 

 

Nový příspěvek

 

pěkná práce

pěkná práce tenhle tutoriál. každop. utnout ho v polovině? je to škoda.

nahlásit spamnahlásit spam 0 odpovědětodpovědět

Dalsie clanky

Nove clanky ohladne skladania dotazov pripadne zlozitejsich dotazov uz nebudu?

nahlásit spamnahlásit spam 0 odpovědětodpovědět

Diskuse: Datové typy a identifikace řádků

V SQL databázi použiju Data Type: date

Proč se mi potom při zobrazení dat (GrfidView)zobrazuje vedle data tak čas ve tvaru: 0:00:00

Jak se toho mohu zbavit?

Děkuji, FJ

nahlásit spamnahlásit spam 0 odpovědětodpovědět

Diskuse: Datové typy a identifikace řádků

Bude pokračování tohoto seriálu?

nahlásit spamnahlásit spam 4 / 4 odpovědětodpovědět

Diskuse: Datové typy a identifikace řádků

Seriál je dobrý a užitečný! Kdy bude pokračování?

S pozdravem, Igor.

nahlásit spamnahlásit spam 3 / 3 odpovědětodpovědět

Diskuse: Datové typy a identifikace řádků

Ahoj,

měl bych k tomuto seriálu dvě poznámky. Za prvé bych chtěl pochválit styl jakým jsou články psané. Je to opravdu srozumitelné a pochopitelné i pro "svátečního programátora", a prozatím jsem nenarazil na obdobně dobrý web pro začátečníky.

Ta druhá poznámka už tak pozitivní není. Chápu, že se tomuto webu věnujete ve volném čase, myslím si ale, že rozepsat seriál bez jakéhokoliv smysluplného dotažení nějakého celku dokonce a zase rozepsat další je o ničem. Když jsem se v prosinci díval na články a u prvních dvou jsem vyděl datování cca 2 roky staré, tak jsem se u toho dost pozastavil. Nyní, po pár dílech, kdy nastává další blíže nespecifikovaná časová prodleva, si říkám jestli vůbec celý ten seriál má smysl? Když budu odhadovat čas dokončení seriálu z počátečních článků,tak dříve než by tento seriál měl být dokončen se budou používat úplně jiné programovací jazyky, nebo jejich verze.

Promiň to vyznění té druhé poznámky, ale musel jsem to napsat, protože opravdu mne velice mrzí, že tak dobře udělaný seriál je takto useknutý

jakr

nahlásit spamnahlásit spam 1 / 1 odpovědětodpovědět

Diskuse: Datové typy a identifikace řádků

Ahoj,

prosimtě potřeboval bych poradit, jak vytvořit trigger, který by kontroloval, aby se do určitého sloupce s názvem např. "je_vedouci" mohla vložit je jedna hodnota jejíž datový typ boolean je true, false se mohou vkládat neomezeně.Zkrátka zajistit, aby každá pobočka měla jen jednoho vedoucího.

Snad je dotaz dost jasný.

Děkuji za případnou odpověď,

Martin S.

nahlásit spamnahlásit spam 0 odpovědětodpovědět

Diskuse: Datové typy a identifikace řádků

Jen bych doplnil, resp. upřesnil, že funkce NEWID() unikátní identifikátor (GUID) negeneruje náhodně, jak je v článku psáno. Pro vytvoření guidu se používá aktuální čas a MAC adresa síťové karty, což by mělo zaručit jeho unikátnost. Je na to přesně definovaný postup vytvořený právě za účelem, aby se guidy daly používat a nikdy se nevygenerovaly dva stejné. Jestli je ta unikátnost 100% zaručena nevím, spíš bych tipnul, že existuje určitá jen velmi malá pravděpodobnost, že dva počítače vygenerují stejný guid.

nahlásit spamnahlásit spam 1 / 1 odpovědětodpovědět

S tím souhlasím jen na půl. Sice uvnitř může existovat nějaký postup proti generování duplicitních GUIDů, ale to nemění nic na tom, že pro uživatele to má být ve výsledku náhodný GUID.

Sice jsem taky někdě slyšel, že je generování řízené nějakým postupem, ale nevím, co je na tom pravdy. V oficiální dokumentaci jsem nic o konkrétním fungování NEWID() nenašel. Máš nějaký zdroj?

A nepleteš si to se sekvenčním guidem (ten je generován systémem podle času a dalších systémových funkcí)?

nahlásit spamnahlásit spam 3 / 3 odpovědětodpovědět

Tak pravdu máme částečně oba - GUID má víc různých verzí - v jedné (starší) se používala opravdu MAC adresa a systémový čas, v novější verzi se místo času používá opravdu náhodná hodnota. http://en.wikipedia.org/wiki/Globally_Un...

Sekvenční GUID je ještě něco jiného.

NEWID ale používá tu novější verzi, která je opravdu náhodná, takže v tom případě se omlouvám za mystifikaci.

nahlásit spamnahlásit spam 1 / 1 odpovědětodpovědět
                       
Nadpis:
Antispam: Komu se občas házejí perly?
Příspěvek bude publikován pod identitou   anonym.

Nyní zakládáte pod článkem nové diskusní vlákno.
Pokud chcete reagovat na jiný příspěvek, klikněte na tlačítko "Odpovědět" u některého diskusního příspěvku.

Nyní odpovídáte na příspěvek pod článkem. Nebo chcete raději založit nové vlákno?

 

  • Administrátoři si vyhrazují právo komentáře upravovat či mazat bez udání důvodu.
    Mazány budou zejména komentáře obsahující vulgarity nebo porušující pravidla publikování.
  • Pokud nejste zaregistrováni, Vaše IP adresa bude zveřejněna. Pokud s tímto nesouhlasíte, příspěvek neodesílejte.

přihlásit pomocí externího účtu

přihlásit pomocí jména a hesla

Uživatel:  
Heslo:  

zapomenuté heslo

 

založit nový uživatelský účet

zaregistrujte se

 
zavřít

Nahlásit spam

Opravdu chcete tento příspěvek nahlásit pro porušování pravidel fóra?

Nahlásit Zrušit

Chyba

zavřít

feedback