SQL Server Force Index Usage Table Hint

22 Dec
December 22, 2011

A quick example that shows how to force SQL Server to use a specified index when generating the Query Plan

You can force SQL Server to use a particular Clustered or Non-Clustered Index using the following SQL Table Hint:

WITH (INDEX(<index-name-1>,<index-name-x>*),<other-table-hints>*)

This being a “Table Hint”, you need to place the WITH statement next to the table name in the FROM section of your query, and after any table alias, as shown in the example below:

SELECT
    Sale,
    Amount
FROM
    dbo.Transactions T WITH (INDEX(IDX_Customer),NOLOCK)
WHERE
    CustomerID = 1

The reasons you might need to force SQL Server to use an index include:

  • Test a newly created index performance.
  • Sometimes you need to force SQL to use a Filtered Index, in cases were you have your WHERE clause (which matches your Filtered Index criteria) made up of Expressions, which SQL has to evaluate at run time, which happens after the Query Plan is generated.
  • You needed to show SQL who’s the boss!

When forcing SQL to use a specific Filtered Index, you need to make sure that the query does not return data that has been filtered out of the index, this will cause a query processor error.

There are also a couple of default values that you can use with this query hint:

  • WITH(INDEX(0)): Force a SQL Server Clustered Index Scan
  • WITH(INDEX(1)): Force a SQL Server Clustered Index Scan or Seek
* * * * ½ 2 votes
1 reply

Trackbacks & Pingbacks

  1. […] the query forcing the utilisation of the newly created Filtered Index, you can check out my post on forcing a sql server index utilization for more details on […]

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply to SQL Cannot Produce Query Plan Error | Thinknook Cancel reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>