What are the pros and cons of using Scope_Identity() vs @@Identity? (If it matters, the environment is Self-Serving with SQL Server).

Thanks!

Here's a quick article on it http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro01/html/sql01d4.asp. In Summary Scope_Identity() will return the value of the last inserted row within the current scope, important for triggers, while @@Identity returns the value of the last inserted row globally.

bclubb wrote:

Here's a quick article on it http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro01/html/sql01d4.asp. In Summary Scope_Identity() will return the value of the last inserted row within the current scope, important for triggers, while @@Identity returns the value of the last inserted row globally.

That's not exactly right unless you don't mean globally the way the word implies it. From the SQL Books On Line:

_ @@IDENTITY and SCOPE_IDENTITY return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope. _

BOb

Correct, BOb. You should always select Scope_Identity() unless: - you're on SqlServer 7 - you use triggers to insert the identity value (for example your entity is mapped onto a view). In this case, @@Identity is required, because the trigger is another scope.