[dba-SQLServer]@@IDENTITY and SCOPE_IDENTITY

@@IDENTITY will have the value of the new identity resulted from the INSERT into the last table affected by this operation initiated directly or indirectly by the current connection, thus - the scope of @@IDENTITY is the connection.  SCOPE_IDENTITY() returns the new value of an identity field resulting from the last INSERT operation, thus - the scope of SCOPE_IDENTITY() is the last INSERT statement.  This statement can be invoked from a script, stored procedure, trigger, or a function.  Connection scope as well as the statement scope stay intact and are hierarchically dependent (statement scope is subordinate to the connection scope).  No statement scope can depend on anything other than 1 and only 1 connection.  At the same time, no connection can be initiated by anything other than 1 and only 1 login.

If a table affected by INSERT statement has an INSERT trigger that in turn inserts into another table with a field defined as IDENTITY(...),  @@IDENTITY will contain the last identity value resulted from the trigger execution, while SCOPE_IDENTITY() will still return the identity value of the first table affected by the INSERT statement before the trigger fired.  BULK INSERT statement will be treated just like INSERT...SELECT FROM..., if KEEPIDENTITY is not specified.  This means that if multiple rows are inserted at the same time, SCOPE_IDENTITY() will have the last identity value generated.  So will @@IDENTITY, unless there is a trigger situation described above...

I don't think there is anything else that one can say about this "mystery" with identity columns. Don't you think?

Robert Djabarov
Senior SQL Server DBA
USAA IT/DBMS
? (210) 913-3148 - phone
? (210) 753-3148 - pager

 -----Original Message-----
From: 	Francisco H Tapia [mailto:my.lists at verizon.net] 
Sent:	Wednesday, March 19, 2003 11:55 AM
To:	dba-sqlserver at databaseadvisors.com
Subject:	Re: [dba-SQLServer]@@IDENTITY and SCOPE_IDENTITY

BOL: INDEX KEYWORD @@IDENTITY

:What's not so clear is multiple users inserting one record at a time.
1) 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.

That is to say current session = userA.  UserB is in his OWN session so you
could have Users A, B, C, D, E, F... insert records before user A can run
the @@Idenity statement and UserA will still get their own Id back from
their own session, not that of userZ.

2)YES



-Francisco
http://rcm.netfirms.com

On Tuesday, March 18, 2003 6:26 PM [GMT-8],
Susan Harkins <harkins at iglou.com> wrote:

: 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?
:


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com