Caching intermediary CTEs for multiple uses

I'm working on this huge query that I chose to use CTEs. Due to the complexity of this query, I ended up having to reuse intermediary query results more than once in subsequent queries. The problem I'm facing can be summarized by the following toy code:

WITH cte1 AS (
    SELECT id, name
    FROM Manager)
, cte2 AS (
    SELECT id, name
    FROM Employee 
    LEFT OUTER JOIN cte1 ON Employee.id = cte1.id)
SELECT *
FROM cte1

I have notice an unusual amount of indexes seeks in a fairly small table that was being queried in one the first queries which, according to the execution plan, were taking about 30% of the whole query. This led to me believe the base table was being queried multiple times as it was being referenced more than once in subsequent queries.

I was under the impression that intermediary results in a CTE were cached until the final statement. Reading Microsofts docs on CTEs, I couldn't find any reason to think otherwise.

Are intermediary CTEs cached for muliple uses within an WITH statement? If not, is there a way to cache it without rewriting the query?

Thanks!

No, there is no way to cache a CTE; it will typically be executed multiple times if it is referenced multiple times. If you want to avoid this, you can cache the results using a #temp table instead.

In some cases, the execution plan may be simple enough that the CTE is only materialized once (this doesn't technically mean it was "cached"), or for the results to be reused with a spool. I don't have any examples at hand and, even if I did, you couldn't rely on that to always be the case. Even something as simple as this:

CREATE TABLE dbo.OneTuple(id int PRIMARY KEY);
INSERT dbo.OneTuple(id) VALUES(1);
GO

;WITH cte AS 
(
  SELECT id FROM dbo.OneTuple
)
SELECT id FROM cte
UNION ALL
SELECT id FROM cte;

Yields two identical index scans:

Also see this great Martin Smith answer on Stack Overflow.

StackExchange Database Administrators Q#221791, answer score: 10