>Yep. And just because it happens to use the index now, as time goes on and data is added or the statistics change, it can decide to use another index. What I've done in some cases is to grab a copy of the actual query execution plan (both as a screenshot and XML) so I have something to compare against if a query suddenly starts behaving differently than it did in the past.
>
>You can also add index hints to the query to force it to use a specific index, ex.
>
>
>SELECT SomeField
> FROM SomeTable WITH(INDEX(NameOfIndex))
> WHERE SomeField = 4
>
>
>Just realize that can cause it's own issues (same reasons - the data changes, more records are added) and suddenly that isn't really the best index to use anymore. But since you're forcing it, the query analyzer can't use anything else.
Yes, exactly, forcing it could create another ball game.
I use that at one place for big calculation but in general, WITH INDEX should not be used.