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

Posted on 2005-04-07
Last Modified: 2010-05-18

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....

Question by:kamleshmistry
    LVL 75

    Accepted Solution

    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"
    LVL 75

    Assisted Solution

    by:Anthony Perkins
    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.

    LVL 4

    Assisted Solution

    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.


    Author Comment

    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...

    LVL 75

    Assisted Solution

    by:Anthony Perkins
    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

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now