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:
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:
dbo.Transactions T WITH (INDEX(IDX_Customer),NOLOCK)
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