Re: @@IDENTITY correct syntax?

From: Leo Gaggl (leo.gaggl_at_0spam0.autom-8.com)
Date: 12/15/03


Date: Tue, 16 Dec 2003 09:07:34 +1030

Mike wrote:
>
> Hi,
> That seems to work well. However, is it possible that another user
> could make an insert just after this one, so that the @@IDENTITY value
> returned is that created by the second, wrong insert? Or will this
> example only return the identity value created by myStatement's
> executeUpdate?
> Thanks,
> Mike

According to the docs it will return the identity produced by the
current statement if selected within the same statement. Although I seem
to recall from the good old days (SQL Server 6.x) that this was not
always the case in high load environments.

See SQL Docs:

@@IDENTITY
Returns the last-inserted identity value.

Syntax
@@IDENTITY

Return Types
numeric

Remarks
After an INSERT, SELECT INTO, or bulk copy statement completes,
@@IDENTITY contains the last identity value generated by the statement.
If the statement did not affect any tables with identity columns,
@@IDENTITY returns NULL. If multiple rows are inserted, generating
multiple identity values, @@IDENTITY returns the last identity value
generated. If the statement fires one or more triggers that perform
inserts that generate identity values, calling @@IDENTITY immediately
after the statement returns the last identity value generated by the
triggers. The @@IDENTITY value does not revert to a previous setting if
the INSERT or SELECT INTO statement or bulk copy fails, or if the
transaction is rolled back.

@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions in
that they return the last value inserted into the IDENTITY column of a
table.

@@IDENTITY and SCOPE_IDENTITY will return the last identity value
generated in any table in the current session. However, SCOPE_IDENTITY
returns the value only within the current scope; @@IDENTITY is not
limited to a specific scope.

IDENT_CURRENT is not limited by scope and session; it is limited to a
specified table. IDENT_CURRENT returns the identity value generated for
a specific table in any session and any scope. For more information, see
IDENT_CURRENT.

HTH,

---
Leo Gaggl
Adelaide, South Australia