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?
@@identityfunction returns the last identity created in the same session.
scope_identity()function returns the last identity created in the same session and the same scope.
ident_current(name)returns the last identity created for a specific table or view in any session.
identity()function is not used to get an identity, it's used to create an identity in a
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