Adam Machanic (7/18/2008)
Jeff Moden (7/18/2008)
Viswanath S. Dhara (7/18/2008)
I am trying to update a table using a correlated subquery.Why? Correlated subqueries are a form of RBAR and can actually be worse than a cursor in some cases. Use a correctly formed joined update instead...
Your "correctly formed" update is non-ANSI Compliant and is nondeterministic in some cases. The query optimizer can--and usually does--optimize out correlated subqueries so that they're not reevaluated on a row-by-row basis. Certainly not worse than a cursor.
That would be true if there is only 1 column being updated... each correlation in an Update in SQL Server 2000 (haven't checked 2k5, yet) adds about the same amount of time.
To be clear, correlated sub-queries can produce performance thousands of times worse than a cursor depending on the usage and the data... please see the following...
http://www.sqlservercentral.com/articles/T-SQL/61539/
Admittedly, that type of correlation won't work in an update and I apologize for confusing the issue...
... And, please tell me that your not one of those folks that writes only ANSI compliant code. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.