Ha egy oszlopot megjelölönk a SPARSE jelzővel, akkor a NULL értéket tartalmazó mezők egyáltalán nem fognak helyet elfoglalni a táblákban. Ez akkor hasznos, ha olyan az adatmodellünk, hogy nagyon sok oszlop van egy táblában, de ezek küzöl sok NULL értékű.
A mintapélda erre az esetre a termékeket és azok jellemzőit tároló adatbázis. Minden terméknek van pár jellemzője, ami vagy azonos, vagy nem a többiekével. Példál minden terméknek van ára, némelyiknek van színe, másnak mérete, stb. Ha a termékeket egy táblában akarjuk tárolni, akkor a közös adatok közönséges oszlopok lennének, a csak bizonyos termékekre vonatkozó adatok pedig sparse oszlopokban. Egy ilyen terv miatt akár több ezer oszlopa is lehet a táblának, ami miatt a sparse oszlopok száma felmegy 30000-re (csak az RTM-ben, most még nem).
Számomra furcsa ez a példa, mert egy új termék miatt sokszor sémát is kell módosítani, új oszlopot felvenni, ami nekem rossz tervet jelent, de sokan ezt favorizáják, ha nem sűrűn változnak az oszlopok, mint például Sharepointban, ahol a dokumentumok jellemzőit fogják majd várhatóan így tárolni (a következő verzióban).
A teljesség kedvéért elmondom, hogy nekem szimpatikusabb a másik két módszer erre a problémára. Az egyikben egy szótártábla van, név, jellemző oszlopokkal. A termékek tábla és a szótártábla között egy kapcsolótábla teremt több-több kapcsolatot. Így bármely termékhez felvehetünk tetszőleges adatokat, illetve egy már létező jellemzőt bármely termékhez hozzárendelhetjük. Entity-attribute-value (EAV) vagy open schema néven fut ez a világban.
A módszer hátránya, hogy a “jellemző” oszlop valamilyen polimorf izé kell legyen, valamilyen string vagy variant, hisz a jellemzők más és más típusúak. Azaz ez a megoldás nem strongly typed, nem erősen típusos. A sparse column-ös az igen.
A lekérdezés is macerás kicsit, hisz 3 táblából kell kijoinolni az adatokat, ami lassabb is, míg a sparse esetén egy sort kell egy táblából lekérdezni, valamint az eredmények sorokban jönnek, nem a természetesebb oszlopos megjelenítésben mint a sparse-nál. Mondjuk egy pivot operátor ezen segíthet.
Harmadik lehetséges design xml oszlop használata, abba aztán minden termékhez annyi adatot rakunk be, olyan típussal, ahogy csak akarjuk. Ekkor az adatok be-ki tolása kicsit körülményesebb, illetve az xml sémamódosítás nem leányálom a szerverben.
Mindenki másra esküszik. Celko pl. a sparse columns híve.
No, de beszéljünk most már konkrétan a sparse oszlopokról.
Előnyök:
- A NULL egyáltalán nem foglal el helyet
- Kívülről nem látszik az oszlopról, hogy belül spórolósan tárol
- A Filtered Indexekkel szuperül együtt tud működni, hisz egy sparse oszlop IS NOT NULL-os filtered index nagyon hatékonyan csak a tényleges adatokat fogja indexelni.
- A tábla sparse oszlopaira lehet készíteni egy ún. column set-et, amin keresztül xmlben ki lehet nyerni a nem null oszlopok adatait egyben, sőt, ezen keresztül módosítani is őket. Mindjárt lesz rá példa.
De persze mindennek meg van az ára:
- A NULL ugyan nem foglal semmi helyet, de ha van ott adat, akkor plusz 4 byte kell hozzá. Egy bit esetén azért ez jelentős veszteség. :)
- A text, ntext, image, timestamp, bármely saját típus, geometry, geography, varbinray (max) FILESTREAM-mel vagy a számított oszlopok nem lehet sparse-ként definiálva
- Nem lehet default értékük (minek is persze)
- A laptömörítés és a merge replikáció nem kompatibilis vele (a tranzakciós igen).
A plusz 4 byte miatt nagyon meg kell gondolni, érdemes-e használni ezt a szolgáltatást egy oszlopra. Van itt egy remek kis táblázat, ami azt taglalja, legalább hány százaléka legyen egy oszlop értékeinek null, hogy megérje bevetni a sparse-t. Nyilván ez típusonként változó, bitnél ha 2%-nál több sorban van nem null, már nem éri meg. Látható a táblázatból, hogy tipikus típusoknál kb. az adatok fele null kell legyen, különben több a veszteség, mint a nyereség.
Lássunk egy termékeket tároló táblás példát:
create table Termek ( id int not null primary key identity, nev nvarchar(30) not null, ar decimal null, szin nvarchar(30) sparse, szag nvarchar(30) sparse, suly decimal sparse, maxhofok int sparse, eltarthatosag date sparse, egyebAdatok xml column_set for all_sparse_columns )
Id, név, ár minden terméknek van, de a többi saláta nem értelmezhető minden termékre, azok sparse oszlopokba kerültek. Az összes sparse oszlopot összefog egy xml column set. Ezen keresztül látható és módosítható is az összes sparse adat egyszerre.
Szúrjunk be pár sort, majd kérdezzük le őket explicit oszlopnevekkel!
insert Termek (nev, ar, szin, szag, suly, maxhofok, eltarthatosag) values (N'Amar margarin', 250, NULL, N'büdös', 250, NULL, '20080104'), (N'Lítiumbázisú csapágyzsír', 140, 'sárga', N'fincsi', 100, 240, NULL), (N'Kalciumbázisú csapágyzsír', 140, 'piros', N'kellemes', 90, 110, NULL) select id, nev, ar, szin, szag, suly, maxhofok, eltarthatosag from Termek
id nev ar szin szag suly maxhofok eltarthatosag ----------- ------------------------------ --------------------------------------- ------------------------------ ------------------------------ --------------------------------------- ----------- ----------------------- 1 Amar margarin 250 NULL büdös 250 NULL 2008-01-04 00:00:00.000 2 Lítiumbázisú csapágyzsír 140 sárga fincsi 100 240 NULL 3 Kalciumbázisú csapágyzsír 140 piros kellemes 90 110 NULL
Kívülről semmi különbség a sima és a sparse oszlopok között. Van azonban egy column setünk, ami picit felborítja a szokásos viselkedést. Nézzünk csak egy select *-ot!
select * from Termek
id nev ar egyebAdatok ----------- ------------------------------ --------------------------------------- ---------------------------------------------------------------------------------------------------- 1 Amar margarin 250 <szag>büdös</szag><suly>250</suly><eltarthatosag>2008-01-04</eltarthatosag> 2 Lítiumbázisú csapágyzsír 140 <szin>sárga</szin><szag>fincsi</szag><suly>100</suly><maxhofok>240</maxhofok> 3 Kalciumbázisú csapágyzsír 140 <szin>vörös</szin><szag>penetráns</szag><suly>90</suly><maxhofok>110</maxhofok>
Hoppá. A sparse oszlopok nem látszanak a select *-ban, csak az összesítő xml column set oszlopunk, amin keresztül xmlként jönnek át az adatok. Amelyik oszlop NULL, annak megfelelő elem hiányzik az xml tartalomból. Ez felfogható egyfajta számított oszlopként, hisz nincs letárolva pluszként, a sparse oszlopokból rakják össze dinamikusan.
Számított létére azonban okos, mert rajta keresztül lehet update-elni és insert-álni:
update Termek set egyebAdatok = N'<szin>vörös</szin><szag>penetráns</szag><suly>90</suly>' where id = 3 select id, nev, ar, szin, szag, suly, maxhofok, eltarthatosag, egyebAdatok from Termek
id nev ar szin szag suly maxhofok eltarthatosag egyebAdatok ----------- ------------------------------ --------------------------------------- ------------------------------ ------------------------------ --------------------------------------- ----------- ----------------------- ---------------------------------------------------------------------------------------------------- 1 Amar margarin 250 NULL büdös 250 NULL 2008-01-04 00:00:00.000 <szag>büdös</szag><suly>250</suly><eltarthatosag>2008-01-04</eltarthatosag> 2 Lítiumbázisú csapágyzsír 140 sárga fincsi 100 240 NULL <szin>sárga</szin><szag>fincsi</szag><suly>100</suly><maxhofok>240</maxhofok> 3 Kalciumbázisú csapágyzsír 140 vörös penetráns 90 NULL NULL <szin>vörös</szin><szag>penetráns</szag><suly>90</suly>
Látható, hogy az adatok sorrendje tetszőleges az xml bemenetben, és a hiányzó adatok NULL-ra lesznek kitöltve.
Egyébként a select * csak akkor nem tartalmazza a sparse oszlopokat, ha van xml column set is definiálva a táblán. Ha nincs, a sparse oszlopok úgy látszanak, mint a sima oszlopok.
alter table Termek drop column egyebAdatok select * from Termek
id nev ar szin szag suly maxhofok eltarthatosag ----------- ------------------------------ --------------------------------------- ------------------------------ ------------------------------ --------------------------------------- ----------- ----------------------- 1 Amar margarin 250 NULL büdös 250 NULL 2008-01-04 00:00:00.000 2 Lítiumbázisú csapágyzsír 140 sárga fincsi 100 240 NULL 3 Kalciumbázisú csapágyzsír 140 vörös penetráns 90 NULL NULL
A 30000-es oszlopszámot teszteltem:
declare @i int = 0
while (@i < 10000)
begin
declare @sql nvarchar(max) = N'alter table Termek add ujoszlop' + CAST(@i as nvarchar(20)) + ' nvarchar(10) sparse'
print @sql
exec(@sql)
set @i += 1
end
[/source]
De egyelőre még 1024 a limit, mint régen, majd az RTM-ig még megváltoztatják.