Why does SQL ignore an index hint and opt for a different index?

Question

Given a table that has two indexes on it, one sorted in the reverse from the other and given these two queries.

Select value From SomeTable wITH (INDEX(IV_Sort_Asc))
Select value From SomeTable wITH (INDEX(IV_Sort_Desc))

I've come across a case in SQL Server 2008 where the hints are ignored and in both cases the IV_Sort_Desc index is used instead of the first one.

I realize many people will immediately suggest to not supply the hint, however given my specific case this is not an option.

What would cause this and what can I do to fix it? Surely you would expect SQL Server to honour an index hint and not use a different one?

Answer 1

I ran into the same problem when I wanted SQL to use an index on a view. It turned out I had to use the NOEXPAND option as well:

WITH (FORCESEEK, INDEX (IndexName),NOEXPAND)

https://technet.microsoft.com/en-us/library/bb510478%28v=sql.105%29.aspx