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.