SCOPE_IDENTITY() v/s @@IDENTITY

SCOPE_IDENTITY() v/s @@IDENTITY:
Both SCOPE_IDENTITY() and @@IDENTITY will return the last identity value generated in the table. but there is some difference between the two:

SCOPE_IDENTITY() will return the Identity value generated in a table that is currently in scope.

@@IDENTITY will return the Identity value generated in a table irrespective of the scope.

For Example:
Let us suppose we have two tables named table1 & table2... and we have one trigger defined on table1 that is insert a record in table2 when new record will be inserted into table1.

in this case the Output of Both SCOPE_IDENTITY() and @@IDENTITY will be different.
SCOPE_IDENTITY() will return the identity value of table1 that is in current scope.
while @@IDENTITY will return the identity value of table2.

0 comments: