If you go around to the various forums, you’ll see postings, including some I’ve put up, that say using subqueries, especially correlated subqueries in the SELECT statements of queries is bad because it effectively acts as a cursor.
I got called on it. So I had to do a bit of research.
Books Online talks about it right off when describing subqueries. That’s not proof though.
I did some more looking around. Adam Machanic does a nice job of slicing up Functions, which are basically the same thing. But it’s not exactly the same.
I kept looking. This blog entry is just flat wrong, but the second comment points out the fallacy. Jeff Moden would also like this example since it shows the disparity between the actual cost of queries and the estimated cost used by the graphical execution plans to display results. But this isn’t in the SELECT statement. Moving on.
Of course. The real answers always come from the SOURCE: Itzik
The ol’ “Hidden RBAR” article also shows the problem in terms of internal rows generated. You can find it at the following URL…
http://www.sqlservercentral.com/articles/T-SQL/61539/