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]

Re: Is @@IDENTITY reliable?

[posted and mailed, please reply in news]

Edward (teddysnips@hot mail.com) writes:[color=blue]
> INSERT INTO Customer (CustomerName)
> VALUES (@CustomerName)
>
> Select @@identity as CustomerID
> INSERT INTO Address (Address, CustomerID)
> VALUES (@Address, CustomerID)[/color]

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)
[color=blue]
> 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?[/color]

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, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Dan Guzman]

Re: Is @@IDENTITY reliable?

@@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" <teddysnips@hot mail.com> wrote in message
news:25080b60.0 312120729.65868 4d7@posting.goo gle.com...[color=blue]
> 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[/color]