Java týden První novoroční výprodej
Hledáš brigádu v IT, která bude 100 % home office a 100 % flexibilní? Pak napiš na: redakce [zavináč] itnetwork.cz pro více info!
80 % bodů zdarma díky akci Black Friday! Tento týden rovněž sleva na e-learning Java až 80 %

Lekce 15 - MS-SQL - Datové typy podrobněji

Unicorn College Tento obsah je dostupný zdarma v rámci projektu IT lidem.
Vydávání, hosting a aktualizace umožňují jeho sponzoři.

V minulé lekci, MS-SQL - Uložené procedury, jsme se naučili používat uložené procedury. Dnes se v MS-SQL tutoriálu podíváme podrobněji na datové typy. Každý sloupec, lokální proměnná nebo parametr má atribut, který specifikuje datový typ. Datové typy jsou velice důležitá součást návrhu tabulky a je složité je po návrhu měnit, proto je důležité věnovat jim pozornost už v počátcích projektu.

Datové typy můžeme rozdělit do těchto skupin

  • Přesná čísla
  • Přibližná čísla
  • Datum a čas
  • Řetězce
  • Řetězce v unikódu
  • Binární řetězce
  • Ostatní datové typy

Přesná čísla

Datové typy reprezentující celá čísla „bigint“, „int“, „smallint“, „tinyint“.

Datový typ Rozsah Paměť potřebná pro uložení
bigint -263 až 263 8 bajtů
int -231 až 231 4 bajty
smallint -215 až 215 2 bajty
tinyint 0 až 255 1 bajt

Datový tip reprezentující pravdu / nepravdu „bit“.

Datový typ Rozsah Paměť potřebná pro uložení
bit 0 až 1 1 bit

Pro datový typ bit optimalizuje MS-SQL paměť, pokud je v tabulce uloženo 8 nebo méně polí s typem bit použije se paměť 1 bajt. Pro 9-17 polí 2 bajty atd.

Datové typy reprezentující desetinná čísla „decimal“ nebo „numeric“. Funkčně jsou tyto dva typy stejné

Deklarují se takto: decimal (precision,scale )

  • precision je maximální počet desetinných míst vlevo i vpravo od desetinné čárky, může nabývat hodnot od 1 do 38
  • scale je maximální počet desetinných míst vpravo od desetinné čárky
Datový typ Precision Paměť potřebná pro uložení
decimal 1-9 5 bajtů
decimal 10-19 9 bajtů
decimal 20-28 13 bajtů
decimal 29-38 17 bajtů

Datové typy money a smallmoney reprezentují měnu a vyznačují se tím, že mají pouze dvě desetinná místa.

Datový typ Rozsah Paměť potřebná pro uložení
money -922,337,203,6­85,477.58 až 922,337,203,6­85,477.58 8 bajtů
smallmoney - 214,748.3648 to 214,748.3647 4 bajty
Naši partneři možná hledají právě tebe!

Příklad

CREATE TABLE [dbo].[Priklad1](
        [sloupec1] [bigint] NULL,
        [Sloupec2] [int] NULL,
        [Sloupec3] [smallint] NULL,
        [Sloupec4] [tinyint] NULL,
        [Sloupec5] [bit] NULL,
        [Sloupec6] [decimal](8, 3) NULL,
        [Sloupec7] [money] NULL,
        [Sloupec8] [smallmoney] NULL
) ON [PRIMARY]

GO
INSERT INTO [dbo].[Priklad1]
           ([sloupec1],[Sloupec2],[Sloupec3],[Sloupec4]
           ,[Sloupec5],[Sloupec6],[Sloupec7],[Sloupec8])
     VALUES
           (
-12345678964125874,
2147483647,
32767,
254,
0,
16.458,
100,
500
)

Přibližná čísla

Přibližná čísla používají plovoucí desetinnou čárku, proto nemůže byt vždy jejich hodnota vyjádřena přesně.

Float[(n)] kde n je počet bitů, které jsou použity k zápisu mantisy při vědeckém zápisu čísel.

Datový typ n Paměť potřebná pro uložení
float(n) 1-24 4 bajty
float(n) 25-53 9 bajtů

Standardní typy pro přibližná čísla.

Datový typ Rozsah Paměť potřebná pro uložení
float - 1.79E+308 až -2.23E-308, 0 a 2.23E-308 až 1.79E+308 8 bajtů
real - 3.40E + 38 až -1.18E - 38, 0 a 1.18E - 38 až 3.40E + 38 4 bajty

Příklad

CREATE TABLE [dbo].[Priklad2](
        [Sloupec1] [float] NULL,
        [Sloupec2] [real] NULL)

Datum a čas

Datové typy reprezentující datum nebo čas.

Datový typ Rozsah Paměť potřebná pro uložení
date 0001-01-01 až 9999-12-31 3 bajty
Time 00:00:00.000 až 23:59:59.999 5 bajtů
Datetime Rozsah pro datum 01.01.1753 až 31.12.9999 Rozsah pro čas 00:00:00 až 23:59:59.997 8 bajtů
Datetime2(n) Rozsah pro datum 01.01.0001 až 31.12.9999 Rozsah pro čas 00:00:00 až 23:59:59.9999999 Podle přesnosti od 6 bajtů do 8 bajtů
Datetimeoffset Rozsah pro datum 01.01.0001 až 31.12.9999 Rozsah pro čas 00:00:00 až 23:59:59.9999999 Rozsah pro časovou zónu -14:00 až +14:00 10 bajtů
Smalldatetime Rozsah pro datum 01.01.1900 až 06.06.2079 Rozsah pro čas 00:00:00 až 23:59:00 4 bajty
  • Date – používá se pro uložení datumu
  • Time – používá se pro uložení času
  • Datetime – používá se pro uložení kombinace datumu a času
  • Datetime2(n) – používá se pro uložení kombinace datumu a času, je možné použít přesnost n a tím ovlivnit potřebnou paměť
  • Datetimeoffset – používá se pro uložení kombinace datumu a času včetně časové zóny
  • Smalldatetime – používá se pro uložení kombinace data a času dne, sekundy jsou vždy nula, desetiny sekund nejsou použity

Příklad

CREATE TABLE [dbo].[Priklad1](
        [sloupec1] [date] NULL,
        [Sloupec2] [time](7) NULL,
        [Sloupec3] [datetime]NULL,
        [Sloupec4] [datetime2](7) NULL,
        [Sloupec5] [datetimeoffset](7) NULL,
        [Sloupec6] [smalldatetime] NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Priklad1]
           ([sloupec1]
           ,[Sloupec2]
           ,[Sloupec3]
           ,[Sloupec4]
           ,[Sloupec5]
           ,[Sloupec6])
     VALUES
           ('2016-05-14',
           '17:15:21.5',
           '2016-07-15 13:22:42.214',
           '2016-08-22 14:25:13.8856625',
           '2016-05-13 12:22:05.22 +8:00',
           '2016-05-18 12:58' )

Řetězce

Datový typ Rozsah Paměť potřebná pro uložení
Char(n) Pevná délka, n 1 až 8000 n bajtů (bude rezervováno místo)
Varchar( n/max) Variabilní délka, n 1 až 8000, max indikuje maximální možnou délku n bajtu + 2 max = 231−1 bajtů

Příklad

CREATE TABLE [dbo].[Priklad1](
        [sloupec1] [char](10) NULL,
        [Sloupec2] [varchar](10) NULL,
        [Sloupec3] [varchar] (max) NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Priklad1]
           ([sloupec1]
           ,[Sloupec2]
           ,[Sloupec3]
           )
     VALUES
           ('abcdefghij',
           'abc',
           'abcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcde
                   fghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghija
                   bcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefgh
                   ijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghij'
)

Řetězce s podporou unikódu

Datový typ Rozsah Paměť potřebná pro uložení
nchar(n) Pevná délka, n 1 až 4000 n x 2 bajtů (bude rezervováno místo)
nvarchar( n/max) Variabilní délka, n 1 až 4000, max indikuje maximální možnou délku n x 2 bajtu + 2 max = 231−1 bajtů

