SQL Server

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);

Leave a Reply

Your email address will not be published.