Difference between @@IDENTITY, SCOPE_IDENTITY() , IDENT_CURRENT

IDENT_CURRENT('table_name')  -returns the last identity for at table in any scope and any session

Scope - scope means ex- trigger ,stored procedure is scope difrerent from normal query insert
session - session is for current user

 Scope_Identity() - will give the last identity inserted in current scope and current session

@@Identity  -it will return the last identity inserted in any scope and current session.



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

Scope - scope whether execution query is in same script  stored proc,trigger etc. or in different script like in above example insert query is different and other query is in trigger means other scope

Session- executing query in same window or different or same user is executing in on flow.
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).