Simple Analogy
Imagine you’re a chef preparing dishes for a party.
🍲 Using a Temp Table
You chop a big bowl of onions once and keep them in a bowl (#TempTable).
Then, whenever a recipe needs onions, you just scoop some out.
✅ Efficient and fast — prep once, reuse many times.
🍳 Using a CTE
Instead of keeping the chopped onions, you follow the “chop onions” step every single time a recipe calls for onions.
❌ Wasteful — you repeat the same work multiple times.
IMPORTAN NOTE: A CTE that is created exists only for the duration of the execution of one statement and within the scope of that statement. So if you are executing multiple statements within a query the use of CTE can bloat up the memory resources used.
Going back to the chef analogy, there is one instances where the CTE option of “chopping onions” step every single time trumps the temp table option of chopping a big bowl of onions once and keeping them in a bowl. That is, if you cooking just one dish.
In SQL terms, if your derived data is being used just once, then it does not make sense to create a temporary table.