Dot Net Technologies - SQL Tips

Dot Net Technologies - SQL Tips: "So, you want to get the value of an identity column after an insert so you can update the UI or use it in another procedure. You've been using @@IDENTITY for ages, and it has worked fine. But today, you're getting a new value. What's up?

There are 3 (or more if you want to consider queries or identity tables) methods for getting the identity value from a table after an insert. The methods are @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT(). What's the difference?

@@IDENTITY gets the last inserted identity value. The problem is, in a high-volumn environment or one with triggers, it may not be the value you are expecting.

The SCOPE_IDENTITY() function gets the last identity inserted within the same scope (e.g. inside the procedure you're running). This is useful especially when your table has triggers which might do another insert. You'll only get the identity value of your change, not the trigger's.

The IDENT_CURRENT('table_name') returns the last identity value for the specified table, regardless of scope.

So, for safety in your routines, if you want the value YOU inserted, try the SCOPE_IDENTITY function for safety!"