Používá se pro ukládání řetězců v unikódu, tzn. Na uložení každého znaku budou použity dva bajty. Lze tak ukládat národní znaky, například diakritiku.

Příklad

CREATE TABLE [dbo].[Priklad1](
        [sloupec1] [nchar] (10) NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Priklad1]
           ([sloupec1])
     VALUES
           ('ěščřžýáíé')

Binární řetězce

Datový typ Rozsah Paměť potřebná pro uložení
Binary (n) Pevná délka, n 1 až 8000 n bajtů (bude rezervováno místo)
varbinary( n/max) Variabilní délka, n 1 až 8000, max indikuje maximální možnou délku n x 2 bajtu + 2 max = 231−1 bajtů

Používá se pro ukládání binárních souboru, například obrázku, přímo do databáze.

Příklad

CREATE TABLE [dbo].[Priklad1](
        [sloupec1] [binary](500) NULL,
        [sloupec2] [varbinary](500) NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Priklad1]
           ([sloupec1],[sloupec2])
     VALUES
           (convert(binary, 'sdkaldjwi235486werfsfg3125h3ouršáhkjíěéýřžéščínřvéěínřzcfdgdfhb654'),
                    convert(binary, 'sdkaldjwi235486werfsfg3125h3ouršáhkjíěéýřžéščínřvéěínřzcfdgdfhb654')
                   )

Ostatní datové typy

  • Timestamp – je datový typ, který generuje jedinečné binární číslo, které se používá na rozlišení verze řádků „rowversion“. Tento datový typ neukládá datum a čas! Pro uložení data a času používejte datetime2. V paměti zabírá 8 bajtů.
  • Uniqueidentifier – je datový typ, který ve spojení například s funkcí NEWID() vygeneruje unikátní identifikátor. V paměti zabírá 16 bajtů.

Příklad

CREATE TABLE [dbo].[Priklad1](
        [sloupec1] [uniqueidentifier] NULL DEFAULT (newid()),
        [sloupec2] [Timestamp],
        [sloupec3] [char] (3)
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Priklad1]
           ([sloupec3])
     VALUES
           ('abc')

Pokud máte dotazy, neváhejte se zeptat v komentářích!


 

 

Aktivity (3)

 

 

Komentáře

Avatar
Antonín Výtaha:23.7.2017 21:44

Kluci, jak mám nastrkat český text do databáze, aby se mi tam uložila normálně písmenka ě,č,ř a ne aby mi je to vypsalo bez háčků? Dělá mi to přesně to, co je popsáno zde v příkladu použití řetězce s podporou unikódu, na tom obrázku se sloupcem 1. Když to tam nastrkám přes Visual Studio, tak se to tam krásně zapíše jak má, ale v aplikaci už to potom blbne...

 
Odpovědět
23.7.2017 21:44
Avatar
Odpovídá na Antonín Výtaha
Michal Štěpánek:24.7.2017 8:53

A když tam ta data strkáš z "té" aplikace, tak je to OK?

Odpovědět
24.7.2017 8:53
Nikdy neříkej nahlas, že to nejde. Vždycky se totiž najde blbec, který to neví a udělá to...
Avatar
Odpovídá na Michal Štěpánek
Antonín Výtaha:24.7.2017 20:48

To právě není. Jakoby byl problém v odesílání přes INSERT do databáze, protože to co je v databázi nastrkané přes Visual Studio (jak je tam na to takový editor, přes Zobrazit data tabulky), tak to se vypíše hezky česky, ale z té aplikace to právě napíše místo tohoto: ěščřžýáíé toto:ešcržýáíé. Když jsem Goooglil tak všichni radili, že je to kvůli kódování, aby se změnilo na UTF-8 (při najezí myši na string ve VS to hlásí, že je v UTF-16), ale nemám sebemenší tuchu o tom, kde by se to ve VS nebo v té databázi měnilo. :-( Do této diskuze mne právě nalákal "Příklad", co je zde u těch řetězců s podporou unikódu, páč tam je přesně to, co se mi děje (jestli nejsem ovšem jediný, komu se to takhle zobrazuji i zde na webu).

Programuji jen pro rekreaci, tak nejspíš jen dělám blbě něco, co máte hoši v malíku. :-)

 
Odpovědět
24.7.2017 20:48
Avatar
Odpovídá na Antonín Výtaha
Hubert Ličman:26.8.2017 22:10

