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