@ Identity
Returns the last inserted id value.
Syntax
@ Identity
Return type
Numeric
Note
After an insert, select into, or large-capacity copy statement is completed, @ identity contains the final id value generated by this statement. If this statement does not affect any table with an ID column, @ identity returns NULL. If multiple rows are inserted, multiple ID values are generated. @ identity returns the final id value. If one or more triggers are triggered to execute the insert operation that generates the id value, @ identity is called immediately after the statement is executed, and the last id value generated by the trigger is returned. If the insert or select into statement fails, the large-capacity copy fails, or the transaction is rolled back, the @ identity value is not restored to the previous setting.
The functions of @ identity, scope_identity, and ident_current are similar in returning the last value of the @ identity column inserted to the table.
@ Identity and scope_identity return the last id value generated in all tables of the current session. However, scope_identity only returns values within the current scope, and @ identity is not limited to specific scopes.
Ident_current is not restricted by the scope and session, but by the specified table. Ident_current returns the id value generated for a specific table in any session and in any scope. For more information, see ident_current.
Example
The following example inserts a row into a table with an ID column and uses @ identity to display the id value used in the new row.
Insert into jobs (job_desc, min_lvl, max_lvl)
Values ('accountant', 12,125)
Select @ identity as 'identified'