Skip to main content
Tweeted twitter.com/#!/StackDBAs/status/563711666551275520
edited tags
Link
user1822
user1822
Source Link
JDT
  • 175
  • 1
  • 6

Is a clustered index on a child table in a parent/child relationship the most optimal index?

A clustered index will sort row data in a table according to the ordering of the fields in the index. If so, why would you (or would you not) want to create a clustered index on the child table in a parent/child relationship?

Lets assume the classical order-with-orderlines example where you never fetch the orderlines without first going through the associated order. I would think that having a clustered index on both the orderid and the orderlineid column (as clustered indexes must be unique) forces all orderlines for a single order to be physically located adjacent to each other, which would make reads very performant. Is this correct? And if so, are there downsides to this approach?