September 2010
Intermediate to advanced
1704 pages
111h 8m
English
How does the Query Optimizer use the index statistics to estimate the number of rows that match the SARGs in a query?
SQL Server uses the histogram information when searching for a known value being compared to the leading column of the index key column, especially when the search spans a range or when there are duplicate values in the key. Consider this query on the sales table in the bigpubs2008 database:
![]()
Because there are duplicates of title_id in the table, SQL Server uses the histogram on title_id (refer to Listing 34.5) to estimate the number of matching rows. For the value of BI3976, it would look ...