{"id":16,"date":"2012-04-08T15:52:02","date_gmt":"2012-04-08T13:52:02","guid":{"rendered":""},"modified":"2018-09-17T21:58:07","modified_gmt":"2018-09-17T19:58:07","slug":"ako-uklada-udaje-ms-sql-server","status":"publish","type":"post","link":"https:\/\/spireng.sk\/en\/ako-uklada-udaje-ms-sql-server\/","title":{"rendered":"Ako uklad\u00e1 \u00fadaje MS SQL Server"},"content":{"rendered":"

\"Obr\u00e1zok\"Datab\u00e1zov\u00fd syst\u00e9m m\u00e1 nepochybne jednu d\u00f4le\u017eit\u00fa \u00falohu a to je ukladanie \u00fadajov. Sp\u00f4sob, ako presne s\u00fa \u00fadaje ulo\u017een\u00e9, je transparentn\u00fd a pri norm\u00e1lnom pou\u017e\u00edvan\u00ed ho ani nie je nutn\u00e9 pozna\u0165. Zauj\u00edmav\u00e9 to za\u010d\u00edna by\u0165 a\u017e ke\u010f sa objav\u00ed nejak\u00fd probl\u00e9m. Vtedy je ka\u017ed\u00e1 inform\u00e1cia dobr\u00e1 a aj preto sa teraz v kr\u00e1tkom r\u00fdchlokurze pozrieme na to, ako \u00fadaje uklad\u00e1 MS SQL Server.<\/p>\n

Z\u00e1kladn\u00fdm princ\u00edpom pre MS SQL Server je, \u017ee \u00fadaje uklad\u00e1 v s\u00faboroch. Pre jednu datab\u00e1zu ich m\u00f4\u017ee by\u0165 jeden alebo viac. V r\u00e1mci servera e\u0161te existuje nie\u010do ako filegroupy. Je to logick\u00fd kontajner, do ktor\u00e9ho m\u00f4\u017ee patri\u0165 viacero s\u00faborov jednej datab\u00e1zy (filegroupa je teda podmno\u017eina s\u00faborov datab\u00e1zy). Nie\u010do ako adres\u00e1r pre s\u00fabory, ale tak\u00fd, ktor\u00fd vid\u00ed len server. Ak sa teda pozrieme do datab\u00e1zov\u00e9ho s\u00faboru (okrem neho existuje e\u0161te \u017eurn\u00e1lov\u00fd s\u00fabor – ale o tom niekedy inokedy), tak m\u00f4\u017eeme vidie\u0165, \u017ee s\u00fabor je rozdelen\u00fd na tzv. extenty. Extent m\u00e1 64Kb a predstavuje jednotku, ktor\u00fa server \u010d\u00edta zo s\u00faboru. MS SQL Server je celkovo postaven\u00fd na pr\u00e1ci s blokmi \u00fadajov, preto\u017ee na posielanie po sieti napr\u00edklad pou\u017e\u00edva Tabular Data Stream, ktor\u00e9mu tieto bloky tie\u017e vyhovuj\u00fa.Ak sa teda pozrieme to extentu, zist\u00edme, \u017ee je tvoren\u00fd \u00f4smimi str\u00e1nkami (Page). Zatia\u013e \u010do jeden extent m\u00f4\u017ee obsahova\u0165 \u00fadaje pre r\u00f4zne tabu\u013eky, str\u00e1nka m\u00e1 u\u017e len \u00fadaje jednej tabu\u013eky. Str\u00e1nka je potom \u010falej tvoren\u00e1 riadkami. Nie s\u00fa to tak celkom riadky, ktor\u00e9 sa nach\u00e1dzaj\u00fa v tabu\u013eke, lebo tak\u00fdto jeden riadok m\u00f4\u017ee ma\u0165 ve\u013ekos\u0165 maxim\u00e1lne 8 060 B, zatia\u013e \u010do riadok v tabu\u013eke m\u00f4\u017ee by\u0165 omnoho v\u00e4\u010d\u0161\u00ed. Preto sa m\u00f4\u017ee sta\u0165, \u017ee na jeden riadok v tabu\u013eke bude pripada\u0165 viac riadkov v s\u00fabore. Riadok m\u00e1 tie\u017e svoju vn\u00fatorn\u00fa \u0161trukt\u00faru, ale pre \u00fa\u010dely tohto \u010dl\u00e1nku ju budeme bra\u0165 ako najmen\u0161iu jednotku.<\/p>\n

 <\/p>\n

\"\u0160trukt\u00fara<\/p>\n

Je zauj\u00edmav\u00e9 porovna\u0165 toto usporiadanie s logick\u00fdm usporiadan\u00edm \u00fadajov z poh\u013eadu rela\u010dnej datab\u00e1zy. Najz\u00e1kladnej\u0161ou jednotkou je riadok, ale tam podobnos\u0165 kon\u010d\u00ed, preto\u017ee zatia\u013e \u010do pre ukladanie do s\u00faboru s\u00fa lep\u0161ie bloky \u00fadajov, z rela\u010dn\u00e9ho poh\u013eadu sa vy\u017eaduj\u00fa riadky v tabu\u013ek\u00e1ch.<\/p>\n

\"Obr\u00e1zok\"<\/p>\n

Toto ale nie s\u00fa v\u0161etky inform\u00e1cie, ktor\u00e9 je o ukladan\u00ed \u00fadajov MS SQL Servera dobr\u00e9 vedie\u0165. S\u00faborov\u00e9 riadky toti\u017e m\u00f4\u017eu by\u0165 nejako usporiadan\u00e9, pod\u013ea toho ak\u00e9 vlastnosti pre dan\u00fa tabu\u013eku potrebujem. V podstate m\u00e1m 3 mo\u017enosti:<\/p>\n

1. Heap<\/em> = hromada<\/p>\n

2. Cluster Index<\/em> = usporiadan\u00e9<\/p>\n

3. Table Partitions<\/em> = usporiadan\u00e9 a rozdelen\u00e9<\/p>\n

Ak vytvor\u00edm tabu\u013eku a nech\u00e1m jej v\u00fdchodzie nastavenia, tak to, \u010do dostanem v s\u00fabore, bude hromada. Riadky nie s\u00fa nijako usporiadan\u00e9, vkladanie alebo mazanie je ve\u013emi r\u00fdchle. S vyh\u013ead\u00e1van\u00edm je to u\u017e o \u010dosi hor\u0161ie. Akon\u00e1hle potrebujem r\u00fdchlo vyh\u013ead\u00e1va\u0165, tak mi ka\u017ed\u00fd lep\u0161\u00ed n\u00e1stroj pre optimaliz\u00e1ciu porad\u00ed zapn\u00fa\u0165 Cluster Index. Cluster Index sa tak vol\u00e1 preto, preto\u017ee pod\u013ea zvolen\u00e9ho st\u013apca vytvor\u00ed zhluky rovnak\u00fdch hodn\u00f4t a potom ich usporiadan\u00e9 ulo\u017e\u00ed do s\u00faboru. Tak\u017ee m\u00e1m ve\u013emi r\u00fdchle vyh\u013ead\u00e1vanie, ale samozrejme vkladanie a mazanie si bude p\u00fdta\u0165 svoje. Posledn\u00e1 mo\u017enos\u0165 je Table Partitions. V takomto pr\u00edpade sa \u00fadaje usporiadaj\u00fa pod\u013ea jedn\u00e9ho st\u013apca a n\u00e1sledne sa pod\u013ea tohto st\u013apca a podmienky rozdelia do samostatn\u00fdch s\u00faborov. V\u00fdhoda toho cel\u00e9ho je, \u017ee pri vyh\u013ead\u00e1van\u00ed pod\u013ea st\u013apca u\u017e d\u00f4jde len k na\u010d\u00edtaniu \u010dasti tabu\u013eky, ktor\u00e1 pod\u013ea podmienky zodpoved\u00e1 vyh\u013ead\u00e1van\u00e9mu v\u00fdrazu. Table Partitions je dostupn\u00e9 a\u017e vo verzii Enterprise. Podobne ako \u00fadaje s\u00fa ukladan\u00e9 aj neclustroven\u00e9 indexy (to s\u00fa \u010fal\u0161ie indexy, ktor\u00e9 je mo\u017en\u00e9 nad tabu\u013ekou zapn\u00fa\u0165). Tie s\u00fa u\u017e ale tvoren\u00e9 len odkazmi do p\u00f4vodnej tabu\u013eky, nie ako clustrovan\u00fd index, samotn\u00fdmi \u00fadajmi.<\/p>\n

Zatia\u013e \u010do ulo\u017eenie \u00fadajov v s\u00fabore sa ned\u00e1 nijako konfigurova\u0165 a ovplyvni\u0165 a jeho znalos\u0165 je u\u017eito\u010dn\u00e1 hlavne pri rie\u0161en\u00ed n\u00edzko \u00farov\u0148ov\u00fdch probl\u00e9mov, usporiadanie riadkov, pr\u00edpadne ich rozdelenie do viacer\u00fdch s\u00faborov je d\u00f4le\u017eit\u00e1 konfigur\u00e1cia, ktor\u00e1 m\u00e1 vplyv na v\u00fdkon servera. V takomto pr\u00edpade je d\u00f4le\u017eit\u00e9 zv\u00e1\u017ei\u0165, ak\u00e9 \u00fadaje bud\u00fa v tabu\u013eke ukladan\u00e9 a ktor\u00e9 oper\u00e1cie maj\u00fa by\u0165 tie r\u00fdchlej\u0161ie.<\/p>","protected":false},"excerpt":{"rendered":"

Datab\u00e1zov\u00fd syst\u00e9m m\u00e1 nepochybne jednu d\u00f4le\u017eit\u00fa \u00falohu a to je ukladanie \u00fadajov. Sp\u00f4sob, ako presne s\u00fa \u00fadaje ulo\u017een\u00e9, je transparentn\u00fd a pri norm\u00e1lnom pou\u017e\u00edvan\u00ed ho ani nie je nutn\u00e9 pozna\u0165. Zauj\u00edmav\u00e9 to za\u010d\u00edna by\u0165 a\u017e ke\u010f sa objav\u00ed nejak\u00fd probl\u00e9m. Vtedy je ka\u017ed\u00e1 inform\u00e1cia dobr\u00e1 a aj preto sa teraz v kr\u00e1tkom r\u00fdchlokurze pozrieme na […]<\/p>","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17],"tags":[],"class_list":["post-16","post","type-post","status-publish","format-standard","hentry","category-databazy"],"_links":{"self":[{"href":"https:\/\/spireng.sk\/en\/wp-json\/wp\/v2\/posts\/16","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/spireng.sk\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/spireng.sk\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/spireng.sk\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/spireng.sk\/en\/wp-json\/wp\/v2\/comments?post=16"}],"version-history":[{"count":1,"href":"https:\/\/spireng.sk\/en\/wp-json\/wp\/v2\/posts\/16\/revisions"}],"predecessor-version":[{"id":238,"href":"https:\/\/spireng.sk\/en\/wp-json\/wp\/v2\/posts\/16\/revisions\/238"}],"wp:attachment":[{"href":"https:\/\/spireng.sk\/en\/wp-json\/wp\/v2\/media?parent=16"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/spireng.sk\/en\/wp-json\/wp\/v2\/categories?post=16"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/spireng.sk\/en\/wp-json\/wp\/v2\/tags?post=16"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}