How are CTEs not better than a temp table?

So, CTEs (Common Table Expressions) are fast and give you flexibility. This is a fact. (They operate in memory, which gives faster access and so on.) Here is why, though, CTEs remind me a bit of a three-wheeler sports car: it might be lighter and faster on a straight line, but when it comes to taking sharp turns… maybe not so great. 🙂 So, imagine the following scenario, where you have the task to refactor some of your code and turn some temp tables in CTEs. It starts off good, because not all of them are involved in a if…else statements.

The logic goes like this: 

1. give me this data subset (here is where you get the subset in a temptable originally)
2. if @parameter is = to something then show me part of the data subset, else show me other part (here is where you would have the if… else statement and you will be selecting from the temptable in either case of the parameter value) Now, the CTE – it is quite clumsy in this situation. You have the following options: 1. leave the temp tables in use 2. write a query which involves 2 different CTEs for each case (don’t forget the ; at the end of each)
3. don’t use the if…else statement – write 2 different procedures which have the CTEs returning the data subset according to the different conditions and call the 2 SPs conditionaly from the middle tier (i.e. from your application’s code). (Yes, the last option is a bit extreme, but I can’t say I haven’t seen it often implemented)

There you have it. The choice is yours. 🙂
Feodor Georgiev