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.