Here’s a quick note and reference point for programmers who are forgetful, like me.
1. @@IDENTITY returns the last identity column value generated for any table during the current session regardless of whether the number was generated within the scope of the current stored procedure or not – Any table, current session, any scope
2. SCOPE_IDENTITY() returns the last identity column value generated for any table during the current session and within the current scope only – Any table, current session, current scope
3. IDENT_CURRENT(‘tablename’) returns the last identity column value auto-generated only for the current table regardless of session and scope – Current table, any session, any scope
I use SCOPE_IDENTITY to return identity values due to the following explanation from SQL Server 2008 Book Online –
“For example, there are two tables, T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 by the trigger.
Assuming that both T1 and T2 have identity columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1. @@IDENTITY will return the last identity column value inserted across any scope in the current session. This is the value inserted in T2. SCOPE_IDENTITY() will return the IDENTITY value inserted in T1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function will return the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.”
Books Online further cautions that the value returned by these functions may be untrue if the INSERT operation has ended with an error –
“Failed statements and transactions can change the current identity for a table and create gaps in the identity column values. The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed. For example, if an INSERT statement fails because of an IGNORE_DUP_KEY violation, the current identity value for the table is still incremented. “