@ @IDENTITY Usage

The syntax @ @IDENTITY returns the last-inserted identity value.
return type
Numeric

Comments
After an INSERT, SELECT into, or bulk copy statement completes, the @ @IDENTITY contains the last identity value produced by this statement. If this statement does not affect any table that has an identity column, the @ @IDENTITY returns NULL. If more than one row is inserted, multiple identity values are generated, and the @ @IDENTITY returns the last-generated identity value. If this statement fires one or more triggers that perform an insert operation that produces an identity value, calling the @ @IDENTITY immediately after the statement executes returns the last identity value produced by the trigger. If the INSERT or SELECT into statement fails or the bulk copy fails, or the transaction is rolled back, the @ @IDENTITY value is not restored to the previous setting.

The @ @IDENTITY, scope_identity, and ident_current functions are similar in terms of returning the last value inserted into the @ @IDENTITY column of the table.

@ @IDENTITY and Scope_identity will return the last identity value generated in all tables in the current session. However, scope_identity only returns values within the current scope, while the @ @IDENTITY is not limited to a specific scope.

Example
The following example inserts a row into the table with an identity column and displays the identity value used in the new row with the @ @IDENTITY.

INSERT into Jobs (JOB_DESC,MIN_LVL,MAX_LVL)
VALUES (' accountant ', 12,125)
SELECT @ @IDENTITY as ' IDENTITY '