@@IDENTITY will return the last identity value inserted into a table in the users current session. While @@IDENTITY is limited to the current session, not limited to the current scope. If an other user insert a row in the same table after you, you might get that Identity value.
Or Second scenario is if you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it. So from the above discussion
it is apparent that what ever that Identity value you got my be or may not be correct. To solve that problem we can use Scope_Identity().
SELECT SCOPE_IDENTITY(), returns the last IDENTITY value created in the current session, but it will also limit it to your current scope as well. i.e You are gauranteed to get the last identity value you inserted into the table.
Or Second scenario is if you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it. So from the above discussion
it is apparent that what ever that Identity value you got my be or may not be correct. To solve that problem we can use Scope_Identity().
SELECT SCOPE_IDENTITY(), returns the last IDENTITY value created in the current session, but it will also limit it to your current scope as well. i.e You are gauranteed to get the last identity value you inserted into the table.
