Covered Index

Here's our scenario:
User inputs data into app such as fname, lname, birthday, etc. You can
search on only one field if you'd like.
Table has multiple indexes. It has an index on fname, lname, birthday, etc.
Index Tuning Wizard suggests combining the index into a covered index. While
that may help if the user searches on everything, doesn't that slow things
down if they only search on lname since the data isn't only lname but all of
the other stuff too?
I hope that makes sense. I am just trying to see the pros and cons of
taking the wizards advice and switching 4 non-clustered indexes to 1 covered
non-clusterd index.
Thanks
Some indexes can be used to cover multiple queries. For example, if your
index is on (lname, fname, bday), then it will support searches on:
lname
lname, fname
lname, fname, bday
lname, bday
As long as your search criteria includes the first column of a
multiple-column index, you have a shot at using the index. For complete
coverage of a query, though, all elements of the WHERE and SELECT clauses
must appear in the columns of the index. Otherwise, a bookmark lookup will
also be required.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:5DF43AE1-39DA-4021-B00F-4E1AEF0D82C6@.microsoft.com...
Here's our scenario:
User inputs data into app such as fname, lname, birthday, etc. You can
search on only one field if you'd like.
Table has multiple indexes. It has an index on fname, lname, birthday, etc.
Index Tuning Wizard suggests combining the index into a covered index. While
that may help if the user searches on everything, doesn't that slow things
down if they only search on lname since the data isn't only lname but all of
the other stuff too?
I hope that makes sense. I am just trying to see the pros and cons of
taking the wizards advice and switching 4 non-clustered indexes to 1 covered
non-clusterd index.
Thanks