I have used the query designer extensively in the past, and it works with a number of different types of queries and with CTEs, "derived tables" and other T-SQL features (though not optimally) if you are using a recent version. I have recently started a new job, however, where the performance demands on the databases are much higher, and I have not opened the query designer once. The main issues are:
- The resultant code can be weirdly arranged, and hard to fix (it may even re-arrange things back where they were before you fixed it)
- It sometimes "optimizes" queries to the point of breaking them. Not all of the rearrangements that it applies are valid.
- It's not useful for complex queries containing embedded comments that you want to keep.
It is still potentially a useful tool for diagramming complex queries, although I find that with practice I really don't need it for that, day to day. The key to using it with derived tables is to select just the derived table query itself and open the query designer against that. You can likewise open it against the query contained in a CTE -- it works against any selection that is a valid T-SQL query (among those CRUD query types that it supports but not, for example, MERGE).