How to Force Index on a SQL Server Query? – Interview Question of the Week #281

Question: How to Force Index on a SQL Server Query?

Answer: I personally do not like to force an index on any query. As a matter of fact, I have enough bad experience with this one. I always recommend in my Comprehensive Database Performance Health Check, I always discuss why index hints and query hints are not recommended in my health check consulting engagement.

Here is how you can force an index to be used with a query with the help of an index hint.

SELECT *
FROM [WideWorldImporters].[Sales].[Invoices]
WITH(INDEX([FK_Sales_Invoices_AccountsPersonID]))
WHERE CustomerID = 191

In the above query, we are forcing the index FK_Sales_Invoices_AccountsPersonID to the index. We can always pass the name of the index in the WITH clause and that index will be used for the query. If the index does not exist, it will give you an error, so it is a good idea to check if the index exists before the query is executed.

It is not required that you have to use the name of the index in the SQL Query. You can also use the number as well.

SELECT *
FROM [WideWorldImporters].[Sales].[Invoices]
WITH(INDEX(0))
WHERE CustomerID = 191

For example, the above query will use the clustered index (if exists) or a heap to retrieve data from the table.

You can replace the index id zero with the other index number (greater than 1) which will represent the non-clustered index on the table.

SELECT *
FROM [WideWorldImporters].[Sales].[Invoices]
WITH(INDEX(2))
WHERE CustomerID = 191

For example, the above query will use the first non clustered index and use it to retrieve the data. The id of the index you can check in the sys.index table.

UPDATE: If a clustered index exists, INDEX(0) forces a clustered index scan and INDEX(1) forces a clustered index scan or seek. If no clustered index exists, INDEX(0) forces a table scan and INDEX(1) is interpreted as an error. (Thanks to Carsten Saastamoinen-Jakobsen)

Once again, I want to reiterate that the index hint is not a good way to get started with the coding. You need to make sure that your query is re-written in such a way that it uses the most optimal index.

Reference: Pinal Dave (https://blog.sqlauthority.com)