Performance between CTE vs Temp table

Question

Hello ,
Just wanted to know if we have any performance benefits of using the CTE over temp tables ?

Thanks
Himanshu

Answer 1

That is very much an It depends question.

If you write a query where you store an intermediate result in a temp table, that comes with over to materialise the result. If you instead make that into a CTE, SQL Server may find a way to recast the computation order for a lot more efficient query plan. You should keep in mind that the CTE is a logical construct.

But it can just as well be the other way round. That intermediate temp table has statistics, and this can help the optimizer to make a better job with the rest of the query.

When I write queries, I tend to start out using a CTE, but if I find performance is bad, I may try to break up the query with an intermediate temp table.

Answer 2

Hi @Himanshu Sinha

In SQL Server, the performance benefits of using CTE over temporary tables depend on the scenario.

For small datasets or one-time queries, CTEs are often more efficient because they don't require physical storage and index maintenance. However, for large datasets or multiple references, temporary tables can significantly improve performance through indexing and physical storage.

While using temporary tables, it supports indexes and statistics, which is suitable for complex queries or multiple reference scenarios, and the optimizer can generate efficient plans based on statistics. On the other hand, CTEs are inline views, which the SQL Server optimizer may incorporate into the main query to produce a better execution plan.

Also, CTE is preferred in recursive scenarios, and its built-in recursion mechanism is more efficient than the circular implementation of temporary tables.

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".