Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

Cluster That Index! Expand / Collapse
Posted Monday, October 9, 2006 7:11 AM


Group: General Forum Members
Last Login: Thursday, July 22, 2010 8:59 AM
Points: 110, Visits: 952
I usually change SQL2000 default clustered index on the primary key to the foreign key of the parent table. We are usually selecting children from a parent-child relationship. By having the child clustered on the parentID, the matching child records for a given parent are located sequentially on the index page. This makes a huge difference if the children are inserted far apart in time (where they would otherwise have ended up on many different index pages) We have not been concerned about page splits since inserts are done during nightly batch processing while lookups are done in real-time throughout the day. A small extra overhead during an unattended process to secure a faster user experience is an obvious tradeoff.

I agree with the author: always have a clustered index. The real decision is which index should be clustered.
Post #314068
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse