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


Relevant Pages

  • Untold(...) variable scope in ASP.NET
    ... We all know that declaring a variable as 'Public' gives it global ... scope over the application, nothing new... ... I usually dont like to create session variable because i found them ... scope goes beyong the user session, actually you are better off using ...
    (microsoft.public.dotnet.languages.vb)
  • Re: How Can I Define Global Classes In Web Application ?
    ... which refers to scope. ... the child might think of "the world" as their neighborhood. ... This is entirely different than "Session Scope" which is restricted to those ... FTP Client Classes, Enhanced Data Controls, much more. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Setting the properties of a javabean in JSP
    ... It depends on the scope where the bean lives. ... containers that do any kind of load balancing or session migration. ...  For entity-like objects such as the one under ...
    (comp.lang.java.programmer)
  • Re: pinball league now forming, shark club howell michigan
    ... How are the seeding tie breakers determined from session to session? ... league meeting #3, which is september 10th. ... mike magyari= 49 ...
    (rec.games.pinball)
  • Re: ballantine 1010a oscilloscope
    ... Thanks, Bob... ... Mike ... Would anyone have a manual on this scope? ... > rotate carefully with power on, and tighten again with power off. ...
    (sci.electronics.basics)