Použij NVARCHAR(XX), kde XX je nejdelší předpokládaná délka. Je to datově úspornější. Variantou je použití NVARCHAR(MAX), když si nejsi jistý délkou.

Odpovědět
26.8.2017 22:10
Neznám slovo "nejde"
Avatar
Hubert Ličman:26.8.2017 22:14

BTW: Poněkud mi tady chybí datový tym IMAGE, který M$ doporučuje právě a jen pro obrázky uložené v MS SQL DB.Je podobný VARBINARY.
Tento typ fakticky pracuje tak, že se snaží na pole bytů aplikovat interní on-line kompresi, což pomůže ušetřit nějaké to místo. Proto je výhodnější, než-li VARBINARY.
ALE POZOR! Datové typy Text, binery, Varbinary a Image se ukládají do BLOB polí, což je odlišná struktura uložení v DB. Fakticky to znamená, že taková pole nejsou běžnými způsoby prohledávatelná a podmínkovatelná, s výjimkou Where... Is NULL.

Odpovědět
26.8.2017 22:14
Neznám slovo "nejde"
Avatar
Tomáš Vrána:31.10.2018 22:55

První dva příklady mi fungují. Další příklady mi podle tohoto zadání nefungují. Vytvoří se tabulka, ale už se do ní nenahrají data. Tomu poslednímu příkladu nerozumím. Proč jsou tam 3 řádky? Používám Visual Studio Express 2012.

Odpovědět
31.10.2018 22:55
Každý svého štěstí strůjcem.
Naši partneři možná hledají právě tebe!
Avatar
Tomáš Vrána:31.10.2018 23:26

Jsem vocas. Už jsem si to zprovoznil. Ale pořád nechápu ten poslední příklad. Proč jsou tam 3 řádky? A proč mi to nevyplňuje 2. sloupec? Píše mi to tam jen <Binary data>

Odpovědět
31.10.2018 23:26
Každý svého štěstí strůjcem.
Avatar
Ondřej Trnka
Redaktor
Avatar
Ondřej Trnka:2.11.2018 13:58

Poslední příklad je na ukázku, jak funguje datový typ "timestamp" a "uniqueidentifier". Příkaz CREATE TABLE vytvoří strukturu a každý příkaz INSERT vloží jeden řádek. 3x INSERT = tři řádky

 
Odpovědět
2.11.2018 13:58
Avatar
Odpovídá na Antonín Výtaha
Antonín Výtaha:14.4.2019 20:28

Takže odpovím si sám: Nakonec se to celé vyřešilo dopsáním písmenka N před odesílaná data do tabulky, např. SELECT [Obyvatel] FROM [Mapy] WHERE [Mesto] = N'Černožice' - právě bez toho N před Černožicemi se do tabulky dotazovalo na Cernožice, čili mi to nevrátilo nic. Kdyby to někoho také potrápilo, tak se na to musí takhle. A z vlastní hlavy to nemám, myšlenka přišla odtud: https://stackoverflow.com/…-server-2008 - jinak datový typ těch měst mám nvarchar. ;-)

 
Odpovědět
14.4.2019 20:28
Avatar
Jastrab
Člen
Avatar
Odpovídá na Antonín Výtaha
Jastrab:21.5.2019 16:37

Ono pri vytvoreni databazy treba spravne zadefinovat nieco ako kodovanie - "Collation". Preto diakritika priamo z SQL Studia fungovala ale z aplikacie nie. Pokial sa chces vyhnut pisania toho N. Ono to spomina aj na tom stackoverflow.
Dalsi problem okrem diakritiky byva aj s datumom a casom, kedy program odosiela jeden format a DB ma definovany druhy format (program miesto ISO formatu 'YYYY-MM-DD' odosle 'DD-MM-YYYY' aj ked je definovane opacne)

 
Odpovědět
21.5.2019 16:37
Avatar
František Střelka:19.9.2019 6:01

