>Walter,
>
>My understanding of an non-clustered index scan is that any efficiency improvement comes from its containment of indexed data separately from the rest of the row. It still has to scan the content for each potential candidate, doesn't it, albeit in a more concise form?

Yes, that is correct. Since the index is smaller than the actual row, it is going to be faster.

>And isn't it true that sometimes it may be quicker to do a full scan- if there are lots of non-discriminatory where clauses - e.g. surname like %M% and firstname like %w% and state like %N% ?

Also true.. however most of the times it chooses one expression, does an index scan on it. Then it would do a table lookup of all the found matches and filters out the other (AND) expressions. The thing would be different if there are ORs in the expression. It will either do two index scans or just one table scan, unless the two columns on which the OR applies are within one single index (In which case it would do an index scan again)

It really is intersting to dive into the optimizer on how it chooses it execution plans. If you're getting a grasp on how it works you're in a better position to judge on how to write SQL statements and what indexes you need to optimize it. But it takes a lot of time and patience..