Article ID: 169642 - View products that this article applies to.
This article was previously published under Q169642
This article has been archived. It is offered "as is" and will no longer be updated.
In determining the existence of useful indexes to resolve a query, SQL Server looks for the search arguments in the query. Search arguments are the arguments in the WHERE clause of a query that help to specify a condition to restrict the result set returned by the query. It is necessary that the arguments in the WHERE clause are of the form "column operator constant". If indexes exist on these columns, the SQL Server optimizer can estimate the selectivity of the index and thereby decide whether or not to use it.
It is desired that the optimizer estimate search argument selectivity based on the distribution information available in the distribution page. Other methods of determining index selectivity for a search argument include using index densities and magic density. Magic density is an estimate of the search argument selectivity that can be used when density or statistics on the distribution page cannot be used. It estimates 10 percent of the rows match for an equality comparison, 25 percent for a between comparison, and 33 percent for greater than, less than, greater than or equal to, and less than or equal to comparisons.
Scoring an index is the process of estimating the usefulness of the index for the search argument in the WHERE clause. Distribution steps are maintained only for the first column of the index specified. A valid search argument is of the form "column operator constant". Any operator that is valid on the column specified can be used. Invalid search arguments may prevent the index from being used.
To allow the optimizer to do index scoring for a search argument based on the distribution page, the following rules on search arguments may be helpful:
Article ID: 169642 - Last Review: October 7, 2013 - Revision: 3.0