Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Clustered index on a DB table

 
Bala Krishna
Ranch Hand
Posts: 95
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 21107
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 95
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 21107
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 584
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic