Is @@IDENTITY reliable?

[Edward]

SQL 7.0

I have a form in ASP.NET and I want to write the values to the SQL
Server tables.

The tables are Customer and Address tables.

There will be an insert into the Customer table, and I need to use the
Identity of this inserted record for the Foreign Key in the Address
table insert.

For example

INSERT INTO Customer (CustomerName)
VALUES (@CustomerName)

Select @@identity as CustomerID
INSERT INTO Address (Address, CustomerID)
VALUES (@Address, CustomerID)

My question is this. If I put this into a single stored procedure can
I absolutely GUARANTEE that the @@identity value will be from the
Customer table insert, or could it feasibly be from another, as it
were, colliding operation?

TIA

Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk

[Erland Sommarskog]

[posted and mailed, please reply in news]

Edward (te********@hotmail.com) writes:

INSERT INTO Customer (CustomerName)
VALUES (@CustomerName)

Select @@identity as CustomerID
INSERT INTO Address (Address, CustomerID)
VALUES (@Address, CustomerID)
This syntax is not correct. You probably mean:

INSERT INTO Customer (CustomerName)
VALUES (@CustomerName)

Select @customerID = @@identity
INSERT INTO Address (Address, CustomerID)
VALUES (@Address, @CustomerID)
My question is this. If I put this into a single stored procedure can
I absolutely GUARANTEE that the @@identity value will be from the
Customer table insert, or could it feasibly be from another, as it
were, colliding operation?

The value of @@identity would normally come from the Customers table,
but there is one situation where it will not. This is if there is a
trigger on the Customers table, and this trigger too inserts data into
a table with the identity property. In that case, @@identity will have
the identity value in that table. (And if you are running SQL7 without
service packs, there is an ugly bug which clears @@identity even if
the triggers inserts into an IDENTITY-less table.)

In SQL2000 there exists an alternative, the function scope_identity(),
which returns the most recently generated IDENTITH value in the current
scope (that is current procedure, trigger etc.)

Note that the value of @@identity *is* local to the current connection,
so it cannot be affected by concurrent operations on other connections.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Dan Guzman]

@@IDENTITY will return the last identity value generated on the current
database connection. The value will not be affected by concurrent inserts
on other connections. However, an issue with @@IDENTITY in SQL 7 is that
the value can get changed my an insert trigger that inserts into tables with
identity columns. In SQL 2000, you can use SCOPE_IDENTIT(), which is not
affected by triggers.

BTW, your SQL code will need to specify @@IDENTITY or other variable on the
insert statement. You need to do something like:

INSERT INTO Customer (CustomerName)
VALUES (@CustomerName)

SE:LECT @CustomerID = @@IDENTITY

INSERT INTO Address (Address, CustomerID)
VALUES (@Address, @CustomerID)
--
Hope this helps.

Dan Guzman
SQL Server MVP
"Edward" <te********@hotmail.com> wrote in message
news:25**************************@posting.google.c om...

SQL 7.0

I have a form in ASP.NET and I want to write the values to the SQL
Server tables.

The tables are Customer and Address tables.

There will be an insert into the Customer table, and I need to use the
Identity of this inserted record for the Foreign Key in the Address
table insert.

For example

INSERT INTO Customer (CustomerName)
VALUES (@CustomerName)

Select @@identity as CustomerID
INSERT INTO Address (Address, CustomerID)
VALUES (@Address, CustomerID)

My question is this. If I put this into a single stored procedure can
I absolutely GUARANTEE that the @@identity value will be from the
Customer table insert, or could it feasibly be from another, as it
were, colliding operation?

TIA

Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk