A special clause, (index index_name), for the select, update, and delete statements allows you to specify an index for a particular query. The syntax is:
select select_list
from table_name
(index index_name)
[, table_name ...]
where ...
delete table_name from table_name (index index_name) ...
update table_name set col_name = value
from table_name (index index_name) ...
Here's an 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.
Be sure to test a full range of valid values for the query clauses, especially if you are tuning range queries, since the access methods for these queries are sensitive to the size of the range. In some cases, skew of values in a table or out-of-data statistics may be other causes for apparent failure to use the correct index.
Use this option only after testing to be certain that the query performs better with the specified index option. Once you include this index option in applications, you should check regularly to be sure that the resulting plan is still superior to other choices that the optimizer makes.
If you want to force a table scan, use the table name in place of index_name.
Note: If you have a nonclustered index with the same name as the table, attempting to specify a table name causes the nonclustered index to be used. You can force a table scan using select select_list from tableA (0).
Specifying indexes has these risks:
Changes in the distribution of data could make the forced index less efficient than other choices.
Dropping the index means that all queries and procedures that specify the index print an informational message indicating that the index does not exist. The query is optimized using the best available index or other access method.
Maintenance costs increase, since all queries using this option need to be checked periodically. Also, future releases of SQL Server may eliminate the problems which led you to incorporate index forcing, so all queries using forced indexes should be checked when new releases are installed.
Before specifying an index in queries:
Check showplan output for the "Keys are" message to be sure that the index keys are being used as expected.
Use dbcc traceon(302) to look for other optimization problems.
Be sure that update statistics has been run on the index recently.