Tuning T-SQL - Professional SQL Server 2005 Performance Tuning - page 297

The plan could be really improved upon if an index could be added. Just adding an index on Order-
Date doesn't really help the CTE, but does allow the optimizer to adjust for the brute force method.
Regardless, the leaf level of the clustered index must be read to retrieve the cost components. If adding
these cost components to the non-clustered index doesn't add too much storage to the index and increase
significantly the number of pages that have to be read on the index, there is a significant advantage in
adding these columns to the index. Since these cost components are unlikely to be search predicates, they
can be added to the leaf level of the index as information only. The index doesn't consider these columns
as keys so this reduces the overhead of including the column in the index statistics. Create a covering
index for the order date like this:

CREATE NONCLUSTERED INDEX [IX_SalesOrderHeader_OrderDate] ON
[Sales].[SalesOrderHeader] ([OrderDate] ASC)
INCLUDE ( [CustomerID],[SubTotal],[TaxAmt],[Freight])

You can see in Table 9-24 that the page reads have dropped significantly. This can be attributed to reading
the non-clustered index leaf instead of the clustered index leaf. Clearly, this is an example of how you
can reduce I/O demands on your SQL Server database.

Table 9-24: Comparison of Aggregate Methods

Method Cost CPU Reads Duration

(Without Index)

Brute Force 2.37388 120 2827 453

CTE 1.2071 101 1410 409

(With Index - No Covering - Still uses clustered index)

Brute Force 1.48929 150 1498 485

CTE 1.2071 110 1422 425

(With Index - Covering)

Brute Force 0.083310 110 46 407

CTE 0.061812 60 92 394

A CTE is not necessary to achieve this I/O reduction. A solution could be coded using a derived table
instead. Simply convert the CTE to a derived table in the FROM statement. However, the execution plan
will remain the same. An advantage to the CTE solution is it may be easier to read, but the best part about
these CTEs is that you can refer to them more than once in a batch. Multi-referencing is not something
you can do with a derived table. This has the fascinating appearance that you can create a derived table
that can be re-summarized while creating the results.

--CTE queried twice
With CustTotals As (