[dba-SQLServer]@@IDENTITY and SCOPE_IDENTITY

Djabarov, Robert Robert.Djabarov at usaa.com
Wed Mar 19 09:45:50 CST 2003


I'd say it's impossible.  However, in the situation where there is a trigger on the table you're inserting into, that in turn inserts a record into another table with IDENTITY column, - @@IDENTITY will contain the value of that other column's new identity value, while SCOPE_IDENTITY() will pertain to the first table.

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

 -----Original Message-----
From: 	Susan Zeller [mailto:szeller at cce.umn.edu] 
Sent:	Wednesday, March 19, 2003 8:15 AM
To:	dba-sqlserver at databaseadvisors.com
Subject:	RE: [dba-SQLServer]@@IDENTITY and SCOPE_IDENTITY

1.  Yes, it is possible that you will get y value when you want x.  I
have never had this happen, but we now use a routine that generates our
own primary key values.  It essentially is a table in sql server where
we store the last highest id used. A sproc evaluates that, increments
one, returns the new id value as an output parameter and updates the
table that stores the last highest id used.  You can also use newid()
which returns a unqiue value.  

--Susan

-----Original Message-----
From: Mike and Doris Manning [mailto:mikedorism at ntelos.net] 
Sent: Wednesday, March 19, 2003 7:40 AM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer]@@IDENTITY and SCOPE_IDENTITY


1) I've never run across this and I've got 25+ users doing data entry at
the same time.

2) You are correct

Doris Manning
Database Administrator
Hargrove Inc.
www.hargroveinc.com


-----Original Message-----
From: dba-sqlserver-admin at databaseadvisors.com
[mailto:dba-sqlserver-admin at databaseadvisors.com] On Behalf Of Susan
Harkins
Sent: Tuesday, March 18, 2003 09:26 PM
To: SQLList
Subject: [dba-SQLServer]@@IDENTITY and SCOPE_IDENTITY


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.

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


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

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






More information about the dba-SQLServer mailing list