First of all, Let me state it upfront that hinting index is not a very good idea and could result in degraded performance. Index hint is not actually a hint rather we are ordering Database Engine to use the provided index however it is still called as “Hint”. In one of my earlier post, I have described the possible side effect of using index hint and how did it help when I managed to remove them.
As you might be aware in SQL Server, if you are using index hint and by chance that index is dropped (or disabled) by DBA – your query is going to fail straight away, there is no escape from that until the date(I hope they will change this behavior very soon). In case of Sybase ASE, you don’t have to worry about that as query is not going to fail and you are saved from facing any embarrassing situation. You might not get your intended query plan and the performance you were expecting though however relieving point is – it won’t fail.
In some of the rarest scenario where I have heard index hint is justified when a specific report is run by Executive Management once in a while and they want that to be executed very fast, that’s where they use it and since it is not used other than that time, they are considered as unused index by DBAs. As these indexes affect more to writes(insert, update and deletes) than they support select – they are considered suitable candidates for dropping.
Please leave your comment if you liked this post or have any feedback.