Databases Reference
In-Depth Information
To see an example, run this query, which creates the plan shown in Figure 3-9:
SELECT
*
FROM
Sales
.
SalesOrderDetail
WHERE
OrderQty
*
UnitPrice
>
10000
Listing 3-32.
Figure 3-9:
Cardinality estimation example using a 30% guess.
The estimated number of rows is 36,395.1, which is 30% of the total number of rows,
121,317, although the query returns only 772 records. SQL Server is obviously using a
selectivity guess, as it cannot estimate the selectivity of the expression
OrderQty
*
UnitPrice
> 10000.
Now create a computed column:
ALTER
TABLE
Sales
.
SalesOrderDetail
ADD
cc
AS
OrderQty
*
UnitPrice
Listing 3-33.
Run the previous query in Listing 3-32 again, and note that, this time, the estimated
number of rows has changed and is close to the actual number of rows returned by the
query, as shown in Figure 3-10 (this plan shows the estimated number of rows as in SQL
Server 2008; statistics for SQL Server 2008 R2 will have minimal differences for a default
