Update:
A cikkben NEM folytonos sorszámgenerálásról írok, mint pl. amit számla sorszámozásnál használnak, hanem pusztán egyedi kulcsok generálását, amik nem ismétlődnek, monoton nőnek, de nem folytonosak.
A sorszámgenerálás igen általános feladat adatbázisalapú alkalmazásokban. Az egyik leggyakoribb eset, amikor egész számokra épített mesterséges elsődleges kulcsoknak kell értéket generálni. Erre eddig a leggyakrabban alkalmazott megoldás az IDENTITY-vel megjelölt oszlop használata volt. IDENTITY-nél az oszlopon kijelölt sorszámgenerálás és az insert művelet össze volt ragadva. A SEQUENCE nevű új adatbázis objektum segítségével mindentől teljesen független sorszámgenerátorokat hozhatunk létre, amelyeket bármikor meghívhatunk, nem csak insertekhez.
Miért jó ez a sorszámgenerálás és a felhasználása elválasztása? Képzeljük el azt az estet, hogy el kell menteni megrendeléseket. Van 1000 megrendelés fejlécünk (Order Header), és mindegyikhez van átlagban 5 megrendelés tétel (Order Detail). IDENTITY -t használva egyesével be kell szúrni a fejléc sorokat, visszavezetni az adatelérő rétegbe a generált identity értéket, átírni az adott fejléc alá tartozó megrendelés tételek szülő fejlécre mutató idegen kulcsát a kapott értékkel, majd beszúrni a megrendelés tételeket. A megrendelés tételeket be lehet szúrni egy batch-ben, azaz egy körülfordulás alatt. Azonban a fejléc sorok identity-jének visszavezetése miatt 1000 külön batch-et kell beküldeni a szülő sorok beszúrásához, majd további 1000-et a gyerek sorok kedvéért. Ez összesen 2000 hálózati körülfordulás, pedig már kötegeltük a gyerekek beszúrását, e nélkül 1000+5000=6000 körülfordulás lenne. Ha egy hálózati körülfordulás 10ms (ez realisztikus, és magában foglalja az insert idejét is), akkor a 2000 művelet 20 másodperc alatt fut le.
Ha azonban SEQUENCE segítségével generáljuk a kulcsokat, akkor az adatelérő réteg minden további nélkül lekérhet egy nagyobb sorszámtartományt, azaz nem 1-gyel lépteti előre a SEQUENCE-t, hanem pl. 10000-rel. Így az az adatelérő réteg kap 10000 sorszámot, amit más garantáltan nem kap meg, így ezzel ő gazdálkodhat. Mit fog tenni egy okos az adatelérő réteg? Előre kiosztja minden szülő sornak a kulcsértéket a kapott tartományból, utána állítja a gyerekeket, és 1 azaz egy batch-ben képes beszúrni az összes szülő és gyerek sort! Praktikusan ez azt jelenti, hogy valószínűleg 1 mp-en belül beszúrásra kerül mind a 6000 sor.
Óriási nyereség ez, amelyet a fejlettebb Objektum Relációs Mapper-ek (pl. NHibernate) HiLo stratégiaként ismernek, és tudnak SEQUENCE-t használni a kiosztott id-k (elsődleges kulcsok) kezelésére.
A megoldás kulcsa tehát az, hogy még a sorok beszúrása előtt tudunk azoknak id-t generálni, ezáltal extra optimalizálási lehetőségeink vannak. Minek köszönhető ez? Annak, hogy az id generálás és a beszúrás műveletek nincsenek összekötve időben, köszönhetően a SEQUENCE-eknek.
Mivel a SEQUENCE által generált sorszámot tetszés szerint használhatjuk, készíthetünk vele például táblák között megosztott id generátort is, így pár tábla úgy kap elsődleges kulcsot, hogy a táblák között nézve se lesz ütközés közöttük.
Nézzünk pár példát rá.
Integer alapú SEQUENCE létrehozás, amely 1-től indul és 1-esével lépked:
create sequence HumanResources.Sequence1 as int start with 1 increment by 1;
A következő sorszám lekérése:
select next value for HumanResources.Sequence1;
Trükkösebb SEQUENCE-ek is vannak, amelyek miután elérek egy felső határt, átfordulnak, és újrakezdik a számlálást:
create sequence HumanResources.SequenceWithCycle as int start with 1 increment by 1 minvalue 1 maxvalue 5 cycle;
Miután ellépkedett 5-ig, újra 1 lesz a következő generált érték. A cycle kulcsszó nélkül hibát kapnánk az 5 után meghívott next value-ra:
The sequence object ‘SequenceWithCycle’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.
Hogyan implementálták a SEQUENCE-t, hogy elég gyors legyen? Ha tisztán memóriában növelgetnék a sorszámot, akkor a sorszám a szerver újraindulása után újraindulna, ezért mindenképpen kell valamilyen tartós tároló (diszk, sql tábla, stb.) mögé. Lehetne azt csinálni, hogy amikor leállítják a szervert, akkor kiírnák az számláló állását, majd újraindítás után visszaolvassák azt. Ezzel viszont az a baj, hogy ha váratlanul lehal a szerver processz, vagy elmegy az áram, akkor nem lesz kiírva az utolsó érték, így legközelebb újra kioszt már kiadott sorszámokat, ami nyilvánvaló logikai hibákat okozna.
Lehetne minden sorszám letépése után kiírni az aktuális értéket, de ez meg nagyon lassú lenne. A SQL Server által választott megoldás kompromisszum a két oldal között, egyfajta cache-elés.
Memóriában tárolja sorszámot, de úgy, hogy egyszerre lekér egy nagyobb tartományt diszkről, és azt osztogatja ki, tisztán memóriában. Mikor elfogyott a tartomány, akkor megint növel egy nagyobb harapást rajta, kiírja a diszkre, és elkezdi újra memóriából osztani. Mi történik, ha elhasal a szerver? Csak annyi, hogy pár sorszám, ami még nem került kiosztásra a lekért tartományból kimarad, és elkezd egy új tartományt osztani a szerver. Luk lesz a sorszámokban, de ez nem okoz problémát, se az IDENTITY-nél, sem a SEQUENCE-nél nem építhetünk a folytonos sorszámokra, csak azt garantálják, hogy kétszer nem adják ki ugyanazt a sorszámot.
A SQUENCE létrehozásakor meg lehet adni, mekkora tartományt használjon cache-ként:
create sequence HumanResources.SequenceWithLargerCache as int start with 1 increment by 1 cache 100;
Kis cache esetén kicsi a valószínűsége a luknak, nagy cache esetén gyorsabb a sorszámosztás. Értelemszerűen intenzív SEQUENCE használó programoknál érdemes nagy cache értéket használni, de érdekes módon pár 10-es méret után már nem érhetünk el jelentős nyereséget.
A SEQUENCE-eket nem csak egyesével lehet léptetni, például a korábban leírt HiLo id generátor adatelérő stratégia egyszerre pár száz vagy ezer sorszámot kér magának, amivel aztán maga gazdálkodik. Tetszőleges számú előreléptetést így kell kérni:
declare @RangeFirstValue sql_variant ; declare @RangeLastValue sql_variant ; exec sp_sequence_get_range @sequence_name = N'HumanResources.CounterSeq' , @range_size = 50 , @range_first_value = @RangeFirstValue output , @range_last_value = @RangeLastValue output; SELECT @RangeFirstValue, @RangeLastValue;
Kimenet: 2336 2385
A SEQUENCE egyébként más adatbázisokról való áttéréskor (Oracle, Firebird) az egyik legproblémásabb pont volt, SQL Server 2012-től ez is megoldódott.
A másik problémás pont a soronként működő triggerek, de ebben nem lépett előre a 2012.