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

Question: How do I force an index for a query? Use a table hint such as WITH (INDEX(IndexName)). It restricts the optimizer’s choice of index; it doesn’t guarantee a faster query or an index seek.

I have enough bad experience with index hints that I don’t recommend them as a starting point during a Comprehensive Database Performance Health Check. An index that looks right today can become a poor choice when the data, parameters or workload change.

The original example forced the WideWorldImporters index on AccountsPersonID while filtering CustomerID = 191. That is a useful reminder: naming an index doesn’t make its leading column match the predicate. SQL Server may scan it and fetch additional columns.

Inspect a small example

This self-contained temporary table makes the same distinction without depending on an index name in your copy of the sample database. In SSMS, enable the actual execution plan before running it:

IF OBJECT_ID('tempdb..#IndexHintDemo') IS NOT NULL
    THROW 50001, 'The example temp table already exists.', 1;
CREATE TABLE #IndexHintDemo
(
    InvoiceID int NOT NULL PRIMARY KEY CLUSTERED,
    CustomerID int NOT NULL,
    AccountsPersonID int NOT NULL
);
INSERT #IndexHintDemo VALUES (1,191,10),(2,192,20),(3,191,30);
CREATE INDEX IX_AccountsPerson ON #IndexHintDemo (AccountsPersonID);

-- Enable Include Actual Execution Plan in SSMS to inspect access paths.
SELECT InvoiceID, CustomerID, AccountsPersonID
FROM #IndexHintDemo WITH (INDEX(IX_AccountsPerson))
WHERE CustomerID = 191;

SELECT InvoiceID, CustomerID, AccountsPersonID
FROM #IndexHintDemo WITH (INDEX(0))
WHERE CustomerID = 191;

SELECT InvoiceID, CustomerID, AccountsPersonID
FROM #IndexHintDemo WITH (INDEX(1))
WHERE InvoiceID = 1;

SELECT index_id, name, type_desc
FROM tempdb.sys.indexes
WHERE object_id = OBJECT_ID('tempdb..#IndexHintDemo');
DROP TABLE #IndexHintDemo;

The first two queries both return invoices 1 and 3 for customer 191. The third returns invoice 1. Compare the chosen access paths, not just the returned rows. This three-row example demonstrates syntax; it isn’t a meaningful performance benchmark.

On the SQL Server 2025 lab, the second query’s INDEX(0) hint produces a Clustered Index Scan. Its native Properties show three rows read and two returned. The predicate remains CustomerID=191. A forced access path is not a guarantee of a seek.

Index IDs have precise meanings

• With a clustered index, INDEX(0) forces a clustered index scan.
• With a clustered index, INDEX(1) permits a clustered index scan or seek.
• On a heap, INDEX(0) forces a table scan; INDEX(1) is an error.

Thanks to Carsten Saastamoinen-Jakobsen for the correction in the original comments. Keep that distinction; it is more useful than saying both 0 and 1 simply mean the clustered index.

A value such as 2 identifies that table’s index with index_id = 2. It isn’t a reliable synonym for “the first nonclustered index.” Look in sys.indexes, and prefer a meaningful name when a deliberate hint is necessary. A missing or disabled hinted index can turn a query into an error.

First understand the estimate and access-path problem, inspect statistics and the index definition, and test the query without a hint. When a measured exception requires one, document the reason and revisit it as the workload changes.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.