Will indexing improve varchar(max) query performance, and how to create index

The best analogy I've ever seen for why an index won't help '%wildcard%' searches:

Take two people. Hand each one the same phone book. Say to the person on your left:

Tell me how many people are in this phone book with the last name "Smith."

Now say to the person on your right:

Tell me how many people are in this phone book with the first name "Simon."

An index is like a phone book. Very easy to seek for the thing that is at the beginning. Very difficult to scan for the thing that is in the middle or at the end.

Every time I've repeated this in a session, I see light bulbs go on, so I thought it might be useful to share here.

you cannot create an index on a varchar(max) field. The maximum amount of bytes on a index is 900. If the column is bigger than 900 bytes, you can create the index but any insert with more then 900 bytes will fail.

I suggest you to read about fulltext search. It should suits you in this case

It's not worthwhile creating a regular index if you're doing LIKE '%keyword%' searches. The reason is that indexing works like searching a dictionary, where you start in the middle then split the difference until you find the word. That wildcard query is like asking you to lookup a word that contains the text "to" or something-- the only way to find matches is to scan the whole dictionary.

You might consider a full-text search, however, which is meant for this kind of scenario (see here).