A bit of a general question really - does SQL abdicate it's responsibility in terms of it's language processing? What I'm getting at is that 4GLs such as SQL typically invite programming in terms of what should be done, rather than how it should be done, which is where 3GLs (e.g. Java, C#, C, Latest-programming-language-of-the-day). Given that the invitation is typically to get the user to specify what should be achieved, is it the case that SQL abdicates it's responsibility in terms of optimisation and query plan generation?
As we see time and time again, how you specify what should be achieved can have massive impacts on the performance of the system. Don't get me wrong - I'm happy that's the case, because it means there's an incredible amount of depth to the domain, but I am wondering if really SQL should be aiming at taking that level of understanding and making it much less necessary.
Obviously, rules will always apply for data storage and effective use of the data engine (for example, storing your integers in varchar fields will always be sub-optimal) - but should the differences between the various types of join (and I am meaning MERGE, HASH and LOOP here - not INNER/OUTER/CROSS) become something that we actually don't care about? Should SQL generally be responsible for extracting the meaning from what's been written and creating the optimal execution plan that satisfies that meaning?
I know this is a bit of a waffly question, but it's something I've been thinking about recently and I find all your insights generally help me achieve a balanced view on my thoughts...
Edit -> Re-reading this, yes I am probably being a bit too harsh - but what I'm getting at is a more fundamental question about what SQL is and what SQL isn't, rather than any comment about any particular implementation. Definitely not saying the SQL Server QO sucks, and definitely not saying I could make a better job of it! :)