Any Idea

If Table1 is going to be joined on 90% of the time on ID, and used to
generate lists 10% of the time, which of the following combinations of
indexes would provide the best performance to the most queries?
1.. Clustered unique inde on ID; non-clustered index on LastName,
FirstName
2.. Clustered index on LastName, FirstNamel non-clustered unque index on
ID
3.. Clustered index on FirstName, LastName; non-clustered unique index on
ID
4.. Non-clustered, unique index on ID; non-clustered index on Last Name
I think so it's B
On Mon, 13 Jun 2005 13:09:18 -0400, Roy wrote:

>If Table1 is going to be joined on 90% of the time on ID, and used to
>generate lists 10% of the time, which of the following combinations of
>indexes would provide the best performance to the most queries?
> 1.. Clustered unique inde on ID; non-clustered index on LastName,
>FirstName
> 2.. Clustered index on LastName, FirstNamel non-clustered unque index on
>ID
> 3.. Clustered index on FirstName, LastName; non-clustered unique index on
>ID
> 4.. Non-clustered, unique index on ID; non-clustered index on Last Name
>I think so it's B
>
Hi Roy,
Did you know that posting school asssignments on Usenet is generally
frowned upon?
However, the question above is so utterly bad, even for a school
assignment, that I can't hold still.
The correct answer is 5: not enough information to answer. You really
must know what columns are in Table1, which of these columns are
candidate keys (alone or in combination), and how many rows (approx)
there are in the table. You'll also have to know the same information
about the tables that Table1 will be joined to. And you'll have to know
the layout of the lists that will be generated. With that information,
you can take a stab at what the indexing strategies look promising - and
then you'll have to test, test, test, test, test them all in order to
know for sure.
Oh, and you might also want to inform your teacher that in the real
world, lists (aka reports) are usually not based on one table, but on
information from several tables. On a join, that is.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)