{"id":490,"date":"2008-04-08T16:42:14","date_gmt":"2008-04-08T15:42:14","guid":{"rendered":"http:\/\/soci.hu\/blog\/?p=490"},"modified":"2008-04-08T16:42:14","modified_gmt":"2008-04-08T15:42:14","slug":"sql-server-2008-ujdonsagok-33-sparse-columns-foghijas-oszlopok","status":"publish","type":"post","link":"https:\/\/soci.hu\/blog\/index.php\/2008\/04\/08\/sql-server-2008-ujdonsagok-33-sparse-columns-foghijas-oszlopok\/","title":{"rendered":"SQL Server 2008 \u00fajdons\u00e1gok 33. &#8211; Sparse columns (fogh\u00edjas oszlopok?)"},"content":{"rendered":"<p>Ha egy oszlopot megjel\u00f6l\u00f6nk a SPARSE jelz\u0151vel, akkor a NULL \u00e9rt\u00e9ket tartalmaz\u00f3 mez\u0151k egy\u00e1ltal\u00e1n nem fognak helyet elfoglalni a t\u00e1bl\u00e1kban. Ez akkor hasznos, ha olyan az adatmodell\u00fcnk, hogy nagyon sok oszlop van egy t\u00e1bl\u00e1ban, de ezek k\u00fcz\u00f6l sok NULL \u00e9rt\u00e9k\u0171.<br \/>\nA mintap\u00e9lda erre az esetre a term\u00e9keket \u00e9s azok jellemz\u0151it t\u00e1rol\u00f3 adatb\u00e1zis. Minden term\u00e9knek van p\u00e1r jellemz\u0151je, ami vagy azonos, vagy nem a t\u00f6bbiek\u00e9vel. P\u00e9ld\u00e1l minden term\u00e9knek van \u00e1ra, n\u00e9melyiknek van sz\u00edne, m\u00e1snak m\u00e9rete, stb. Ha a term\u00e9keket egy t\u00e1bl\u00e1ban akarjuk t\u00e1rolni, akkor a k\u00f6z\u00f6s adatok k\u00f6z\u00f6ns\u00e9ges oszlopok lenn\u00e9nek, a csak bizonyos term\u00e9kekre vonatkoz\u00f3 adatok pedig sparse oszlopokban. Egy ilyen terv miatt ak\u00e1r t\u00f6bb ezer oszlopa is lehet a t\u00e1bl\u00e1nak, ami miatt a sparse oszlopok sz\u00e1ma felmegy 30000-re (csak az RTM-ben, most m\u00e9g nem).<br \/>\nSz\u00e1momra furcsa ez a p\u00e9lda, mert egy \u00faj term\u00e9k miatt sokszor s\u00e9m\u00e1t is kell m\u00f3dos\u00edtani, \u00faj oszlopot felvenni, ami nekem rossz tervet jelent, de sokan ezt favoriz\u00e1j\u00e1k, ha nem s\u0171r\u0171n v\u00e1ltoznak az oszlopok, mint p\u00e9ld\u00e1ul Sharepointban, ahol a dokumentumok jellemz\u0151it fogj\u00e1k majd v\u00e1rhat\u00f3an \u00edgy t\u00e1rolni (a k\u00f6vetkez\u0151 verzi\u00f3ban).<br \/>\nA teljess\u00e9g kedv\u00e9\u00e9rt elmondom, hogy nekem szimpatikusabb a m\u00e1sik k\u00e9t m\u00f3dszer erre a probl\u00e9m\u00e1ra. Az egyikben egy sz\u00f3t\u00e1rt\u00e1bla van, n\u00e9v, jellemz\u0151 oszlopokkal. A term\u00e9kek t\u00e1bla \u00e9s a sz\u00f3t\u00e1rt\u00e1bla k\u00f6z\u00f6tt egy kapcsol\u00f3t\u00e1bla teremt t\u00f6bb-t\u00f6bb kapcsolatot. \u00cdgy b\u00e1rmely term\u00e9khez felvehet\u00fcnk tetsz\u0151leges adatokat, illetve egy m\u00e1r l\u00e9tez\u0151 jellemz\u0151t b\u00e1rmely term\u00e9khez hozz\u00e1rendelhetj\u00fck. Entity-attribute-value (EAV) vagy open schema n\u00e9ven fut ez a vil\u00e1gban.<br \/>\nA m\u00f3dszer h\u00e1tr\u00e1nya, hogy a &#8220;jellemz\u0151&#8221; oszlop valamilyen polimorf iz\u00e9 kell legyen, valamilyen string vagy variant, hisz a jellemz\u0151k m\u00e1s \u00e9s m\u00e1s t\u00edpus\u00faak. Azaz ez a megold\u00e1s nem strongly typed, nem er\u0151sen t\u00edpusos. A sparse column-\u00f6s az igen.<br \/>\nA lek\u00e9rdez\u00e9s is macer\u00e1s kicsit, hisz 3 t\u00e1bl\u00e1b\u00f3l kell kijoinolni az adatokat, ami lassabb is, m\u00edg a sparse eset\u00e9n egy sort kell egy t\u00e1bl\u00e1b\u00f3l lek\u00e9rdezni, valamint az eredm\u00e9nyek sorokban j\u00f6nnek, nem a term\u00e9szetesebb oszlopos megjelen\u00edt\u00e9sben mint a sparse-n\u00e1l. Mondjuk egy pivot oper\u00e1tor ezen seg\u00edthet.<br \/>\nHarmadik lehets\u00e9ges design xml oszlop haszn\u00e1lata, abba azt\u00e1n minden term\u00e9khez annyi adatot rakunk be, olyan t\u00edpussal, ahogy csak akarjuk. Ekkor az adatok be-ki tol\u00e1sa kicsit k\u00f6r\u00fclm\u00e9nyesebb, illetve az xml s\u00e9mam\u00f3dos\u00edt\u00e1s nem le\u00e1ny\u00e1lom a szerverben.<br \/>\nMindenki m\u00e1sra esk\u00fcszik. <a href=\"http:\/\/www.celko.com\/\">Celko<\/a> pl. a sparse columns h\u00edve.<br \/>\nNo, de besz\u00e9lj\u00fcnk most m\u00e1r konkr\u00e9tan a sparse oszlopokr\u00f3l.<br \/>\nEl\u0151ny\u00f6k:<\/p>\n<ul>\n<li>A NULL egy\u00e1ltal\u00e1n nem foglal el helyet<\/li>\n<li>K\u00edv\u00fclr\u0151l nem l\u00e1tszik az oszlopr\u00f3l, hogy bel\u00fcl sp\u00f3rol\u00f3san t\u00e1rol<\/li>\n<li>A <a href=\"http:\/\/soci.hu\/blog\/index.php\/2008\/04\/01\/sql-server-2008-ujdonsagok-28-filtered-indexek\/\">Filtered Indexekkel<\/a> szuper\u00fcl egy\u00fctt tud m\u0171k\u00f6dni, hisz egy sparse oszlop IS NOT NULL-os filtered index nagyon hat\u00e9konyan csak a t\u00e9nyleges adatokat fogja indexelni.\n<\/li>\n<li>A t\u00e1bla sparse oszlopaira lehet k\u00e9sz\u00edteni egy \u00fan. column set-et, amin kereszt\u00fcl xmlben ki lehet nyerni a nem null oszlopok adatait egyben, s\u0151t, ezen kereszt\u00fcl m\u00f3dos\u00edtani is \u0151ket. Mindj\u00e1rt lesz r\u00e1 p\u00e9lda.<\/li>\n<\/ul>\n<p>De persze mindennek meg van az \u00e1ra:<\/p>\n<ul>\n<li>A NULL ugyan nem foglal semmi helyet, de ha van ott adat, akkor plusz 4 byte kell hozz\u00e1. Egy bit eset\u00e9n az\u00e9rt ez jelent\u0151s vesztes\u00e9g. :)<\/li>\n<li>A text, ntext, image, timestamp, b\u00e1rmely saj\u00e1t t\u00edpus, geometry, geography, varbinray (max) FILESTREAM-mel vagy a sz\u00e1m\u00edtott oszlopok nem lehet sparse-k\u00e9nt defini\u00e1lva<\/li>\n<li>Nem lehet default \u00e9rt\u00e9k\u00fck (minek is persze)<\/li>\n<li>A lapt\u00f6m\u00f6r\u00edt\u00e9s \u00e9s a merge replik\u00e1ci\u00f3 nem kompatibilis vele (a tranzakci\u00f3s igen).<\/li>\n<\/ul>\n<p>A plusz 4 byte miatt nagyon meg kell gondolni, \u00e9rdemes-e haszn\u00e1lni ezt a szolg\u00e1ltat\u00e1st egy oszlopra. Van itt egy <a href=\"http:\/\/msdn2.microsoft.com\/en-us\/library\/cc280604(SQL.100).aspx\">remek kis t\u00e1bl\u00e1zat<\/a>, ami azt taglalja, legal\u00e1bb h\u00e1ny sz\u00e1zal\u00e9ka legyen egy oszlop \u00e9rt\u00e9keinek null, hogy meg\u00e9rje bevetni a sparse-t. Nyilv\u00e1n ez t\u00edpusonk\u00e9nt v\u00e1ltoz\u00f3, bitn\u00e9l ha 2%-n\u00e1l t\u00f6bb sorban van nem null, m\u00e1r nem \u00e9ri meg. L\u00e1that\u00f3 a t\u00e1bl\u00e1zatb\u00f3l, hogy tipikus t\u00edpusokn\u00e1l kb. az adatok fele null kell legyen, k\u00fcl\u00f6nben t\u00f6bb a vesztes\u00e9g, mint a nyeres\u00e9g.<\/p>\n<p>L\u00e1ssunk egy term\u00e9keket t\u00e1rol\u00f3 t\u00e1bl\u00e1s p\u00e9ld\u00e1t:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\ncreate table Termek\r\n(\r\n  id int not null primary key identity,\r\n  nev nvarchar(30) not null,\r\n  ar decimal null,\r\n  szin nvarchar(30) sparse,\r\n  szag nvarchar(30) sparse,\r\n  suly decimal sparse,\r\n  maxhofok int sparse,\r\n  eltarthatosag date sparse,\r\n  egyebAdatok xml column_set for all_sparse_columns\r\n)\r\n<\/pre>\n<p>Id, n\u00e9v, \u00e1r minden term\u00e9knek van, de a t\u00f6bbi sal\u00e1ta nem \u00e9rtelmezhet\u0151 minden term\u00e9kre, azok sparse oszlopokba ker\u00fcltek. Az \u00f6sszes sparse oszlopot \u00f6sszefog egy xml column set. Ezen kereszt\u00fcl l\u00e1that\u00f3 \u00e9s m\u00f3dos\u00edthat\u00f3 is az \u00f6sszes sparse adat egyszerre.<\/p>\n<p>Sz\u00farjunk be p\u00e1r sort, majd k\u00e9rdezz\u00fck le \u0151ket explicit oszlopnevekkel!<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\ninsert Termek (nev, ar, szin, szag, suly, maxhofok, eltarthatosag)\r\nvalues\r\n(N&#039;Amar margarin&#039;, 250, NULL, N&#039;b\u00fcd\u00f6s&#039;, 250, NULL, &#039;20080104&#039;),\r\n(N&#039;L\u00edtiumb\u00e1zis\u00fa csap\u00e1gyzs\u00edr&#039;, 140, &#039;s\u00e1rga&#039;, N&#039;fincsi&#039;, 100, 240, NULL),\r\n(N&#039;Kalciumb\u00e1zis\u00fa csap\u00e1gyzs\u00edr&#039;, 140, &#039;piros&#039;, N&#039;kellemes&#039;, 90, 110, NULL)\r\n\r\nselect id, nev, ar, szin, szag, suly, maxhofok, eltarthatosag from Termek\r\n<\/pre>\n<pre class=\"brush: cpp; title: ; notranslate\" title=\"\">\r\nid          nev                            ar                                      szin                           szag                           suly                                    maxhofok    eltarthatosag\r\n----------- ------------------------------ --------------------------------------- ------------------------------ ------------------------------ --------------------------------------- ----------- -----------------------\r\n1           Amar margarin                  250                                     NULL                           b\u00fcd\u00f6s                          250                                     NULL        2008-01-04 00:00:00.000\r\n2           L\u00edtiumb\u00e1zis\u00fa csap\u00e1gyzs\u00edr       140                                     s\u00e1rga                          fincsi                         100                                     240         NULL\r\n3           Kalciumb\u00e1zis\u00fa csap\u00e1gyzs\u00edr      140                                     piros                          kellemes                       90                                      110         NULL\r\n<\/pre>\n<p>K\u00edv\u00fclr\u0151l semmi k\u00fcl\u00f6nbs\u00e9g a sima \u00e9s a sparse oszlopok k\u00f6z\u00f6tt. Van azonban egy column set\u00fcnk, ami picit felbor\u00edtja a szok\u00e1sos viselked\u00e9st. N\u00e9zz\u00fcnk csak egy select *-ot!<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nselect * from Termek\r\n<\/pre>\n<pre class=\"brush: cpp; title: ; notranslate\" title=\"\">\r\nid          nev                            ar                                      egyebAdatok\r\n----------- ------------------------------ --------------------------------------- ----------------------------------------------------------------------------------------------------\r\n1           Amar margarin                  250                                     &lt;szag&gt;b\u00fcd\u00f6s&lt;\/szag&gt;&lt;suly&gt;250&lt;\/suly&gt;&lt;eltarthatosag&gt;2008-01-04&lt;\/eltarthatosag&gt;\r\n2           L\u00edtiumb\u00e1zis\u00fa csap\u00e1gyzs\u00edr       140                                     &lt;szin&gt;s\u00e1rga&lt;\/szin&gt;&lt;szag&gt;fincsi&lt;\/szag&gt;&lt;suly&gt;100&lt;\/suly&gt;&lt;maxhofok&gt;240&lt;\/maxhofok&gt;\r\n3           Kalciumb\u00e1zis\u00fa csap\u00e1gyzs\u00edr      140                                     &lt;szin&gt;v\u00f6r\u00f6s&lt;\/szin&gt;&lt;szag&gt;penetr\u00e1ns&lt;\/szag&gt;&lt;suly&gt;90&lt;\/suly&gt;&lt;maxhofok&gt;110&lt;\/maxhofok&gt;\r\n<\/pre>\n<p>Hopp\u00e1. A sparse oszlopok nem l\u00e1tszanak a select *-ban, csak az \u00f6sszes\u00edt\u0151 xml column set oszlopunk, amin kereszt\u00fcl xmlk\u00e9nt j\u00f6nnek \u00e1t az adatok. Amelyik oszlop NULL, annak megfelel\u0151 elem hi\u00e1nyzik az xml tartalomb\u00f3l. Ez felfoghat\u00f3 egyfajta sz\u00e1m\u00edtott oszlopk\u00e9nt, hisz nincs let\u00e1rolva pluszk\u00e9nt, a sparse oszlopokb\u00f3l rakj\u00e1k \u00f6ssze dinamikusan.<br \/>\nSz\u00e1m\u00edtott l\u00e9t\u00e9re azonban okos, mert rajta kereszt\u00fcl lehet update-elni \u00e9s insert-\u00e1lni:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nupdate Termek\r\nset egyebAdatok = N&#039;&lt;szin&gt;v\u00f6r\u00f6s&lt;\/szin&gt;&lt;szag&gt;penetr\u00e1ns&lt;\/szag&gt;&lt;suly&gt;90&lt;\/suly&gt;&#039;\r\nwhere id = 3\r\n\r\nselect id, nev, ar, szin, szag, suly, maxhofok, eltarthatosag, egyebAdatok from Termek\r\n<\/pre>\n<pre class=\"brush: cpp; title: ; notranslate\" title=\"\">\r\nid          nev                            ar                                      szin                           szag                           suly                                    maxhofok    eltarthatosag           egyebAdatok\r\n----------- ------------------------------ --------------------------------------- ------------------------------ ------------------------------ --------------------------------------- ----------- ----------------------- ----------------------------------------------------------------------------------------------------\r\n1           Amar margarin                  250                                     NULL                           b\u00fcd\u00f6s                          250                                     NULL        2008-01-04 00:00:00.000 &lt;szag&gt;b\u00fcd\u00f6s&lt;\/szag&gt;&lt;suly&gt;250&lt;\/suly&gt;&lt;eltarthatosag&gt;2008-01-04&lt;\/eltarthatosag&gt;\r\n2           L\u00edtiumb\u00e1zis\u00fa csap\u00e1gyzs\u00edr       140                                     s\u00e1rga                          fincsi                         100                                     240         NULL                    &lt;szin&gt;s\u00e1rga&lt;\/szin&gt;&lt;szag&gt;fincsi&lt;\/szag&gt;&lt;suly&gt;100&lt;\/suly&gt;&lt;maxhofok&gt;240&lt;\/maxhofok&gt;\r\n3           Kalciumb\u00e1zis\u00fa csap\u00e1gyzs\u00edr      140                                     v\u00f6r\u00f6s                          penetr\u00e1ns                      90                                      NULL        NULL                    &lt;szin&gt;v\u00f6r\u00f6s&lt;\/szin&gt;&lt;szag&gt;penetr\u00e1ns&lt;\/szag&gt;&lt;suly&gt;90&lt;\/suly&gt;\r\n<\/pre>\n<p>L\u00e1that\u00f3, hogy az adatok sorrendje tetsz\u0151leges az xml bemenetben, \u00e9s a hi\u00e1nyz\u00f3 adatok NULL-ra lesznek kit\u00f6ltve.<\/p>\n<p>Egy\u00e9bk\u00e9nt a select * csak akkor nem tartalmazza a sparse oszlopokat, ha van xml column set is defini\u00e1lva a t\u00e1bl\u00e1n. Ha nincs, a sparse oszlopok \u00fagy l\u00e1tszanak, mint a sima oszlopok.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nalter table Termek\r\ndrop column egyebAdatok\r\nselect * from Termek\r\n<\/pre>\n<pre class=\"brush: cpp; title: ; notranslate\" title=\"\">\r\nid          nev                            ar                                      szin                           szag                           suly                                    maxhofok    eltarthatosag\r\n----------- ------------------------------ --------------------------------------- ------------------------------ ------------------------------ --------------------------------------- ----------- -----------------------\r\n1           Amar margarin                  250                                     NULL                           b\u00fcd\u00f6s                          250                                     NULL        2008-01-04 00:00:00.000\r\n2           L\u00edtiumb\u00e1zis\u00fa csap\u00e1gyzs\u00edr       140                                     s\u00e1rga                          fincsi                         100                                     240         NULL\r\n3           Kalciumb\u00e1zis\u00fa csap\u00e1gyzs\u00edr      140                                     v\u00f6r\u00f6s                          penetr\u00e1ns                      90                                      NULL        NULL\r\n<\/pre>\n<p>A 30000-es oszlopsz\u00e1mot teszteltem:<\/p>\n<p>declare @i int = 0<br \/>\nwhile (@i < 10000)\nbegin\n    declare @sql nvarchar(max) = N'alter table Termek add ujoszlop' + CAST(@i as nvarchar(20)) + ' nvarchar(10) sparse'\n    print @sql\n    exec(@sql)\n    set @i += 1\nend\n[\/source]\n\nDe egyel\u0151re m\u00e9g 1024 a limit, mint r\u00e9gen, majd az RTM-ig m\u00e9g megv\u00e1ltoztatj\u00e1k.\n<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Ha egy oszlopot megjel\u00f6l\u00f6nk a SPARSE jelz\u0151vel, akkor a NULL \u00e9rt\u00e9ket tartalmaz\u00f3 mez\u0151k egy\u00e1ltal\u00e1n nem fognak helyet elfoglalni a t\u00e1bl\u00e1kban. Ez akkor&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,4,30,58],"tags":[],"class_list":["post-490","post","type-post","status-publish","format-standard","hentry","category-adatbazisok","category-szakmai-elet","category-sql-server","category-sql-server-2008"],"_links":{"self":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/490","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=490"}],"version-history":[{"count":0,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/490\/revisions"}],"wp:attachment":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=490"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=490"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=490"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}