February 23, 2011 at 9:00 pm
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
February 23, 2011 at 9:50 pm
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/SQL/feedback/ViewFeedback.aspx?FeedbackID=476577
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply