t-sql OUTPUT clause vs. scope_Identity()

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

    TIA,

    Barkingdog

  • Using the OUTPUT clause is a much better option - I can't remember the last time I used SCOPE_IDENTITY, @@IDENTITY, or IDENT_CURRENT. Not only is OUTPUT more flexible (you can insert a set of values, rather than operating row-by-row, for example) it avoids bugs found with SCOPE_IDENTITY, @@IDENTITY, and IDENT_CURRENT:

    http://connect.microsoft.com/SQLServer/feedback/details/328811/scope-identity-sometimes-returns-incorrect-value

    http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=476577

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply