Denali I. – SEQUENCE vs IDENTITY

Az egyik újdonság a Denali-ban a szekvencia. Eddig ilyen volt Oracle-ben, PostgreSQL-ben, most már van MSSQL-ben is. Eddig MSSQL-ben volt IDENTITY, de az egy kicsit más. Az IDENTITY egy adott tábla sémájához kötött autoincrementes oszlop, megadott kezdőértékkel és növekedési értékkel. Értéket neki nem adunk, hanem ő magától kitölti, kivéve ha azt mondjuk, hogy SET IDENTITY_INSERT tabla ON. Az általunk utoljára felhasznált értéket a @@IDENTITY változó illetve SCOPE_IDENTITY() függvény megmondja, ha szeretnénk. Ez jó. Viszont olyan mértékben a sémához van kötve, hogy az identity tulajdonság nem változtatható. Nem lehet se rátenni egy meglévő oszlopra, se levenni róla. Ez igen fájdalmas tud lenni időnként, és én még nem tudtam feldolgozni, hogy ez miért ilyen béna.

Ezzel szemben a szekvencia csupán egy sorszámosztó automata, amiből mindig leszedjük a következő számot, amikor kell. Nézzünk meg egy kedves összehasonlító elemzést, melyben ugyanazt végigcsináljuk identity és sequence esetre:

CREATE TABLE ident (a SMALLINT IDENTITY(32600,100), b INT);
GO
INSERT INTO ident
OUTPUT inserted.*
VALUES(1)
GO 3

Itt ugye létrejön a tábla, az identity érték miatt már csak két sort lehet beleszúrni, ezért a második batch harmadik futásánál jön a hiba, és ez lesz a kimenet:

Beginning execution loop
a      b
------ -----------
32600  1
(1 row(s) affected)
a      b
------ -----------
32700  1
(1 row(s) affected)
a      b
------ -----------
Msg 8115, Level 16, State 1, Line 1
Arithmetic overflow error converting IDENTITY to data type smallint.
Arithmetic overflow occurred.
** An error was encountered during execution of batch. Continuing.
Batch execution completed 3 times.

És innen csak egy DBCC CHECKIDENT segítségével szabadulhatunk el.

A szekvenciás élet elég hasonló:

CREATE TABLE sequen (a SMALLINT, b INT)
GO 
CREATE SEQUENCE mysec
as SMALLINT
START WITH 32600
INCREMENT BY 100;
GO
INSERT INTO sequen 
OUTPUT inserted.*
VALUES(NEXT VALUE FOR mysec, 1)
GO 3

Látjuk, hogy a szekvenciából a NEXT VALUE FOR varázsszó szippantja ki a soron következő számot. Ha nekiállunk SELECT NEXT VALUE FOR szekvencia parancsokat kiadni, akkor kiválóan pörgetjük a számlálót. Amúgy ez a kimenet:

The sequence object 'mysec' cache size is greater than the number of available values; the cache size has been automatically set to accommodate the remaining sequence values.
Beginning execution loop
a      b
------ -----------
32600  1
(1 row(s) affected)
a      b
------ -----------
32700  1
(1 row(s) affected)
a      b
------ -----------
Msg 11728, Level 16, State 1, Line 1
The sequence object 'mysec' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.
** An error was encountered during execution of batch. Continuing.
Batch execution completed 3 times.

A két különbségből az egyik nyilvánvalóan az, hogy más a hibaüzenet meg hibakód. A másik viszont mókás: The sequence object ‘mysec’ cache size is greater than the number of available values; the cache size has been automatically set to accommodate the remaining sequence values. Ez mit is jelent? Hát azt, hogy a szekvenciának a következő N értékét cache-eli az engine, és így sokkal gyorsabban tudja kiszolgálni a kéréseket. Ez persze azzal jár, hogy ha természetellenes módon áll meg a szerver, akkor a cache-elt értékek elvesznek, és a következő nem-cache-elt számtól megy tovább az élet. Esetünkben mindössze két lehetséges következő érték volt, ezért dobott egy warningot, hogy nem tudta megtölteni a cache-t. A sebességvonzatról annyit, hogy a cache-elt szekvencia a leggyorsabb, követi az identity, a sor végén pedig a no cache-es szekvencia ballag – azt azért tegyük hozzá, hogy a különbség 10-20% az egyes szintek között, szóval nem itt fogunk performanciát tuningolni.

