Virtual Tables during SQL Compilation

One of my goals this year is to build proficiency in SQL Server - I know my way around it pretty well, but there's a ton of technical detail, tools, etc that I DON'T know about. I've got a big fat book I'm slowly reading, and will try to share some of what I learn here.

SIDE NOTE - alot of these posts are going to ultimately sum up to "This doesn't really add value", or "I didn't find anything hugely interesting here". In order to save you, the faithful reader, some time, I'll try and tell you up front whether you're going to walk away a better person after reading a particular post.

In this post, I'm laying the groundwork for the next post, providing some background information. Probably useful.

A big piece of SQL performance involves how a query is processed logically. In a nutshell, the SQL Server compiler will parse a query into multiple pieces, and construct a series of "Virtual Tables" at each stage of the processing. It's useful knowing how this works - a few reasons being:

1) There are certain SQL rules that are pretty confounding, but make sense in light of how SQL Server is processing the query. For example, "why can't I use the 'MAX' aggregate function in my WHERE clause?"

2) Performance - if you can move certain processing to a logical step that will cut down the processing required by subsequent steps, you can improve performance

3) Weird Results - sometimes, things are included in a result set, when they don't seem like they should be. For example, sometimes the "GROUP BY" clause will toss in a result that seems like it should have been excluded by a WHERE clause.

There's more to this topic than I want to cover here - I didn't see anything on the web, but here's the book if you're interested:

http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735623139/ref=sr_1_6?ie=UTF8&s=books&qid=1267032068&sr=8-6

So one of the big surprises for me was that, for each table you JOIN in a query, SQL Server does a Cartesian Join. Basically, for 2 tables, this means that the number of rows will be:

[# of rows in Table1] x [# of rows in Table2]

If you've got big tables, and queries that need to join alot of them at once, this seems like it would get pretty ridiculous within SQL Server? How does it manage to work with datasets this big? I've looked around on the web a bit for some sort of explanation, but haven't found anything.

My next post is going to contain a test I ran to try and illustrate the performance implications of this Cartesian join. I think I'll also post something to http://stackoverflow.com/ and see if someone there has an answer. Stay tuned...