Home » Microsoft » MB6-886 » Which optimizer hint can you include for the query to force the query to use a specific index rather than the index chosen by the query optimizer?
You are a database developer on an instance of SQL Server 2008. You have a complex query that is performing poorly. You examine the query’s execution plan and determine that the query optimizer has not chosen an index that will provide the best performance. Which optimizer hint can you include for the query to force the query to use a specific index rather than the index chosen by the query optimizer?
A. Optimize for B. table hint
C. fast
D. force order
Correct Answer: B
Explanation/Reference:
You can use the TABLE HINT query hint. In this scenario, you want to force a query to use a specific index. You can do so using an INDEX table hint specified using the TABLE HINT query hint.
You should not use the OPTIMIZE FOR query hint because it is used to optimize queries based on a specific value for a local variable used in the query.
You should not use the FAST query hint. The FAST query hint optimizes the query to quickly retrieve a specified number of rows. You would use the FAST query hint to quickly return a fewer number of rows from a large table. For example, you might use the following query to quickly retrieve the first 200 rows from the Products table:
SELECT * FROM ProductHistory ORDER BY ProdStyle DESC OPTION (FAST 200);
When this query executes, the first 200 rows in the result set will be returned as quickly as possible, and then the query will return the remainder of the result set when the query completes.
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.
