SCOPE_IDENTITY() or @@IDENTITY

I’ve seen quite a lot of usage of both functions in the T-SQL codes. However, their usage sometimes matters. The expectation from @@IDENTITY can sometimes not be the desired.

This post shows why you should use SCOPE_IDENTITY() instead of @@IDENTITY when using it with tables that have triggers.

Select the SCOPE_IDENTITY() AND @@IDENTITY functions’ current values.

The table Contacts doesn’t have a trigger and the values of both the functions are identical.
Create Contacts history table and a trigger on Contacts.

Insert a new record.

Now the values of the functions differ. SCOPE_IDENTITY() returns a value of 2 which is the desired, and @@IDENTITY returns a value of 1 which is not the desired. That’s because of the trigger, and the usage of @@IDENTITY produces a bug in such situations.

Now you know why you should use SCOPE_IDENTITY() in your T-SQL codes.

Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+

Leave a Reply

Your email address will not be published. Required fields are marked *

*