Charles Kincaid (12/9/2009)
...Here is the secret, at least to thinking about this. The CTE generates a very temporary table. It only lives for the length of the single statement.
...
Totally wrong! You think that it is building an intermediate result set (temp table) that is being referenced over and over. When in fact it is just taking the entire SQL statement that makes up the CTE and "plugs" it into the main query wherever the CTE is referenced. For proof, just see the query plan. As noted by Tony Rogerson. See: http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/05/17/non-recursive-common-table-expressions-performance-sucks-1-cte-self-join-cte-sub-query-inline-expansion.aspx
So multiple joins to a CTE would be one of the WORST thing you would do.