>As I was saying, in an awfull lot of case you just do not need CTE's as there is a better alternative.
>
>Aside from that, CTE's cannot be used as a total replacement of temp tables which are dirived from more complex statement as you cannot nest CTE's and cannot use two CTE's in one SQL statement.
>
>In my carreer I've used CTE's only in a handful of cases, but only as a last resort. Esspecially the recursive variant is sometimes very difficult to follow and therefore hardly maintainable. I appreciate people use it to show their capabilities, but to me, simplicity and maintainability is worth much more than to score with a SQL statement that can only be analysed and maintained by high skilled SQL programmers.

This statement is incorrect. You can nest CTEs and you can use more than 1 of them in the final statement.