>>>

>>>WITH w AS (
>>>    SELECT key, very_expensive_function(val) as f FROM some_table
>>>)
>>>SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;
>>>

>>
>>First of all this type of query falls into my category 2 where you use a subquery multiple times in which I outlined a CTE has the advantage you can use the definition multiple times in the same query, thus cutting code and points of failure.
>>
>>That being said, SQL server does not materialize the subquery and therefore writing it as a CTE does not avoid the very_expensive_function to be called for every instance you use it. So substituting W1 and W2 with the CTE most likely will produce the very same execution plan and thus the same performance.
>>
>>

>>SELECT * 
>>    FROM (SELECT key, very_expensive_function(val) as f FROM some_table) as W1
>>         INNER JOIN  (SELECT key, very_expensive_function(val) as f FROM some_table) as W2 ON W1.f = W2.f
>>

>>Which is perectly readable to me. For me its better than the CTE version.
>>
>
>Yes, you could do that, but I don't see how you can argue that it's more readable. In your example, the reader has to compare the two subqueries and realize that they're the same and that the query is a self-join. With a CTE, that's apparent. In this particular case, the subquery is fairly short, so the comparison isn't that hard, but it's still more effort than seeing the same name appear on both sides of the join.

Until that name is "FOO" and you have to scroll up two pages to read and memorize its definition before going back to the bottom.

Walter,