Roy Oliver (9/14/2009) Jeff Moden (9/12/2009)
Roy Oliver (9/12/2009)
Interesting... it'd be easier if SQL Server implemented triggers in the same way Oracle did.
Why? They are inherently RBAR. "For Each Row".
True, yet isn't the performance faster than the SQL Server Cursor?
I don't know, Roy. Comparing anything in Oracle with SQL Server is pretty hard to do. A better question might be, are they faster than an Oracle Cursor? ... and the answer is "I don't know for sure in Oracle" because I've never tested triggers vs set-based code in Oracle, but I don't believe so. I can say I have tested Cursors vs Set-Based in Oracle... properly written set-based code blows cursors away even in Oracle. It's pretty much a myth that Oracle has been "optimized" for cursors so far as speed is concerned.
Most RDBMS's work best with Set-Based code... it would be a real shame if they changed the current set-based mechanism built into SQL Server triggers into similar RBAR code as they did in Oracle if for no other reason other than to simply NOT get into the habit of writting RBAR anywhere.
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
How to post code problemsHow to post performance problemsForum FAQs