Clustered index on a DB table

[Bala Krishna]

This question has nothing to do with Java, but it's an interview question - I hope this is the right place. Here's the question:

In SQLServer, you can define a Clustered Index on a Database Table. (For those unfamiliar with what a clustered index is...A clustered index guarantees that the data in the table is ordered per the values in the column(s) that form the clustered index). Can we or can we not define more than one clustered index on a table? Why?

I know the answer is no. We cannot define more than one clustered index on a table. I couldn't come up with a good answer for the why part of it. Any help?
TIA.

[Paul Clapham]

Suppose, just for example, that you had two clustered indexes. One on employee_name and the other on employee_number. Then this guarantees that (a) the table will be ordered by employee_name and (b) the table will be ordered by employee_number. You can't have both of those at the same time.

So the only way to have two clustered indexes would be if they were over the same columns. And that's trivially pointless, so there's no point in allowing it.

[Bala Krishna]

Makes sense. However, why can't we arrange the data so that it's first sorted by one column, and then by the other? I'll consider the simple case of defining clustered indices on last name and first name. If there are any collisions in the last names, the data could then be ordered by the first name:

Last Name First Name
========= ==========
Adams John
Bush George H.W.
Bush George W.
Clinton William
....... ........

etc.

[Paul Clapham]

So?

A clustered index guarantees that the data in the table is ordered per the values in the column(s)...

Note that it says IS, not COULD BE. In your example the data is not ordered by first name.

[Edisandro Bessa]

Originally posted by Bala Krishna:
Makes sense. However, why can't we arrange the data so that it's first sorted by one column, and then by the other? I'll consider the simple case of defining clustered indices on last name and first name. If there are any collisions in the last names, the data could then be ordered by the first name:

Last Name First Name
========= ==========
Adams John
Bush George H.W.
Bush George W.
Clinton William
....... ........

etc.

Maybe I misunderstood your question but for sure you can define one clustered index sorted by one column and then by another.

Here's an example :

create table RESULTS
(
score int,
candidate_name varchar(30)
)

create clustered index IDX_CLUSTER_RESULTS on RESULTS (score, candidate_name)
[ June 14, 2006: Message edited by: Edisandro Bessa ]