Performance characteristics of T-SQL CTEs

Question

I've got some SQL that looks roughly like this:

with InterestingObjects(ObjectID, OtherInformation, Whatever) as (
    select X.ObjectID, Y.OtherInformation, Z.Whatever
    from X join Y join Z -- abbreviated for brevity
)

-- ...long query follows, which uses InterestingObjects in several more CTEs,
-- and then uses those CTEs in a select statement at the end.

When I run it, I can see in the execution plan that it appears to be running the query in the CTE basically every single time the CTE is referenced. If I instead create a temp table #InterestingObjects and use it, of course, it runs the query once, puts the result in the temp table, and queries that from then on. In my particular instance, that makes the whole thing run much faster.

My question is: Is this always what I can expect from CTEs (not memoizing the results in any way, just as if it were inlining the query everywhere?) Is there a reason that SQL Server could not optimize this better? Usually I am in awe at how smart the optimizer is, but I'm surprised that it couldn't figure this out.

(edit: BTW, I'm running this on SQL Server '08 R2.)

Answer 1

CTE's can be better or worse, just depending on how they're used (involving concepts of recursion, indexing, etc.). You might find this article interesting: http://www.sqlservercentral.com/articles/T-SQL/2926/