CTE’s vs Temp tables – an Opinion

This is one of those topics that will get people fired up. But here goes.

I am mostly an explicit temp table developer. By contrast, I am not an implicit temp table developer. What I mean by that is – in writing SQL, you simply cannot avoid the use of tempdb. Either you write to tempdb by breaking queries out and intentionally create temp tables, or you write to tempdb by not breaking queries out (keeping them as long convoluted statements with a long convoluted query plan) and let the optimizer create “worktables”. In either case you are writing to tempdb whether you like it or not. Yet.. the difference is this:

Breaking them out:

  1. You can control the size of the result set being written to disk
  2. You can ensure that the execution plan is simple
  3. You can utilize the materialized temp table data throughout the entire procedure
  4. Temp tables contain statistics and can be indexed

To compare temp table development to CTE development is somewhat of an apples and oranges comparison. A CTE uses nothing special on the back end. It is simply a (potentially) clean way to write a query. The difference is this however. With a CTE, the execution plan of the main query becomes intertwined with the CTE, leaving more room for the optimizer to get confused. By contrast, when a temp table divides two queries, the optimizer is not intertwined with that which created the temp table and the execution plans stay simple and decoupled from each other.

There are also situations where you cannot just swap a CTE for a temp table. You want to write as little as possible to disk. So when you break the queries out, you want to do it in a way that limits the result sets as much as possible (while keeping the plan as simple as possible) so you do not write a lot to disk. Keeping that in mind, the same query techniques are not interchangeable.

Long story short, there is a game in SQL Server called “choose your bottleneck”. There is always a bottleneck, which do you want it to be? Do you want to be a slave to the optimizer by hoping it generates a good plan for a complicated query? Or would you rather create simple queries so you don’t have to be woken up at 2am because of 200 waiting tasks because one of the most called procs caught a bad plan? I choose keep the plan simple. Break up the queries. Mitigate the writes by limiting the result set. And get some sleep.

6 comments
get a research paper written 18 Nov 2018 at 4:33 pm

get a research paper written

blog topic

Julian Meers 14 Nov 2018 at 2:18 pm

Often I find CTEs more scalable actually – the optimizer produces a better plan if the table sizes change.
EXCEPT
1. if the CTE calls table valued functions
2. if recursive CTEs are in use
The reason being that the optimizer produces poor cardinality estimates for the output of TVFs and recursive CTEs.

So:
– keep CTEs small and simple to aid readability
– If your sub-query calls CTEs returning more than a few rows, or is recursive and returns more than a few rows, use intermediate temp tables (or table variables – little to choose between temp tables and table variables)

WoundedEgo 23 Mar 2015 at 4:41 pm

What about subqueries and cross applies?

Rob StGeorge 09 Jan 2015 at 3:24 am

I have to agree with you, I spend a lot of time taking queries that have been written with nested CTEs and use temp tables instead. 9 times out of 10 I get a huge performance improvement, and more scalable code.

Sukhjinder 30 Oct 2014 at 10:47 am

Excellent comparison and useful content

SQLServer.info 01 Oct 2014 at 3:08 am

Personally, I choose the temp table option. Actually, I choose table variables over temp tables. One of my big issues with CTEs is that I think they are hard to read in SSMS. It almost is counter intuitive to how my brain thinks a query should look. I agree with you!

Featured Articles

 Site Author

  • Thanks for visiting!
css.php