@@IDENTITY, SCOPE_INDENTITY(), IDENT_CURRENT() - Comparison

Often when one wants to check the last inserted identity value one of the three commands @@IDENTITY, SCOPE_INDENTITY(), IDENT_CURRENT() is used. In this post we will do a comparsion of the three.

@@Identity:
As most you may be aware @@IDENTITY provides the last inserted identity value
for that particular connection. But the thing to note is, it provides you last inserted identity value on the LAST identity table inserted on that connection. Assume, the inserted table A has a trigger inserting into another table B with a identity column. Then @@identity yields identity value of Table B and not table A.

Ident_current() :

Ident_Current('tablename') provides the last inserted identity value across all connections on the table specified. So it cant be used to find the last inserted identity value by your connection.

Scope_Identity(): Scope_identity() is perhaps the most reliable command to find the last inserted identity value on the table by your connection. Provides the last inserted value by the connection not by the underlying trigger.

Couple of things to note:

>If your connection hasnt inserted any rows then @@Identity/Scope_Identity()
returns null.
>Rollback of transaction doesnt reset the identity