Statistics and Cost Estimation - Inside the SQL Server Query Optimizer - page 82

Figure 3-4: Cardinality estimation example using a RANGE_HI_KEY value.
Since 831 is the RANGE_HI_KEY on the second step of the histogram shown in Listing
3-12, the Query Optimizer will use the EQ_ROWS value (the estimated number of rows
whose column value equals RANGE_HI_KEY ) directly, and the estimated number of rows
will be 198, as shown on Figure 3-4.
Now run the same query, with the value set to 828. This time, the value is inside the
range of the second step but is not a RANGE_HI_KEY , so the Query Optimizer uses
the value calculated for AVG _ RANGE_ROWS (the average number of rows per distinct
value), which is 36.6667 as shown in the histogram. The plan is shown in Figure 3-5 and,
unsurprisingly, we get the same estimated number of rows for any of the other values in
the range (except for the RANGE_HI_KEY , obviously). This also includes 829, even when
there are no records for this ProductID value.
Figure 3-5: Cardinality estimation example using an AVG_RANGE_ROWS value.