tldr: Examples of CTE vs. temp table query where temp tables perform much better than CTEs. See the query examples at the end of the post.
caveat: I'm not saying CTEs are bad, they have their place, but different situations require different solutions.
The long read:
Lurking around the various SQL subreddits, I am constantly encountering questions about any combination of: CTEs vs Subqueries vs temp tables vs table variables. Other question variations revolve around how best to write or tackle long and/or complex queries or procedures.
Many responses extoll the virtues of CTEs, particularly over subqueries, but the discussion of #temp tables is often lacking. People also neglect to discuss the quirks (or features) of CTEs that can compromise performance (not part of my scope here).…