Index vs Index hint

  • October 1, 2010

This is a discussion I had with several colleagues. What is the difference between index and index hint and what do we use in our code?

There are a lot of misunderstandings about what these keywords actually  do. When you use the index keyword in a select statement the kernel will translate this to a order by command and the database optimizer will chose the best index to actually use. When you chose to use the index hint keyword in your select statement, Ax will force the database to use the chosen index.

Now what guideline should you use in writing your select statement? Index / Index hint or should you write nothing and let Ax decide what to do with the query. There are pro’s and con’s for all decisions.
Some people have no trust in the common sense of the developer and say ‘let the database optimizer chose’, it will know what’s best.
Personally I’m a big fan of using the index keyword in all your select statement because you it forces you to think about the indexes that are available on the table or if you should add a extra index to optimize the database-call for your SQL statement. In the end, the database optimizer can chose what the best index is to use.

If anyone has a opinion about this matter, don’t hesitate to comment.

  1. Jonas left a comment on October 1, 2010 at 7:45 am

    If the main goal of using the index keyword is to achieve an order by, I would prefer adding the order by keyword explicitely.
    Changes to the index by someone else could cause existing select statements to return data in an unexpected order.

    Regarding the index hint keyword, I never use that one. I will never be as smart as SQL Server when it comes to selecting the best index.
    Also…as far as I can rememeber (I might be wrong), if you use the Index Tuning Wizard to optimize queries, all queries that contain index hints are ignored and will not be evaluated.

    • Jeroen Doens left a comment on October 1, 2010 at 8:33 am

      To be honest: I say I am a fan of the index keyword, but I almost never use it (I also use ORDER BY). It is indeed a way to order the records. I’m also not as smart as SQL, and I will never say I am. But looking at index is something that is often forgotten (especially when you start programming) that is how I ment “I’m a big fan“.

  2. Malcolm Burtt left a comment on October 1, 2010 at 9:06 am

    I would actively discourage the use of the INDEX keyword for exactly the reason that Jonas points out above but I agree that its good to encourage developers to think about the available indexes every time they right a query. For that we reason I require the developers that work in my team to add an INDEX HINT that is commented out showing the index that they expect SQL Server to use for the query. This, at least, encourages them to check that there is a supporting index although even this won’t always make them think about adding a new one when needed.
    Generally it is best to leave SQL Server to pick the best index but there are occassions when the plan it chooses isn’t optimal and that is the time to use query hints (INDEX HINT, FORCEPLACEHOLDERS, FORCELITERALS, FORCESELECTORDER, FORCENESTEDLOOP) to get the query to run in the fastest time possible for the data set that you have. Don’t always reach for INDEX HINT, the other query hints all have their place to.

  3. Luegisdorf left a comment on October 5, 2010 at 11:35 am

    I’m going agree with Malcom and Jonas to avoid ‘index’ and ‘index hint’ feature. ‘Order by x, y, z’ has a better readablity than ‘index z’ and ‘index hint’ can have fatal performance incursions if f.ex. the condition does not match the fields in the specified index …

    But good explanation for the difference betweend the two index key words 🙂

Comments are closed.