There are a couple of problems here.
Firstly, the index is on the
column. It can be used to satisfy queries on the
column, but will be of no use for queries on the
Think of it an address book which is sorted by last name. You have created a secondary index based on the first name, and included the town in that index. You now want to find all entries in a specific town. The secondary index wouldn't help, since you'd still have to go through every entry to find the matching ones.
The other problem is that you're using a "contains" query -
WHERE column LIKE '% ... %'
. There is no sensible way for an index to help with that query. Again, given a list of towns in alphabetical order, if you want to find any town which contains
the letter "p", you still have to go through every entry to find it.
If you were performing a "starts with" query -
WHERE column LIKE '... %'
- then an index could help.
Even for an "ends with" query -
WHERE column LIKE '% ...'
- it would be possible to use an index.
But for a "contains" query, there's not much you can do, short of creating a separate table with every substring from your column.
Sargability: Why %string% Is Slow - Brent Ozar Unlimited®
One way to get an index seek for a leading %wildcard in SQL Server