@@IDENTITY Vs SCOPE_IDENTITY | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive


From BOL, @@IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope. But I see that both lose their scope in new instance
What is the significant difference? Madhivanan Failing to plan is Planning to fail
You haven’t read the rest of the information under SCOPE_IDENTITY, have you? The explanation is that @@IDENTITY doesn’t limit itself to the current code that is executing. If your code inserts a row on a table T1 (with an identity column) and T1 has an insert trigger that inserts a row in table T2 (with an identity column) then @@IDENTITY returns the identity value for T2 (occurred in the trigger, so outside of the scope) and SCOPE_IDENTITY returns the identity value for T1 (occurred in the procedure, so within the scope). But if there is no trigger on T1, then both @@IDENTITY and SCOPE_IDENTITY will return the same identity value. You can always use IDENT_CURRENT(‘table_name’) to get the most recently inserted identity value for a specific table – but you have to remember that this is in no way related to the current scope, and it will return a value even if no row was inserted by your code. You’ll have to wrap the whole thing in a transaction to get any dependable results. Also it can only give you the last inserted value, so it won’t cover multiple rows …
I think the most detailed explanations and the example illustrating it all one can find in BOL under IDENT_CURRENT. Looks like the wrong place, because most folks onyl know about @@IDENTITY and/or SCOPE_IDENTITY(). —
Frank Kalis
Microsoft SQL Server MVP
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

Well. I see under @@Identity but IDENT_CURRENT has full details. Thanks for that point Madhivanan Failing to plan is Planning to fail