If you fire that trigger, make sure you have the correct ID


I think it would be a fair bet to say that we’ve all used the @@IDENTITY function. It returns the last identity value that was generated by the statement – that’s what books online says.

Note that that statement says absolutely nothing about scope.

Generally, the scope will not affect the value returned by the @@IDENTITY function. However, there is one quite common occurrence that will certainly affect the value of @@IDENTITY.

And that’s when we fire an insert trigger and that trigger is inserting into another table that also has an identity column.

In this case the value of @@IDENTITY will be taken from the table that the trigger inserted into and not the value that was generated from the initial table insert itself.

If we want to ensure that we have the value of @@IDENTITY that the statement generated (and not any trigger(s) that fired) then we have a very good alternative – SCOPE_IDENTITY.

As the name implies, scope is taken into consideration here.

Using SCOPE_IDENTITY instead of @@IDENTITY will ensure that you have the correct value.

It will also ensure that should somebody come along at a later date and add an insert trigger to the table, that itself inserts into a table with an IDENTITY column, then a very subtle bug will not be introduced into your system.

IDENT_CURRENT is another similar function but is not limited by scope or session.

I’d encourage any database professional to become very familiar with these definitions so that they know when to choose the correct one.

I personally like to use SCOPE_IDENTITY a lot, but like everything in database land, we need to be aware of our options.

have a nice day.



Tags: ,

About Martin Catherall

Martin Catherall is Senior SQL consultant at SQL Down Under, based in Melbourne, Australia. Martin is also a Microsoft Data Platform MVP and Regional Mentor (RM) for the Professional Association for SQL Server. Prior to relocating to Australia he was extremely active in the Christchurch, New Zealand data community - founding the local SQL Server user group and organizing SQL Saturday Christchurch - which later became SQL Saturday South Island. He likes learning interesting stuff about processing and storing data and passing on his knowledge. In his spare time he likes to learn guitar and hang out with his two young sons Callum Glen and Robert Michael.