SQL Server index usage question

Question

Assuming the following:

/*

drop index ix_vouchers_nacsz on dbo.vouchers;
drop index ix_vouchers_nacsz2 on dbo.vouchers;

create index ix_vouchers_nacsz on dbo.Vouchers(
 FirstName, LastName,
 Address, Address2, City,
 State, Zip, Email
);

create index ix_vouchers_nacsz2 on dbo.Vouchers(
 Email, FirstName, LastName,
 Address, Address2, City,
 State, Zip
);

*/

select count(firstname) from vouchers
 with (index(ix_vouchers_nacsz))
where 
 firstname = 'chris' and
 lastname = '' and
 address = '' and
 address2 = '' and
 city = '' and
 state = '' and
 zip = ''

select count(firstname) from vouchers
 with (index(ix_vouchers_nacsz2))
where 
 firstname = 'chris' and
 lastname = '' and
 address = '' and
 address2 = '' and
 city = '' and
 state = '' and
 zip = ''

Why does the second index result in an index scan while the first results in an index seek? What difference does the ordering of the keys make?

Answer 1

The second index starts with the email field, but you're not filtering on email. That makes the index useless.

An index is typically a b-tree that allows you to do a binary search. The b-tree is sorted by its index fields. So if you know the first field, you can look it up quickly. Within the same values of the first field, you can look up the second field very quickly.

It's like a telephone book that's sorted on last name. You can't use it to search for a specific telephone number.

Answer 2

Applying the 'phone book' analogy may help in understanding.

The first index is a 'phone book' sorted by FirstName, then Last Name and so on. If your asked to look up Chris in this phone book, then you can find all the Chris' listed together by the index.

In the second index, is a phone book sorted by 'phone numbers' (or email just as easily) then first name, then last name and so on. If your asked to use this phone book to look up listings with the firstname of Chris, your out of luck the phone book is not sorted that way! Of course, if you were asked to look for email address [email protected] and name Chris, then you can find the email address first, and then look for matching names.