Home » Microsoft » MB6-886 » Which action should you take?
You are a database developer on an instance of SQL Server 2008. Your database contains a table named ProductDetails that has a PRIMARY KEY constraint defined on the ProdID column. Several other nonclustered indexes have been created on the table.
For a particular query, you discover that full table scans are being performed because the optimizer does not choose the best index. You want to force the optimizer to use the clustered index.
Which action should you take?
A. Drop all existing nonclustered indexes.
B. Use the INDEX(0) table hint.
C. Use the FORCESEEK table hint.
D. Use the FORCE ORDER query hint.
Correct Answer: B
Explanation/Reference:
If a clustered index exists on the table, the INDEX(0) hint forces a clustered index scan on the table. If no clustered index exists, the hint forces a full table scan.
You can also use the INDEX table hint and specify a specific index that the query optimizer should use, or you can use the INDEX(1) to force an index scan or seek on the clustered index.
You should not drop all existing nonclustered indexes because this might adversely affect other queries.
You should not use the FORCESEEK table hint. The FORCESEEK table hint can be specified to force the query optimizer to use only an index seek when accessing the data. The query optimizer will consider both clustered and nonclustered indexes, but will not use a specific index.
You should not use the FORCE ORDER query hint. The FORCE ORDER query hint controls how the query’s join order is handled when a query is being optimized.
Specifying FORCE ORDER indicates that query optimization will not affect the join order specified in the query.
