4

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Middletone
  • 4,190
  • 12
  • 53
  • 74
  • So how do I force it to use the particular index? I'm looking for a command, not a hint. – Middletone May 23 '13 at 03:55
  • 1
    I've got very large indexed views and I'm using the index since it's pre-sorted. If I ran a sort command on 10+ Million rows I'd be sitting here for ages. By constructing the query carefully and using the correct index it returns almost instant results, the problem is that in this particular case (indexes on an indexed view) it's no longer respecting the parameters that I've supplied it. – Middletone May 23 '13 at 04:09
  • From looking at the documentation my *guess* would be that SQL Server wasn't able to come up with a valid execution plan using that index. Were any warnings returned? – Justin Jun 04 '13 at 15:40
  • Nope, I checked on the query plan. In the end I reverted to a single index and the server picked it up correctly. I'm a bit puzzled by it still. – Middletone Jun 05 '13 at 03:17

1 Answers1

10

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

Roeland
  • 820
  • 1
  • 9
  • 33