[dba-SQLServer]@@IDENTITY and SCOPE_IDENTITY

Susan Harkins harkins at iglou.com
Tue Mar 18 20:26:02 CST 2003


I have two questions about these two functions.

1.) BOL is really clear on inserting more than one record or triggers -- I
understand the scope issues in those situations. What's not so clear is
multiple users inserting one record at a time. If a stored procedure inserts
a record and then grabs the newly inserted identity value using @@IDENTITY,
is there any chance @@IDENTITY might return a value other than the right
one? Say a number of users are updating the same table at the same time.
UserA inserts identity value x but before the stored procedure can grab x,
UserB inserts identity value y. Is there any possibility @@IDENTITY will
return y to UserA instead of x? Or, does SQL Server resolve these conflicts
internally?

2.) A single sp inserts two records. In this case, @@IDENTITY and
SCOPE_IDENTITY return the same value -- always the last inserted value.
Right?

Susan H.




More information about the dba-SQLServer mailing list