Very good point indeed. I'll add to that, that you can trivially examine temporary tables wheres CTEs don't have that transparency. Thanks! Edit: I'm going to

Very good point indeed. I'll add to that, that you can trivially examine temporary tables wheres CTEs don't have that transparency. Thanks! Edit: I'm going to clarify this. It is a mistake to throw CTEs at a beginner but to be clear, CTEs have very important advantages over the using temp tables to hold intermediate results. It comes down to efficiency. MSSQL only optimises within a single statement; it does not optimise across statements, so if you have a several queries comprising a single CTE, the optimiser has plenty to get its teeth into and may produce a much more efficient query plan. Also a query plan of a complex CTE can (depending on what you're doing) end up being a straightforward pipeline where one result feeds into another into another and finally gets spat out at the end. That can be very efficient. If you use temp tables you spool intermediate results (which may be large), re-read, spool into another temp table etc. If you're working with a large data set that can use up a lot of memory, and if it's large enough that it has to spill to disk... oh dear. So for beginners, yes, as you get more expertise, CTEs are the way to go (depending, of course, on various factors) (Conversely, temp tables do have a definite cardinality, whereas queries in a CTE are estimates and can be badly out leading to very poor query plans. So temp tables can work to your advantage here).