>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.

Exact match and begins with are best optimizable. Indeed when doing a contains, the best an optimizer can do is a index scan.

BTW, see message Re: The use of UPPER() in an index Thread #1298350 Message #1298363. In a little test I did:

SELECT * FROM t_demo WHERE UPPER(dem_surname) LIKE 'TEST%' 

GO

SELECT * FROM t_demo WHERE dem_surname LIKE 'TEST%'

The outcome was dramatic. In the first it was doing an index scan, the second an index seek. When looking at the profiler the index scan did thousands of reads and quite some milliseconds of CPU, while the index seek only did about 50 reads and no CPU at all. The bigger the table, the bigger the difference between an index scan and index seek.