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)
3 Comments. Leave new
SELECT *
FROM [WideWorldImporters].[Sales].[Invoices]
WITH(INDEX(1))
WHERE CustomerID = 191
For example, the above query will use the first non clustered index and use it to retrieve the data.
It’s wrong! The rules are:
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.
You are correct. I wanted to know use index(2) but ended up using index 1 in example. So now to make my blog correct, I will add your note there.
Thanks Carsten.
It is important to remember that the SQL engine may decide not to use your index if it just isn’t worth the effort. For example, during development and testing you might only have a few records in a table, and SQL decides it’s faster to do a full clustered index scan rather than use your index and then have to do a key lookup to fetch the remaining columns. This is not an error and it does NOT mean that you should add an index hint to force the index to be used. When you go to production and have thousands (or millions) of rows, SQL will make use of that index. That’s what SQL statistics are for. In general, SQL Server is way smarter than you or me (well, me at least) and you shouldn’t try to second-guess it except under very edge cases.