How to use index in select statement?

user_814548 0
Viewed 489822

Lets say in the employee table, I have created an index(idx_name) on the emp_name column of the table.

Do I need to explicitly specify the index name in select clause or it will automatically used to speed up queries.

If it is required to be specified in the select clause, What is the syntax for using index in select query ?

indexing sql database
8 Answers

The index hint is only available for Microsoft Dynamics database servers. For traditional SQL Server, the filters you define in your 'Where' clause should persuade the engine to use any relevant indices... Provided the engine's execution plan can efficiently identify how to read the information (whether a full table scan or an indexed scan) - it must compare the two before executing the statement proper, as part of its built-in performance optimiser.

However, you can force the optimiser to scan by using something like

    Select  *
    From    [yourtable] With (Index(0))
    Where   ...

Or to seek a particular index by using something like

    Select  *
    From    [yourtable] With (Index(1))
    Where   ...

The choice is yours. Look at the table's index properties in the object panel to get an idea of which index you want to use. It ought to match your filter(s).

For best results, list the filters which would return the fewest results first. I don't know if I'm right in saying, but it seems like the query filters are sequential; if you get your sequence right, the optimiser shouldn't have to do it for you by comparing all the combinations, or at least not begin the comparison with the more expensive queries.

user_52146630
Related