Clustered indexes — OS, DBMS & Networks Aptitude Question
Clustered indexes: Which statement about a clustered index is correct? Answer and worked solution on CodeKairo.
- Topic: OS, DBMS & Networks
- Section: Programming MCQs
- Difficulty: Medium
- Time target: 75 seconds
Question
Which statement about a clustered index is correct?
Options
- A table can have at most one clustered index
- A table can have one clustered index per column
- A clustered index cannot be built on the primary key
- A clustered index stores only pointers and never the rows themselves
Show the answer and the worked solution
Correct answer: A. A table can have at most one clustered index
Worked solution
- A clustered index determines the physical order in which the rows are stored, and rows can be laid out in only one order at a time.
- So A table can have at most one clustered index, though it may carry many non-clustered ones.
- The primary key is in fact the usual choice for it, and the leaf level of a clustered index holds the rows themselves rather than pointers to them.
Answer: A table can have at most one clustered index.
The idea
Clustered means the index is the table's storage order; non-clustered means a separate structure pointing back at the rows. Range scans are fast on a clustered key and slower through a non-clustered one.
Shortcut. Eliminate: the primary key is the usual clustered index, its leaves hold the rows, and one per column would need several physical row orders at once, which leaves at most one per table.
Common trap. "A clustered index stores only pointers and never the rows themselves" describes a non-clustered index; a clustered index's leaves are the rows.