Menu

CTE vs Temp Table Performance Example

Reading 0:00
15s threshold

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).…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.