>That is where your idea is basically lacking. You think CTEs are constrained to MS SQL Server and I said before it is in ANSI SQL, not specific to MS SQL Server, and actually there are deviations between implementations.
>
For me embolded part is the strongest reason to hesitate on using CTEs.
Whenever code repetitions occur, measures should be taken to make code DRY again.
My mental picture of CTE is an analogue of breaking common code out of a long function into a subroutine, but in the SQL/database context - where benefits and drawbacks exist, but benefits usually are greater. Knowing that different backends might show drastic differences in optimizer results is a drawback. In a few years optimizers generate comparable query times, but IMO not today.

But Walter already acknowledged benefit in recursive/reusing queries - So much of the argument is based on things in the eye of the beholder. I will not get into an argument on beauty ;-)