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?
Solution 1:
- 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 aselect...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.
Solution 2:
Good question.
@@IDENTITY
: returns the last identity value generated on your SQL connection (SPID). Most of the time it will be what you want, but sometimes it isn't (like when a trigger is fired in response to anINSERT
, and the trigger executes anotherINSERT
statement).SCOPE_IDENTITY()
: returns the last identity value generated in the current scope (i.e. stored procedure, trigger, function, etc).IDENT_CURRENT()
: returns the last identity value for a specific table. Don't use this to get the identity value from anINSERT
, it's subject to race conditions (i.e. multiple connections inserting rows on the same table).IDENTITY()
: used when declaring a column in a table as an identity column.
For more reference, see: http://msdn.microsoft.com/en-us/library/ms187342.aspx.
To summarize: if you are inserting rows, and you want to know the value of the identity column for the row you just inserted, always use SCOPE_IDENTITY()
.