How to use a specific index in a SQL Server query?

Question

I have a non-standard table in SQL Server installed on my PC which is fed monthly by Integration Services (SSIS) from a report in an Excel spreadsheet. I mean, I’m keeping track of a certain report.

This table has several key fields. Depending on the query I want to do in this table, I want SQL Server to use a specific index.

For example, the table below has several identification fields.

+---------------+---------+-------+
| Campo         | Tipo    | Index |
+---------------+---------+-------+
| DataID        | date    |   *   |
| NumFolhaID    | int     |   *   |
| LotacaoID     | int     |   *   |
| LotacaoNome   | varchar |       |
| LotacaoZona   | varchar |       |
| FuncMatricID  | int     |   *   |
| FuncVincID    | varchar |   *   |
| FuncNome      | varchar |       |
| FuncCpf       | varchar |       |
| CargoCarreira | varchar |       |
| CargoNome     | varchar |       |
| FuncaoNome    | varchar |       |
| Remuneracao   | money   |       |
| DataAdm       | date    |       |
+---------------+---------+-------+

I created three indexes:

• INDEX_FUNC - composed of the fields (FuncMatricID, FuncVincID, DataID) in this order;
• INDEX_LOTA - composed of the fields (LotacaoID, DataID, FuncVincID) in this order;
• INDEX_FOLH - composed of the fields (DataID, NumFolhaID) in this order;

When using a SELECT I would like to use a specific index for the query.

Answer 1

You can run the query as below:

SELECT CAMPO
FROM TABELA WITH (INDEX(INDEX_LOTA))

And you can also add INDEX to a Join

SELECT CAMPO
FROM TABELA T WITH (INDEX(INDEX_LOTA))
INNER JOIN OUTRA_TABELA OT
WITH (INDEX(OUTRO_INDICE))
ON OT.ID = T.ID