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.