Tags

, ,


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:

SELECT @@IDENTITY

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.

SELECT SCOPE_IDENTITY()

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.

References:MSDN

Thanks

Subhro Saha

Advertisements