An article so absolutely shallow and superficial as to be entirely braindead. I won't go into why (if you don't already know, then you shouldn't be writing SQL code in the first place) but I will correct one error. An index scan is not "much better" than a table scan. It's exactly the same cost as scanning a table as wide as that index. In this example, if the table had no other rows than last name and first name, the cost would be identical whether table or index scanning, and the QO would only favor the index scan on the assumption that the index is more likely to already be cached.

Kiran, B-trees really aren't that complex to understand. In all four of your examples, there will be an index seek. In case 1 and 4, the index will then be range-scanned for all ABC values (in case #1, this is optimal anyway). In case 2 and 3, the second WHERE predicate is also an index key, so the seek will proceed directly to the first matching value from the result set.

Thanks for the article Sarvesh. You may want to spend a little more time on the next one, just nail down why these things occur. As you showed, it's not simply the order in which columns are stored, rather, it's which column is on the front, the leading edge, of the index. Answer the question, why does the leading edge matter, and you'll have a much improved article.