what is Difference between @@Identity,@scope Identity,Identity Current

@@Identity:

It returns the last identity value generated for any table in the current session, across all scopes.

Let me explain this... suppose we create an insert trigger on table which inserts a row in another table with generate an identity column, then @@IDENTITY returns that identity record which is created by trigger.

@Scope Identity:

It returns the last identity value generated for any table in the current session and the current scope.

Let me explain this... suppose we create an insert trigger on table which inserts a row in another table with generate an identity column, then SCOPE_IDENTITY result is not affected but if a trigger or a user defined function is affected on the same table that produced the value returns that identity record then SCOPE_IDENTITY returns that identity record which is created by trigger or a user defined function.

============================

INSERT Parent1 DEFAULT VALUES;

SELECT @@IDENTITY;

/*Returns the value 100. This was inserted by the trigger.*/

SELECT SCOPE_IDENTITY();

/* Returns the value 1. This was inserted by the

INSERT statement two statements before this query.*/ 

@IDENTITY CURRENT:

It returns the last identity value generated for a specific table in any session and any scope.

In other words, we can say it is not affected by scope and session, it only depends on a particular table and returns that table related identity value which is generated in any session or scope.

eXAMPLE:

SELECT IDENT_CURRENT('Child');

/* Returns the last value inserted into Child.*/

@@IDENTITY method is not limited to a specific scope.

SCOPE_IDENTITY method is limited for current scope.

IDENT_CURRENT method is not affected by scope and session, it only depends on a specific table.