Index Architecture and Behavior - SQL Server Query Performance Tuning - page 116

■ Note Chapter 12 covers statistics in more depth.

To understand the significance of a WHERE clause column in a query, let's consider an example. Let's return to the
original code listing that helped you understand what an index is; the query consisted of a SELECT statement without
any WHERE clause, as follows:

SELECT p.ProductID,
p.Name,
p.StandardCost,
p.Weight
FROM Production.Product p;

The query optimizer performs a clustered index scan, the equivalent of a table scan against a heap on a table that
has a clustered index, to read the rows as shown in Figure 8-6 (switch on the Include Actual Execution Plan option
by using Ctrl+M inside a query window, as well as the Set Statistics I0 option by right-clicking and selecting Query
Options and then selecting the appropriate check box in the Advanced tab).

Figure 8-6. Execution plan with no WHERE clause

The number of logical reads reported by SET STATISTICS I0 for the SELECT statement is as follows:

Table 'Product'. Scan count 1, logical reads 15

To understand the effect of a WHERE clause column on the query optimizer's decisions, let's add a WHERE clause to
retrieve a single row.

SELECT p.ProductID,
p.Name,
p.StandardCost,
p.Weight
FROM Production.Product AS p
WHERE p.ProductID = 738 ;

With the WHERE clause in place, the query optimizer examines the WHERE clause column ProductID , identifies the
availability of the index PK_Product_ProductId on column Productld , assesses a high selectivity (that is, only one
row will be returned) for the WHERE clause from the statistics on index PK_Product_Productld , and decides to use that
index to retrieve the data, as shown in Figure 8-7 .