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.