• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 562
  • Last Modified:

@@Error, @@RowCount, @@Identity....

I often see references to system information such as

@@Identity, etc.

I often see it being used in a way that might not seem completely accurate.

For example, after an Insert operation, you might fetch identity:
Declare @NewlyAddedId int
@NewlyAddedId = @@Identity

Is this guaranteed to get the right answer?

Because several users can be running scripts at the same time.  Who can guarantee that no-one else did an insert operation at about the same time that you did an insert operation?

Or does @@Identity return the identity of the most recently added id that was added by the current script or procedure.   I don't think that is the case, and hence the usage of @@identity and other such system information is a bit bothersome....

  • 3
4 Solutions
Anthony PerkinsCommented:
First of all the syntax is:

Declare @NewlyAddedId int
Set @NewlyAddedId = @@Identity


Declare @NewlyAddedId int
Select @NewlyAddedId = @@Identity

>>Is this guaranteed to get the right answer?<<
That is a loaded question.  If there are no Triggers involved than the answer is yes, regardless of other users.  If there are Triggers involved and you are using SQL Server 2000 you should use the Scope_Identity() function instead to guarantee the "right answer"
Anthony PerkinsCommented:
From BOL:

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions in that they return values inserted into IDENTITY columns.

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

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

For example, you have two tables, T1 and T2, and an INSERT trigger defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 as a result of the trigger.

Assuming that both T1 and T2 have IDENTITY columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1.

@@IDENTITY will return the last IDENTITY column value inserted across any scope in the current session, which is the value inserted in T2.

SCOPE_IDENTITY() will return the IDENTITY value inserted in T1, which was the last INSERT that occurred in the same scope. The SCOPE_IDENTITY() function will return the NULL value if the function is invoked before any insert statements into an identity column occur in the scope.

It is accurate if it is the next call after an insert  in a procedure or if there is a begin and end trans wraping  both intert and  @@Identity commands.

It would not be accurate if you had this code say on a web server and calling sending it to the sql server.

There is also the  SCOPE_IDENTITY and  IDENT_CURRENT
Which may be what your looking for.

kamleshmistryAuthor Commented:
Not sure what triggers have anything to do with it...

a)    ->  Insert a record here

b)   ->  retrieve @@identity here

How is it guaranteed that b) will always execute after a) ?

Maybe some other statement by some other user (say statement c) ) will execute before statement b)

Then it is impossible to say that @@identity 's value will be predictable, should it be the very next line.  It may be PROBABLE, but not guaranteed.  Is this not true?  Not sure what triggers have to do with it....

Yes, but I can appreciate the Scope_Identity function and that might be an acceptable answer.  But I will keep this question open a bit longer for people to reply to my latest comments here...

Anthony PerkinsCommented:
Forget about all the Trigger stuff for now.  To put it simply: The Scope_Identity() function is always guaranteed to return the last Inserted value in the same session.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now