Difference between @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT

on
Many times we need last generated  identity from database.  It's very useful to get last generated id and use it. Many developer use bad approach to get  last generated identity by using Top 1 .
Now we will discuss how IDENTITY functions useful .

We have may three ways to get identity .

•    @@IDENTITY
•    SCOPE_IDENTITY
•    IDENT_CURRENT

Listed functions return last-generated identity value. But all three have different way of work.

@@IDENTITY

@@Identity return last identity created by sql server in current session across all scopes.
Suppose we have one trigger on table "Order" which inserting data into "Order History" table. And both tables have identity column. @@Identity functions will return last generated identity from "Order History" table.

SELECT @@IDENTITY;

SCOPE_IDENTITY
 
Scope_identity  returns last generated identity from current session and the current scope. 
Suppose we have one trigger on table "Order" which inserting data into "Order History" table. And both tables have identity column. @@Identity functions will return last generated identity from "Order" table.

IDENT_CURRENT
 
It return last generated identity for a table from any session. It depend upon table not matter which session inserted record.

Comments

Post a Comment