Identity sloupce a práce s nimi

Dnes se podíváme na stručný přehled všech důležitých funkcí, globálních proměnných a DBCC příkazů pro práci s IDENTITY hodnotami v SQL Serveru. Programátoři často chybují, kdy použít kterou z nich, zejména pak z neznalosti toho, jak se jednotlivé funkce chovají v rámci kontextu, ve kterém jsou volány (tělo uložené procedury, triggery a jejich vzájemné vztahy). Snad to následující shrnutí osvětlí.

@@IDENTITY

• vrací datový typ numeric (38,0)
• po dokončení INSERT, SELECT INTO nebo BULKCOPY vrací poslední hodnotu identity sloupce v tabulce
• pokud není v tabulce identity sloupec, vrací NULL
• pokud je vloženo více řádků a vygenerováno více identity hodnot, je vrácena poslední hodnota
• pokud dojde k odpálení triggeru, který zvýší identity hodnotu, je @@IDENTITY tato triggerem zvýšená hodnota
• @@IDENTITY se nevrací k předchozí hodnotě, pokud dojde k rolbacku nebo failu insertu – např. dojde-li k failu na IGNORE_DUP_KEY, identity pro danou tabulku se stále inkrementuje
• @@IDENTITY, SCOPE_IDENTITY a IDENT_CURRENT jsou shodné funkce, všechny vrací poslední hodnotu insertu do identity sloupce v tabulce
• @@IDENTITY a SCOPE_IDENTITY vrací poslední identity hodnotu vygenerovanou v kterékoliv tabulce aktuální session. SCOPE_IDENTITY vrací hodnotu pouze ve scope, @@IDENTITY nezávisle na scopu.
• IDENT_CURRENT není limitována session ani scopem, ale pouze konkrétní tabulkou, pro niž nezávisle vrací poslední identity
• @@IDENTITY platí pouze pro aktuální session na lokálním serveru. Nelze ji volat pro linkovaný nebo vzdálený server => je nutné vytvořit na vzdáleném serveru uloženou proceduru, v ní použít @@IDENTITY a volat ji v kontextu vzdáleného serveru
• není vhodné používat v replikacích – replikační triggery mohou ovlivnit identity hodnotu, protože není vráceno identity posledního řádku tabulky, ale identity řádku vloženého triggerem do replikačních tabulek => již existující procedury je nutné přepsat na SCOPE_IDENTITY()

SCOPE_IDENTITY()

•  vrací poslední identity hodnotu v rámci scopu => scopem se rozumí modul: uložená procedura, trigger, funkce nebo batch
• vrací numeric
• rozdíl mezi @@IDENTITY a SCOPE_IDENTITY: (BOL příklad)
  • For example, there are two tables, T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 by the trigger.
  • Assuming that both T1 and T2 have identity columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1. @@IDENTITY will return the last identity column value inserted across any scope in the current session. This is the value inserted in T2. SCOPE_IDENTITY() will return the IDENTITY value inserted in T1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function will return the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.

IDENT_CURRENT()

•  vrací identity poslední operace pro specifickou tabulku a to nezávisle na session a scope
• použití: IDENT_CURRENT( ‘table_name’ )
• vrací numeric(38,0)
• pokud caller nemá práva na tabulku, vrací NULL nebo error
• pokud tabulka neexistuje, vrací NULL
• pokud je tabulka prázdná, vrací první identity seed
• pozor na rozdíl při mazání pomocí DELETE (neruší identity) a TRUNCATE (nuluje identity)
• odrolování transakce nebo chyba nikdy nevrací hodnotu identity => číslo zůstává inkrementované jako by transakce byla commitnuta

IDENT_INCR()

•  vrací velikost incrementu (např. 1)
• použití: IDENT_INCR ( ‘table_or_view’ )

IDENT_SEED()

• vrací počáteční hodnotu, od které startuje increment
• použití: IDENT_SEED ( ‘table_or_view’ )

DBCC CHECKIDENT

•  vrací počáteční hodnotu, od které startuje increment
• použití:
  • DBCC CHECKIDENT  ( table_name  [ , { NORESEED | { RESEED [ , new_reseed_value ] } } ])
    [ WITH NO_INFOMSGS ]
• zjistí aktuální hodnotu identity ve specifické tabulce a provede případně její změnu – umožňuje nastavit novou příští identity hodnotu pro tabulku
• tabulka musí obsahovat identity sloupec
• nelze tímto změnit inicializační seed hodnotu při vytvoření sloupce ani přeseedovat exitující data => na to je nutné vytvořit znovu identity sloupec
• příklad: DBCC CHECKIDENT (“HumanResources.Employee”, RESEED, 30);

DBCC CHECKIDENT ('dbo.SampleTable', RESEED, 0);