Consider scenario where I am creating multiple intermediate tables in CTEs before joining the last intermediate result to some other table. The intermediate results can use joines as well.

The alternative is that I rewrite the code so that each intermediate CTE gets its own table with clustered index on columns required for efficient subsequent joins. I would expect queries on tables with indexes to perform faster.

I understand the intermediate CTE results are written to disk anyway, so I/O constraints should not be considered here, right?

Let’s assume we are considering joining tables with 50m-500m of rows in SQL Server.

  • 1
    CTE results are not written anywhere. They're like temporary views. There is nothing physical about them. – Erik Darling Jul 14 '18 at 19:44

A CTE is just a disposable view. As Erik commented, CTE results are not stored to disk.

In terms of perf, a temp table has many advantages of creating indexes, stats, etc which will outperform CTE when dealing with large tables and complex queries.

When we migrated from sql server 2008 to 2014 (SP2+CU8), I saw queries performing poor (generating bad query plans) and we had to change them to use temp tables.

So - based on my experience - if you need speed, go with temp tables (they are not free as depending on how much data you are dealing with, it hits temp table and memory).

Read Martin's answer at : Which are more performant, CTE or temporary tables?

| improve this answer | |
  • 2
    "I fixed slow CTE queries by materializing intermediate results" is fine. But it does not follow that "if you need speed, go with temp tables". Many (most?) CTE queries are faster without materializing intermediate results. As a DBA, if it works fine you never have to look at it, so it's easy to have your intuition be influenced by Selection Bias. – David Browne - Microsoft Jul 14 '18 at 22:27
  • "CTE results are not stored to disk." They might be, if the results are too big to fit in memory, right? – ypercubeᵀᴹ Jul 15 '18 at 0:07
  • Cte query access the base tables. Is that what you are asking ? – Kin Shah Jul 15 '18 at 0:33
  • CTE results are typically not materialized at all, let alone stored to disk. Even Temp Tables and Table Variables are not always stored to disk. – David Browne - Microsoft Jul 15 '18 at 19:06

Not the answer you're looking for? Browse other questions tagged or ask your own question.