October 19, 2009 at 7:39 am
Hi,
We've got a issue we'd like to solve...
We have several tables that have identity columns and need to get the value into a variable...
There are 3 possible ways to do it (probably more but I can only recall these):
1 - use @@IDENTITY --> VERY BAD... due to multiple transactions running at the same time...
2 - SCOPE_IDENTITY() --> according to the documentation it returns the last identity on the scope. but what's the scope? the session? aren't session shared when using connection pooling with ASP.NET?
3 - OUTPUT INSERTED.[column] --> insert the value into a table variable and get it into a variable. this is "safer" but probably "heaviest" cause it uses a table variable...
Right now the application is using method 1, the stores were made by developers and not DB developers, and now the DB team is analyzing and changing the stores.
Probably method 1 and 2 work fine if using a proper transaction isolation level.. due to performance we are using READ UNCOMMITED, so that transactions don't block themselves.
What's you opinion?
Thanks,
Pedro
October 19, 2009 at 7:43 am
There are parallelism bugs with scope_identity():
http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=328811
so use OUTPUT.
October 19, 2009 at 10:54 am
I read that article also...
That's why I was suspicious about SCOPE_IDENTITY()...
And probably with connection pooling the "share" problem also happens.
Thanks,
Pedro
October 19, 2009 at 8:15 pm
You say "probably" heaviest, which means you haven't tested it.
Why not set up a test of 100000 inserts into a dummy table and see how much of a difference between OUTPUT and @@IDENTITY ??
It shouldn't take but about ten minutes to set up and then you will know if you are worrying about something substantial or not.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 20, 2009 at 2:33 am
Performance here isn't really an issue, since this has to do with getting ids from a payment tables and inserting relational data on another table.
Safety and reliability is the main issue here, and since @@IDENTITY can "loose", "switch" values when having multiple transactions at the same time and SCOPE_IDENTITY() also has a bug, the only "logical" method is the OUTPUT inserted.
I did the test and @@IDENTITY took about 25secs getting the id, and OUTPUT into a table variable and getting the value into a variable took 45secs, inserting 100.000 records.
Thanks U all,
Pedro
October 20, 2009 at 8:06 am
I like the OUTPUT mechanism for batch inserts, but if you're doing single row inserts, you should be fine with SCOPE_IDENTITY() in almost all cases. If you read through the mechanisms that bring about the bug, it's not exactly a common circumstance. I wouldn't toss the baby with the bathwater.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 20, 2009 at 8:48 am
I prefer using the Output clause these days. SO much more useful, and I don't have to worry about using one method for single-row CRUD and another for multi-row. Ideally, all database code should be designed to deal with multi-row actions. Also allows for non-identity columns.
It's mainly so I don't have to use different methods in different cases. Makes for a more maintainable database, because of more consistency.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy