One annoyance about the query optimizer in SQL Server is that sometimes the prettier solutions perform far worse, eg. sometimes ugly procedural T-SQL with temp table use etc can perform far better than a single, succinct SQL SELECT statement using CTE's etc..

I think the optimizer could do a better job with CTE definitions in sql server..

Or maybe its just my queries 😛