• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Clustered index on a DB table

 
Ranch Hand
Posts: 95
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 584
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
reply
    Bookmark Topic Watch Topic
  • New Topic