[dba-SQLServer]@@IDENTITY and SCOPE_IDENTITY

Susan Zeller szeller at cce.umn.edu
Wed Mar 19 08:15:25 CST 2003


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




More information about the dba-SQLServer mailing list