2

I use temporary tables in just about every scenario and when people bring up CTE to me it's typically something along the lines of CTE has it's uses. What are the uses where CTE is the go to over other methods? As for readability are CTEs now best practice for maintainable code?

  • https://www.brentozar.com/archive/2019/06/whats-better-ctes-or-temp-tables/ – Squirrel Mar 15 '21 at 01:32
  • 3
    They are often just syntactical sugar e.g. they are more readable than the equivalent sub-query. But they do allow multi-use of a single sub-query i.e. self-joins etc. And of course recursive CTE's can do some pretty cool stuff. A single execution plan is generated for a CTE, so when a complex CTE starts performing badly often the fix is to materialise to a temp table which then splits the execution plan into 2. – Dale K Mar 15 '21 at 01:32
  • 2
    I suppose the better question is why use temp tables? It makes the query more complex, enforces particular join orderings and often results in unnecessary writes – Charlieface Mar 15 '21 at 01:37
  • Charlieface's link explains it the best to me, not the selected answer or the highest voted. The whole collection of answers paints a picture of when and where to use CTEs. They are not a silver bullet as some believe it really depends on the situation. –  Mar 16 '21 at 02:06

1 Answers1

5

A temporary table incurs overhead for writing and reading the data. In doing so, they have two advantages:

  • They can be used in multiple queries.
  • The optimizer has good information about them, namely the size.

On the other hand, CTEs are available only within one query -- which is handy at times. They can simplify a single query -- and put all the logic together. Unlike a subquery, a CTE can be referenced multiple times in the query. And unlike a temporary table, the optimizer can figure out the best way to incorporate the logic into the queries.

CTEs are also capable of one thing that temporary tables cannot do: recursive CTEs would require looping in a scripting language.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786