SCOPE_IDENTITY
Posted by Brian Clifton
Written May 28, 2008 at 09:07
Written May 28, 2008 at 09:07
In SQL Server, there are three ways to get the identity of the row you had just inserted: IDENT_CURRENT(), @@IDENTITY, and SCOPE_IDENTITY().
IDENT_CURRENT() is not limited by scope and session; it is limited to a table. For example, you can use IDENT_CURRENT('TableName') to get the last identity generated for table 'TableName'.
SCOPE_IDENTITY() and @@IDENTITY will return last identity values generated in any table in the current session. However, only SCOPE_IDENTITY() returns values inserted within your current scope (the scope of the SQL you just executed). @@IDENTITY is dangerous to use in my opinion because even though it is limited to session, it doesn't take into account identities that could have been made by triggers. SCOPE_IDENTITY() is always safe to use after making your insert to get exactly the identity you need.
For more information, check out the official MSDN documentation:
http://msdn.microsoft.com/en-us/library/aa259185(SQL.80).aspx
IDENT_CURRENT() is not limited by scope and session; it is limited to a table. For example, you can use IDENT_CURRENT('TableName') to get the last identity generated for table 'TableName'.
SCOPE_IDENTITY() and @@IDENTITY will return last identity values generated in any table in the current session. However, only SCOPE_IDENTITY() returns values inserted within your current scope (the scope of the SQL you just executed). @@IDENTITY is dangerous to use in my opinion because even though it is limited to session, it doesn't take into account identities that could have been made by triggers. SCOPE_IDENTITY() is always safe to use after making your insert to get exactly the identity you need.
For more information, check out the official MSDN documentation:
http://msdn.microsoft.com/en-us/library/aa259185(SQL.80).aspx