Difference between @@IDENTITY, SCOPE_IDENTITY() , IDENT_CURRENT

Open Microsoft Sql server management studio

Create following two tables and trigger

download.png

Now we will execute following commands but within same session (with in same query window)

download (1).png

Result of both select statements is empty.

Now we will execute following commands but within same session (with in same query window)

download (2).png

Note: Insert statement on table1 will insert value ‘1' in table 1 and trigger will insert value ‘100' in table2

So we have two insert on single insert

One in table1 and another in table2 so we have two scope one is current related to table1 one another is global scope related two table1 and table2

Now open a new query window (new session) and execute the following commands:

download (3).png

So we have two scenario to compare session and scope

  Session Scope
@@IDENTITY Same Session Global scope value
SCOPE_IDENTITY() Same Session Local scope value
IDENT_CURRENT() May be different Depends on table name passed in parameter

Conclusion:

SELECT @@IDENTITY: returns the last identity value generated for any table in the current session, across all scopes(i.e. global scope).

SELECT IDENT_CURRENT : returns the last identity value generated for any table in the current session and the current scope(i.e. local scope).

SELECT SCOPE_IDENTITY(): returns the last identity value generated for a specific table in any session and any scope(i.e. global scope).

X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now