In this excellent SO question, differences between CTE and sub-queries were discussed.
I would like to specifically ask:
In what circumstance is each of the following more efficient/faster?
- CTE
- Sub-Query
- Temporary Table
- Table Variable
Traditionally, I’ve used lots of temp tables in developing stored procedures – as they seem more readable than lots of intertwined sub-queries.
Non-recursive CTEs encapsulate sets of data very well, and are very readable, but are there specific circumstances where one can say they will always perform better? or is it a case of having to always fiddle around with the different options to find the most efficient solution?
EDIT
I’ve recently been told that in terms of efficiency, temporary tables are a good first choice as they have an associated histogram i.e. statistics.