it chooses an index based on which index will provide the best performance
- usually the index that will use the fewest I/O operations and retrieve the least number of rows.
Although Query Optimizer usually chooses the most efficient query execution plan
and access path for your query, you might be able to do better
if you know more about your data than Query Optimizer does.
For example,
suppose you want to retrieve data about a person named "Smith"
from a table with a column listing last names.
Index statistics generalize based on a column.
Suppose the statistics show that each last name appears three times on average in the column.
This information provides fairly good selectivity;
however, you know that the name "Smith" appears much more often than average.
If you have an idea about how to better conduct an SQL operation, you can use a hint.
A hint is simply advice you give to Query Optimizer
specifying that it should not make the automatic choice.
Several types of hints are available including
* join hints,
* query hints,
* and table hints
but here we are most interested in table hints.
Table hints let you specify how the table is accessed.
A table hint can be used to specify the following information:
* Table scan
In some cases, you might decide that a table scan would be more efficient
than an index lookup or an index scan.
A table scan is more efficient
when the index scan will retrieve more than 20 percent of the rows in the table
- such as when 70 percent of the data is highly selectable and the other 30 percent is "Smith."
* Which index to use
You can specify a particular index to be the only index considered.
You might not know which index SQL Server Query Optimizer would choose without your hint,
but you feel the hinted index will perform best.
* Which group of indexes to select from
You can suggest several indexes to Query Optimizer,
and it will use all of them (ignoring duplicates).
This option is useful when you know that a set of indexes will work well.
* Locking method
You can tell Query Optimizer which type of lock to use
when it is accessing the data from a particular table.
If you feel that the wrong type of lock might be chosen for this table,
you can specify that Query Optimizer should use a row lock, a page lock, or a table lock.
Source:
Microsoft SQL Server 2000 Administrator's Companion eBook