IDENTITY Cheat Sheet

Today we will look at a brief overview of all important functions, global variables, and DBCC commands for working with IDENTITY values in SQL Server. Programmers often make mistakes regarding when to use each of them, particularly due to not understanding how the individual functions behave within the context in which they are called (stored procedure bodies, triggers, and their interrelationships). Hopefully, the following summary will clarify.

@@IDENTITY

• returns data type numeric (38,0)
• after completing INSERT, SELECT INTO, or BULKCOPY, returns the last identity column value in the table
• if there is no identity column in the table, returns NULL
• if multiple rows are inserted and multiple identity values are generated, the last value is returned
• if a trigger is fired that increases the identity value, @@IDENTITY returns this trigger-increased value
• @@IDENTITY does not revert to the previous value if a rollback or insert failure occurs – for example, if a failure occurs on IGNORE_DUP_KEY, the identity for the given table is still incremented
• @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions, all returning the last inserted identity column value in the table
• @@IDENTITY and SCOPE_IDENTITY return the last generated identity value in any table of the current session. SCOPE_IDENTITY returns the value only within the scope, @@IDENTITY regardless of the scope.
• IDENT_CURRENT is not limited by session or scope but only by the specific table, for which it independently returns the last identity
• @@IDENTITY applies only to the current session on the local server. It cannot be called for a linked or remote server => it is necessary to create a stored procedure on the remote server, use @@IDENTITY in it, and call it in the context of the remote server
• it is not suitable for use in replications – replication triggers can affect the identity value because the identity of the last row of the table is not returned, but the identity of the row inserted by the trigger into replication tables => existing procedures must be rewritten to SCOPE_IDENTITY()

SCOPE_IDENTITY()

• returns the last identity value within the scope => scope is defined as a module: stored procedure, trigger, function, or batch
• returns numeric
• difference between @@IDENTITY and SCOPE_IDENTITY: (BOL example)
  • 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()

• returns the identity of the last operation for a specific table regardless of session and scope
• usage: IDENT_CURRENT( ‘table_name’ )
• returns numeric(38,0)
• if the caller does not have permissions on the table, returns NULL or an error
• if the table does not exist, returns NULL
• if the table is empty, returns the first identity seed
• beware of the difference when deleting using DELETE (does not reset identity) and TRUNCATE (resets identity)
• rolling back a transaction or an error never returns the identity value => the number remains incremented as if the transaction was committed

IDENT_INCR()

• returns the increment size (e.g., 1)
• usage: IDENT_INCR ( ‘table_or_view’ )

IDENT_SEED()

• returns the initial value from which the increment starts
• usage: IDENT_SEED ( ‘table_or_view’ )

DBCC CHECKIDENT

• returns the initial value from which the increment starts
• usage:
  • DBCC CHECKIDENT ( table_name [ , { NORESEED | { RESEED [ , new_reseed_value ] } } ] )
    [ WITH NO_INFOMSGS ]
• checks the current identity value in a specific table and possibly changes it – allows setting a new next identity value for the table
• the table must contain an identity column
• this cannot change the initial seed value when creating the column or reseeding existing data => for this, the identity column must be recreated
• example: DBCC CHECKIDENT (“HumanResources.Employee”, RESEED, 30);