Tebo Tebo - 16 days ago 6
SQL Question

What is the difference between Scope_Identity(), Identity(), @@Identity, and Ident_Current?

I know Scope_Identity(), Identity(), @@Identity, and Ident_Current all get the value of the identity column, but i would love to know the difference.

Part of the controversy i'm having is what do they mean by scope as applied to these functions above?

I would also love a simple example of different scenarios of using them?

Answer
  • The @@identity function returns the last identity created in the same session.
  • The scope_identity() function returns the last identity created in the same session and the same scope.
  • The ident_current(name) returns the last identity created for a specific table or view in any session.
  • The identity() function is not used to get an identity, it's used to create an identity in a select...into query.

The session is the database connection. The scope is the current query or the current stored procedure.

A situation where the scope_identity() and the @@identity functions differ, is if you have a trigger on the table. If you have a query that inserts a record, causing the trigger to insert another record somewhere, the scope_identity() function will return the identity created by the query, while the @@identity function will return the identity created by the trigger.

So, normally you would use the scope_identity() function.

Comments