Po dokončení sekce jsem tápal, jak databázi "propojit" s logickou vrstvou aplikace (ve VB .NET) a také, jak SQL zakomponovat do OOP. Zkombinoval jsem několik návodů jak se mi to (laicky :-) ) zdálo nejvhodnější. Možná to někomu pomůže, ale budu hlavně rád, když mi poradíte, co je třeba doladit, případně zda volit zcela jinou logiku.
V kódu je čtení/zápis dat z/do tabulky s uživateli, (jako v předchozích lekcích). Setříděná data načítám do listu() s instancemi třídy Uzivatel a s tím dál již pracuji klasicky v OOP.
Module1.vb

Dim uzivatele As New Uzivatele()
Try
     uzivatele.NactiDataSQL()
Catch ex As Exception
     Console.WriteLine("Nepodařilo se načíst data z databáze!{0}Chyba: {1}", vbCrLf, ex.Message)
End Try
Try
     uzivatele.ZapisNovehoUzivateleDoSQL("Fanda", "[email protected]")
Catch ex As Exception
     Console.WriteLine("Nepodařilo se zapsat data do databáze!{0}Chyba: {1}", vbCrLf, ex.Message)
End Try

Uzivatele.vb

Imports System.Data.SqlClient

Public Class Uzivatele
    Property listUzivatelu As List(Of Uzivatel)
    Private conn As SqlConnection
    Private connectionString As String
    Private comm As SqlCommand
    Private query As String

    Public Sub New()
        ConnectionString = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\DBF\RedakcniSystem.mdf;Integrated Security=True" ' s relativní cestou
        ' Absolutní cesta: "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=Y:\Mfdesign\VBnet_aplikace\Vyuka\ITnetwork\MS_SQL\Lekce7azDotazyPresViceTabulekRedakcniSys\Lekce7_az_RedakcniSystem\DBF\RedakcniSystem.mdf;Integrated Security=True"
        listUzivatelu = New List(Of Uzivatel)
    End Sub
    ''' <summary>
    ''' Naplní llistUzivatelu setříděnými daty z SQL
    ''' </summary>
    ''' <remarks>třída Uzvatel má stejné Property jako jsou názvy sloupců v SQL tabulce, vč. id </remarks>
    Public Sub NactiDataSQL()
        listUzivatelu.Clear()
        query = "SELECT * FROM [Uzivatele] ORDER BY [Prezdivka]"
        Using conn = New SqlConnection(connectionString)
            Using comm = New SqlCommand()
                comm.Connection = conn
                comm.CommandType = CommandType.Text
                comm.CommandText = query
                conn.Open()
                Dim dr = comm.ExecuteReader()
                While dr.Read()
                    listUzivatelu.Add(New Uzivatel(dr("Id"), dr("Prezdivka"), dr("Email")))
                End While
            End Using
        End Using
    End Sub
    ''' <summary>
    ''' Zápis nového řádku (uživatele)
    ''' </summary>
    ''' <remarks> </remarks>
    Public Sub ZapisNovehoUzivateleDoSQL(prezdivka As String, email As String)
        query = "INSERT INTO [Uzivatele] ([Prezdivka], [Email]) VALUES (@prezdivka, @email)"
        Using conn = New SqlConnection(connectionString)
            Using comm = New SqlCommand()
                comm.Connection = conn
                comm.CommandType = CommandType.Text
                comm.CommandText = query
                comm.Parameters.AddWithValue("@prezdivka", prezdivka)
                comm.Parameters.AddWithValue("@email", email)
                conn.Open()
                comm.ExecuteNonQuery()
            End Using
        End Using
    End Sub
 
Odpovědět
19.9.2019 6:01
Děláme co je v našich silách, aby byly zdejší diskuze co nejkvalitnější. Proto do nich také mohou přispívat pouze registrovaní členové. Pro zapojení do diskuze se přihlas. Pokud ještě nemáš účet, zaregistruj se, je to zdarma.

Zobrazeno 11 zpráv z 11.