19
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.
Sorry, I do not know if this is a particularity of SQL Server, but I could not understand exactly at what point you are in need of help.
– Guilherme
It wasn’t very clear to me either...
– Kenny Rafael
When using a SELECT I would like to use a specific index in order to optimize the search. For, if the index is in the order of INDEX_FUNC, when I use a query that adds the total of the field
Remuneracao
forLotacaoID
, the query simply becomes slow, as the fields are indexed by other fields that are not part of the query strategy.– ricidleiv
Force a certain index is never good...
– Ruberlei Cardoso