aspose file tools*
The moose likes Programming Diversions and the fly likes Clustered index on a DB table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Other » Programming Diversions
Bookmark "Clustered index on a DB table" Watch "Clustered index on a DB table" New topic
Author

Clustered index on a DB table

Bala Krishna
Ranch Hand

Joined: Jan 26, 2001
Posts: 95
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
Bartender

Joined: Oct 14, 2005
Posts: 18656
    
    8

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

Joined: Jan 26, 2001
Posts: 95
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
Bartender

Joined: Oct 14, 2005
Posts: 18656
    
    8

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

Joined: Jan 19, 2006
Posts: 584
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 ]

"If someone asks you to do something you don't know how to, don't tell I don't know, tell I can learn instead." - Myself
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Clustered index on a DB table