locked
t-sql OUTPUT clause vs. scope_Identity() RRS feed

  • Question

  • It is common practice to use scope_identity() to return the identity value of a newly inserted record in a table with an integer identity primary key. The idea was that the identity returned was the one generated by your session regardless of any other processes that may be inserting data into the same table at that time.  I'm under the impression that OUTPUT is as safe, as well as more flexible option, that can achieve the same result. Do you agree?

     

    As an example,

    >>>>> 

    create table myTable (

        Id int identity(1,1) primary key clustered,

        myData varchar(50) not null

        );

     

     

    create table #myTempTable (

                                    i int not null

                    );

     

     

    insert myTable (myData)

    output inserted.Id into  #myTempTable

    values ('MyData');

     

    select * from #myTempTable

    >>>>> 

     

    the last Select returns the values of all Identities INSERTEd by your current session, regardless of any other sessions' Insert statements into that table.

     

    TIA,

    Barkingdog

     

     

    Thursday, February 24, 2011 4:02 AM

Answers

  • Yes, the OUTPUT clause is a very good alternative to SCOPE_IDENTITY and even
    better. It can return the new values when you insert multiple rows. On top of
    that in SQL Server 2011 (Denali, still in CTP) we have the new sequences that
    are better alternative to IDENTITY, for which SCOPE_IDENTITY does not work. But
    you can use the OUTPUT clause to retrieve it (although you can obtain the new
    sequence value even before the insert).
     

    Plamen Ratchev
    • Marked as answer by Kalman Toth Tuesday, March 1, 2011 11:48 PM
    Thursday, February 24, 2011 4:13 AM
  • The OUTPUT is the safest mechanism. I've seen reports in this forum about problems with SCOPE_IDENTITY(). Try searching in this forum (and the issue was also on MS Connect)
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Kalman Toth Tuesday, March 1, 2011 11:48 PM
    Thursday, February 24, 2011 4:08 AM

All replies

  • The OUTPUT is the safest mechanism. I've seen reports in this forum about problems with SCOPE_IDENTITY(). Try searching in this forum (and the issue was also on MS Connect)
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Kalman Toth Tuesday, March 1, 2011 11:48 PM
    Thursday, February 24, 2011 4:08 AM
  • Yes, the OUTPUT clause is a very good alternative to SCOPE_IDENTITY and even
    better. It can return the new values when you insert multiple rows. On top of
    that in SQL Server 2011 (Denali, still in CTP) we have the new sequences that
    are better alternative to IDENTITY, for which SCOPE_IDENTITY does not work. But
    you can use the OUTPUT clause to retrieve it (although you can obtain the new
    sequence value even before the insert).
     

    Plamen Ratchev
    • Marked as answer by Kalman Toth Tuesday, March 1, 2011 11:48 PM
    Thursday, February 24, 2011 4:13 AM