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

Navigation
20

I have a non-normalized table in SQL Server installed on my PC which is fed monthly by Integration Services (SSIS) from a report into an Excel worksheet. That is, I am 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 ID 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've created three indexes:

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

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

    
asked by anonymous 17.12.2013 / 13:24

1 answer

17

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
    
17.12.2013 / 13:36
How to prevent spam in contact forms without using CAPTCHA? How to add a CSS class to images that did not load?