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

Question:

I have a non-normalized SQL Server table installed on my PC which is fed monthly by Integration Services (SSIS) from a report in an Excel spreadsheet. That is, I am keeping the history of a certain report.

This table has several key fields. Depending on the query I want to do on 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:

You can run the query as below:

SELECT CAMPO
FROM TABELA WITH (INDEX(INDEX_LOTA))

And it is also possible to 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
Scroll to Top