@@IDENTITY, SCOPE_IDENTITY() , IDENT_CURRENT

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:

• The @@identity function returns the last identity created in the same session across all scopes.
• The scope_identity() function returns the last identity created in the same session and the same scope.
• The ident_current(name) returns the last identity created for a specific table or view in any session.
• The identity() function is not used to get an identity, it's used to create an identity in a select...into query.

The session is the database connection. The scope is the current query or the current stored procedure.

A situation where the scope_identity() and the @@identity functions differ, is if you have a trigger on the table. If you have a query that inserts a record, causing the trigger to insert another record somewhere, the scope_identity() function will return the identity created by the query, while the @@identity function will return the identity created by the trigger.

So, normally you would use the scope_identity() function.

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