I am going to proceed to some lengthy and boring esoterica of ORDER BY, but as a reminder, you can influence the choice of topics pretty easily by posting comments with your favorites :-).

This posting is provided "AS IS" with no warranties, and confers no rights.

Earlier, I explained what exactly a query specification is. It is important to note that query specifications come in two very different varieties: grouped and not. A query specification is grouped if it has any of the following: GROUP BY clause, HAVING clause, or aggregates. Roughly speaking, this would mean that the result of the FROM and WHERE clauses gets "compressed": the query may deliver only one, aggregated row for each group of several rows coming out of FROM. What is important to note is that even without an explicit GROUP BY clause, a query specification may still be grouped if HAVING or aggregates are present. If there is no GROUP BY clause, the entire rowset is considered a single group, and the query delivers exactly one row (if not filtered away completely by HAVING).

The SELECT clause is governed by quite different rules in grouped and non-grouped query specifications. The difference of course is that in the grouped query specifications, the SELECT clause is executed after a GROUP BY (and remember, there is *always* a GROUP BY in the grouped query specifications, even if not explicitly specified!). And after your grouping is done, you are in a very different rowset than before the grouping. Generally speaking, all your original columns are gone. An exception is made for columns that you grouped by, because their value within a group is guaranteed to be the same, so they "survive" a GROUP BY operator. Another exception is aggregates. Inside an aggregate, you can use any column freely.

SQL standard (of '92 vintage) allowed only GROUP BY individual columns, i.e. you could say "GROUP BY a, b", but not "GROUP BY a+b". As an implementor, I wish it remained that way :-), because the semantics is pretty clear, and you can still achieve "GROUP BY a+b" functionality by using a derived table. However, it was perceived so convenient, that the ability to group by an arbitrary expression was added as a language feature. Seemingly benign, it actually brings a couple of esoteric issues with it.

First, what would be a valid GROUP BY expression. The standard is pretty clear that the column you are grouping by should not be an outer reference. This makes sense, because an outer reference would be constant across the rowset, and the result of grouping by a constant is a single group - just as though the GROUP BY was not specified at all. While this is not so terrible, it's more likely that this is a coding bug than the real intent. So basically, the idea is to not allow grouping on a constant. So, how do you tell a constant from a non-constant when you have a complex expression? Here is the rule that Yukon Beta2 enforces: each individual GROUP BY expression has to contain at least one column reference, and that column has to be from the local scope (i.e. not an outer reference). Note that this is a little bit tighter than in SQL 2000, where any column reference (even from an outer scope) was enough to make a GROUP BY expression valid.

As an additional restriction, GROUP BY expressions do not allow scalar subqueries or aggregates.

Ok, that should be enough of esoterica for today, I'll leave the other GROUP BY issue for the next post :-)

This posting is provided "AS IS" with no warranties, and confers no rights.

Here is an interesting comment from chrisbro about some features he finds sorely missing in TSQL:

* Can't specify a parameter to be used as an IN clause
* Can't easily specify parameters to be used in the ORDER BY clause; especially painful to do multiple order by clauses

I actually hear quite a bit of requests in the same "parameterization" vein, such as "how come I cannot specify a table name as a parameter?". So let's look under the hood for a moment. Although it looks to the user as a "pure interpreter", TSQL is actually a compiler. During the compilation phase, all tables/columns are fetched from the catalogs, the statement gets type checked, and, most importantly, the query processor comes up with a plan for executing the query. Compilation is a pretty expensive process, so we keep the resulting plan in cache and reuse it if we see the same statement again. So, here is a rule: during compilation, we cannot look at the actual variable or parameter values. Otherwise, we would have to compile the query every time it is executed, because the parameter value may be different. So, we restrict parameters to the relatively "benign" positions where they cannot really influence the data types involved in a statement and the execution plan. This allows us to compile a statement once and reuse the plan for different parameter values (of course I am simplifying a bit here, let me know if more details would be of interest). Now, if you look at, say, ORDER BY clause, the execution plan is actually different (in some cases radically different) depending on what columns you sort on. That's why we don't allow you to parameterize this aspect of the query (IN clause is roughly the same deal). Good workarounds for parameterizing the IN clause are described here. When you want to parameterize your ORDER BY clause, you have multiple options, and which one you choose depends on how flexible you want to be on your ordering options and what your performance requirements are. The most performant solution would be to write several separate queries, each one with the particular flavor of ORDER BY that you want, and then choose one of them using IF statement. If you want to be absolutely flexible, you can use dynamic SQL (i.e. compose your statement on-the-fly and execute it using EXEC). This is slower, and requires you to be careful to avoid SQL injection attacks if you take the ORDER BY list directly from the user input (tip: check that this is indeed a simple ORDER BY list before composing the statement).

So, why cannot SQL Server just implement the above "workarounds" as features and allow parameterizing of ORDER BY clause for the user? One reason is performance. Any generic solution would require essentially generating dynamic SQL under the covers. This would incur serious performance overhead in excess of "regular" parameterization, which would be "unexpected" as opposed to the explicit use of dynamic SQL where you are aware that it has perf penalty. Another reason is that we just cannot implement all features in the world, we have to prioritize them. So, keep posting your wishes, and they may come true in upcoming releases.

This posting is provided "AS IS" with no warranties, and confers no rights.

My name is not actually TSQL language :-), I am Eugene Zabokritski, a Technical Lead working in the TSQL front-end team of the SQL Engine group.

My plan is to post a series of overviews of some TSQL areas that I think are complicated enough to warrant some explanation or discussion. Please feel free to suggest topics of interest. I'll try to provide an implementor's viewpoint and explain some rationale behind even your least favorite features :-). I may also try to answer particular questions about TSQL syntax or semantics, we'll see if the question-answer format will actually work out.

This posting is provided "AS IS" with no warranties, and confers no rights.