Originally posted by rdjabarov
You can use (INDEX=index_name) optimizer hint.
Well, you can certainly specify the optimizer hint, but it won't help the performance any, and could easily hurt it.
Sad fact is, NO index will help if you lead off your target string with a wildcard. Think of it like this. The fact that a dictionary is in alphabetic order won't help you find all the words that have 'TIO' somewhere in the middle. You'll still have to look at every word in the dictionary. Same with this query.
If you specify the index hint, it engine will still have to scan the entire index looking for matching values. And if the index you specify doesn't cover all the needed columns, the engine will ALSO have to do a bookmark lookup in the clustered index or heap to find those other columns. A clustered index scan is usually faster than an index scan + bookmark lookup (as opposed to index SEEK + bookmark)
If the logic of the situation will let you get correct results by using this
Where NME_PROSPECT_FULL LIKE 'John smith%
ie, you know you have the starting characters, the engine will probably use the index without have to tell it to.