Many times I have been asked the difference between @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT by many seasoned developers and programmers. So I thought may this post can be very helpful for all of us to understand the basic difference between @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT. As a programmer I have used these three functions a lot of times in developing some codes.
Lets see and understand the basic differences:
It contains the last identity value that is generated by a statement after an INSERT, SELECT INTO or BULK COPY statements on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value. Since @@IDENTITY is not limited by current scope , so if you have a trigger on a table that is fired after an insert action on a table that has identity column, and the trigger inserts identity values in another table then you will get the last identity value that was generated by the trigger.
It returns the last IDENTITY value inserted into an IDENTITY column in the same scope and the same connection, regardless of the Table that produced the value. A SCOPE is a module : a Stored Procedure, trigger, function, or batch. Therefore two statements are in the same scope if they are in the same Stored Procedure, function or batch. So SCOPE_IDENTITY will return the last identity value that was explicitly created, rather than any identity that was created by a trigger or User defined functions.
SELECT IDENT_CURRENT (‘table_name’)
It returns the last IDENTITY value produced in a table or view regardless of the session or scope of the statement that produced the value. It is only limited to the specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session or scope.