City LIKE 'ISLAND%'
note that there is no wildcard at the beginning, the optimizer can scan, or seek a very small range of the index (skipping unneccesary entries). With a million records or more this mean significant less IO and CPU (you can not trust the profiler to state the number of reads because cached reads won't display, unless you restart SQL server each time, duration should be reliable if doing it locally because it includes network I/O duration).

That's true....but obviously depending on what the user wants to do. If the user wants an option to either search for exact match, or "begins with", or "contains", then the search becomes less optimizable by the time you get to that third option.