Why are Subqueries Dangerous?

June 13, 2008 at 1:51 pm (TSQL) (, )

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

1 Comment

  1. Jeff Moden said,

    The ol’ “Hidden RBAR” article also shows the problem in terms of internal rows generated. You can find it at the following URL…


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: