Indexes are the database's fast-access path to a table's data. SQL will choose an index when its key components have matching predicates and it is estimated as the least costly access path. An index is usually regarded as least costly when its predicates select the least amount of data.
Cost estimates are most accurate when SQL has good statistics with which to work. Statistics are created by the UPDATE STATISTICS command and its various options.
The most basic statistic is table cardinality. This is vital to join order determination and to estimating the number of rows that will be selected by a set of predicates.
Column statistics give information about the distribution of data within a column. The column statistics are a sample of data across the entire range of a column's data. They especially enable SQL to estimate costs for range predicates, such as BETWEEN, and ">"and "<". They are useful for equality predicates also, but are much less precise than index statistics.
When there are no statistics at all, the optimizer uses certain default values for the selectivity of a column. Selectivity expresses what fraction of a column's data will be selected by some set of predicates.
