[dba-SQLServer]@@IDENTITY and SCOPE_IDENTITY

2). Not necessarily true if you have triggers.

/* -- cut here -- */
USE TEMPDB
BEGIN TRAN
SET NOCOUNT ON
CREATE TABLE AAA(AAA_ID INT IDENTITY(1,1), AAA_VALUE CHAR(1));
CREATE TABLE BBB(BBB_ID INT IDENTITY(10,10), BBB_VALUE CHAR(1));
GO
CREATE TRIGGER TR_AAA ON AAA AFTER INSERT AS
BEGIN
INSERT INTO BBB(BBB_VALUE) VALUES('x');
END
GO
INSERT INTO AAA(AAA_VALUE) VALUES('y');

/* Scope_identity() is one scope, one session, any table */
/* RETURNS 1 */
select SCOPE_IDENTITY() AS SCOPE_IDENTITY_VALUE

/* @@identity is any scope, one session, any table */
/* RETURNS 10 */
select @@IDENTITY AS IDENTITY_VALUE
SET NOCOUNT OFF
ROLLBACK
/* -- cut here -- */





>From: "Susan Zeller" <szeller at cce.umn.edu>
>Reply-To: dba-sqlserver at databaseadvisors.com
>To: <dba-sqlserver at databaseadvisors.com>
>Subject: RE: [dba-SQLServer]@@IDENTITY and SCOPE_IDENTITY
>Date: Wed, 19 Mar 2003 08:15:25 -0600
>
>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
>


_________________________________________________________________
Add photos to your e-mail with MSN 8. Get 2 months FREE*.  
http://join.msn.com/?page=features/featuredemail