You can specify the index to use for a query using the (index index_name) clause in select, update, and delete statements. You can also force a query to perform a table scan by specifying the table name. The syntax is:
select select_list
from table_name [correlation_name]
(index {index_name | table_name } )
[, table_name ...]
where ...
delete table_name
from table_name [correlation_name]
(index {index_name | table_name }) ...
update table_name set col_name = value
from table_name [correlation_name]
(index {index_name | table_name})...
For example:
select pub_name, title
from publishers p, titles t (index date_type)
where p.pub_id = t.pub_id
and type = "business"
and pubdate > "1/1/93"
Specifying an index in a query can be helpful when you suspect that the optimizer is choosing a suboptimal query plan. When you use this option:
Always check statistics io for the query to see whether the index you choose requires less I/O than the optimizer’s choice.
Ttest a full range of valid values for the query clauses, especially if you are tuning queries:
Tuning queries on tables that have skewed data distribution
Performing range queries, since the access methods for these queries are sensitive to the size of the range
Use this option only after testing to be certain that the query performs better with the specified index option. Once you include an index specification in a query, you should check regularly to be sure that the resulting plan is still better than other choices made by the optimizer.
If
a nonclustered index has the same name as the table, specifying
a table name causes the nonclustered index to be used. You can force
a table scan using select select_list from tablename (0).
