G’day,

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.

cheers

Martin.