Difference between @@IDENTITY, SCOPE_IDENTITY() , IDENT_CURRENT

Sql server table identity column in different session and scopes.

Open Microsoft Sql server management studio

Create following two tables and trigger



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



Result of both select statements is empty.

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

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:

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).