Nézzük meg, hogy mit lehet még csinálni a szekvenciával! Hát, van egy olyan opciója is, hogy CYCLE, ebben az esetben amikor eléri az utolsó értéket, akkor reseteli magát a definíció szerinti alapértékre, itt pl. az utolsó beszúrás megint 32600 lett volna. Ha esetleg valahova máshova szeretnénk ugrani, vagy csak nincs a CYCLE opció bekapcsolva, akkor a RESTART opció segít. Paraméterek nélkül pont mint a cycle, de mondhatjuk úgy is, hogy

ALTER SEQUENCE mysec RESTART WITH 2011;

és akkor 2011-től pörög tovább a számláló. A szekvenciának mindene megváltoztatható: kezdőérték, növekmény (ez ugye fix az identity esetén), cycle ,cache, plusz lehet neki minvalue meg maxvalue opciót is megadni, hogy pl csak 10000 és 99999 közötti számokat adjon ki, és tutifix, h ötjegyű értékeket kapok, ha pl. ilyen a vágyam.

Ha pedig valaki teljesen megvadul, és kell neki 100 szám egymás után, azt is lehet kérni, a sp_sequence_get_range tárolt eljárással, ami output paraméterekben tol vissza mindent.

Összességében a szekvencia egy kiváló lehetőség az identity-től való megszabadulásra, bár tudnám értékelni azt is, ha nem lenne így a tábla szívébe vésve az identity-ség. Valószínűleg azonban ez kevésbé volt fájdalmas a termékfejlesztőknek. Szerintem az esetek 90%-ában syntax sugar, néha – pl. ott, ahol több, mint egy sorszám kéne egy sorba, amit az identity nem enged – igazi haszna is van neki. Viszont mindenki, aki Oracle-ről MSSQL-re migrált eddig, most azt gondolja: miért nem vártam mostanáig? :)

2 Comments

  1. S.E.:

    Két komment:

    1: identity esetén sem fix a növekmény: definiáláskor megadhatod, hogy mennyi legyen.

    2: Nem hiszem, hogy az SQL fejlesztők tömegesen lecserélnék az identity-t sequence-re. A kettő nem ugyanarra való. Arra, amire kitalálták, az identity a legjobb. A sequence felhasználhatósága sokrétűbb, akár ki is lehet vele váltani az identity-t, de minek?
    Ráadásul ha a sequence-t újragenerálom, akkor az insertem simán elhasalhat duplikátum miatt. Igaz, az identity-t is lehet resetelni, de leglább nem kell még egy objektumot létrehozni az identity-funkcionalitás eléréséhez.

    Szóval szerintem szépen egymás mellett fog élni mind a kettő.

    My 2 cents.

    Üdv,

  2. Erik:

    Szia!

    1. Identity definiáláskor megadod, h mennyi legyen – és onnantól fix. Ha később kitalálod, hogy nem ötösével, hanem tízesével akarsz ugrálni, akkor az fájni fog. Bocsánat, ha nem voltam egyértelmű.
    2. Az identity egész jó, addig, amíg nem kell változtatni a sémát. Pl. egy alkalmazás edig maga generálta a sorszámokat egy ötvenmillió soros táblába, ezentúl pedig egy másik rendszerből kapja. Hogyan oldod meg? A fejlesztő nem fogja tömegesen lecserélni magától, de lehet, hogy szépen meg fogom rá kérni, mert az üzemeltetőt szivatja meg vele, neki kell majd migrálnia.

    Erre gondoltam, amikor azt mondtam, hogy tudnám értékelni, ha nem lenne a táblába belehegesztve, mert akkor nem gondolnék én sem a cserére. Ideális esetben az identity egy constraint lenne vagy efféle, amit levehetek, feltehetek – de nem ez a helyzet. A másik oldalon meg a szekvencia nextval default value-ként való megadása lenne ezzel egyenértékű, de az sem megy. Kiváltani biztos nem fogja, legalábbis nem a belátható jövőben, az tuti. Ez inkább egy régi igénye a népnek, Oracle meg ANSI oltáron való áldozat bemutatása.

    Btw, az SQL fejlesztők kifejezés tetszik. Sajnos nincsenek túl sokan viszont. A legtöbb fejlesztő procedurális-imperatív, aki megírja a SQL kódot is, kevesebben tudják, hogy mit is tesznek valójában. Pedig irgalmatlan pénzt és szívást lehetne megtakarítani.

    Your 2 cent is much appreciated. Jó célokra fordítom.

    Erik

Leave a comment