Aggregate Binding in Visual Studio .NET

Generator QR Code ISO/IEC18004 in Visual Studio .NET Aggregate Binding

Consider the following example against table T1 with columns c1 and c2 , and table T2 with column x of the same data type as T1.c2 : SELECT c1 FROM dbo.T1 GROUP BY c1 HAVING EXISTS (SELECT * FROM dbo.T2 WHERE T2.x > MAX(T1.c2));

SQL Server computes the MAX aggregate in the outer query SELECT c1 FROM dbo.T1 GROUP BY c1 , although it is syntactically located in the inner one. The algebrizer makes this decision based on the aggregate's argument. The bottom line is that every aggregate needs to be bound to its hosting querythe place where it is correctly evaluated. This is known as aggregate binding.

This is perhaps the least obvious of all activities performed by the algebrizer. Let's consider an example against table T1 with columns c1 , c2 , and c3 : SELECT c1 + c2, MAX(c3) FROM dbo.T1 GROUP BY c1 + c2;

The use of the expression c1 + c2 in the SELECT list is legitimate, although had we placed just c1 in this list, the query would have been semantically incorrect. The reason for this is that grouped queries (for example, those with an explicit GROUP BY or HAVING clause) have different semantics than nongrouped ones. In particular, all nonaggregated columns or expressions in the SELECT list of a query with GROUP BY must have a direct match in the GROUP BY list, and the process of verifying this fact is known as grouping binding . Unfortunately, explicit clauses such as GROUP BY or HAVING aren't the only factors that might force a SELECT list to become grouped. According to SQL's rules, just the presence of an aggregate function that binds to a particular list makes that SELECT list grouped, even if it has no GROUP BY or HAVING clauses. Here is a simple example: SELECT c1, MAX(c2) FROM dbo.T1;

This is a grouped SELECT, because there is a MAX aggregate. Because it is a grouped SELECT, the use of the nonaggregated column c1 is illegal and the query is incorrect. An important role of the algebrizer is to identify any semantic errors in the statement. The following example shows that this is a nontrivial task for some queries with aggregates: SELECT c1, (SELECT T2.y FROM dbo.T2 WHERE T2.x = MAX(T1.c2)) FROM dbo.T1;

This query is incorrect for the same reason as the previous one, but it is clear that we have to complete aggregate binding for the entire query just to realize this. The MAX(T1.c2) in the inner query must be evaluated in the outer query much as it was in the query SELECT c1, MAX(c2) FROM dbo.T1; just shown, and therefore, the use of the nonaggregated column c1 in the SELECT list is illegal and the query is incorrect.

One of the most important and complex components involved in processing your queries is the query optimizer. The optimizer's job is to produce an efficient execution plan for each query in a batch or a stored procedure. The plan lists the steps SQL Server has to carry out to execute your query, and it includes such information as which index or indexes to use when accessing data from each table in the query. The plan also includes the strategy for processing each join operation, each aggregation, each sort, and each partitioned table access. The plan shows an intent to perform operations on parallel threadsthat is, where the row streams are partitioned, repartitioned, and then merged into a single stream. SQL Server's query optimizer is a cost-based optimizer, which means that it tries to come up with the cheapest execution plan for each SQL statement. The cost of the plan reflects the estimated time to complete the query. For each query, the optimizer must analyze the possible plans and choose the one with the lowest estimated cost. Some complex statements have millions of possible execution plans. In these cases, the query optimizer does not analyze all possible combinations. Instead, it tries to find an execution plan that has a cost reasonably close to the theoretical minimum. Later in this section, I'll explain some ways the optimizer can reduce the amount of time it spends on optimization. The lowest estimated cost is not necessarily the lowest resource cost; the query optimizer chooses the plan that most quickly returns results to the user with a reasonable cost in resources. For example, processing a query in parallel (using multiple CPUs simultaneously for the same query) typically uses more resources than processing it serially using a single CPU, but the query completes much faster in parallel. The optimizer will propose a parallel execution plan to return results, and SQL Server will use such a parallel plan for execution if the load on the server is not adversely affected. Optimization itself involves several steps. The trivial plan optimization is the first step. The idea behind trivial plan optimization is that cost-based optimization is expensive to initialize and run. The optimizer can try many possible variations in looking for the cheapest plan. If SQL Server knows by investigating the query and the relevant metadata that there is only one viable plan for a query, it can avoid a lot of the work required to initialize and perform cost-based optimization. A common example is a query that consists of an INSERT with a VALUES clause into a table that does not participate in any indexed views. There is only one possible plan. Another example is a SELECT from single table with no indexes and no GROUP BY. In these two cases, SQL Server should just generate the plan and not try to find something better. The trivial plan the optimizer finds is the obvious plan, and usually it is very inexpensive. Later in the chapter, I will show how to determine whether the optimizer produced a trivial plan for a particular query. If the optimizer doesn't find a trivial plan, SQL Server will perform some simplifications, which are usually syntactic transformations of the query itself, to look for commutative properties and operations that can be rearranged. SQL Server can perform operations that don't require considering the cost or analyzing what indexes are available but that result in a more efficient query. An example of simplification is to evaluate simple single table where filters before the joins. As described in 1 , the filters are logically evaluated after the joins, but evaluating the filters before the joins produces correct result as well and is always more efficient because it removes unqualified rows before the join operation. Another example of simplification is transforming outer joins into inner joins in some cases, as shown in Figure 2-6 . In general, an outer join will add rows to the result set of an inner join. These additional rows have the NULL value in all columns of the inner set if there is no inner row satisfying the join predicate. Therefore, if there is a predicate on the inner set that disqualifies these rows, the result of the outer join is the same as the inner join and it will never be cheaper to generate the outer join result. Therefore, SQL Server changes the outer join to an inner join during simplification. In the following OUTER JOIN query, the predicate Products.UnitPrice > 10 disqualifies all additional rows that would be produced by the OUTER JOIN, and therefore, the OUTER JOIN is simplified into an INNER join: USE Northwind;

SELECT [Order Details].OrderID, Products.ProductName, [Order Details].Quantity, [Order Details].UnitPrice FROM dbo.[Order Details] LEFT OUTER JOIN dbo.Products ON [Order Details].ProductID = Products.ProductID WHERE Products.UnitPrice > 10;