In what use case would you use CTE instead of a Temp Table

Admin 1
Viewed 205

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?

common-table-expression sql-server sql
1 Answers

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.

user_11440350
Related