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?