Which columns to cluster?

[willydude]

I have the following tables. Note CustNumber out of order in first table and Cust_PK_ID in second table.

Tbl_Customers
Cust_PK_ID CustNumber Name
1 36 Pete
2 38 Millie
3 12 Coop
4 3 Andy


Tbl_Jobs
Jobs_PK_ID Cust_PK_ID JobNumber Location
1 3 0212 3
2 1 0786 1
3 4 3131 1
4 2 3386 2
5 1 0786 2

It seems that it would it be better to set Tbl_Customers to Clustered on the CustomerNumber (36, 38, 12 and 3) b/c new records, searches and queries will be based on the CustomerNumber, not the PK_ID. Doing this would put them in order of 3, 12, 36 , 38.

But when I need to look up information from the Tbl_Jobs, create the cluster on JobNumber, Location and Cust_PK_ID? If I understand it correctly, in the Tbl_Jobs above, the order of Jobs_PK_ID would become 1,2,5,3,4.

If I used the order of Cust_PK_ID, JobNumber, Location, then it would be 2, 5, 4, 1, 3.

When entering timesheets and new jobs, I will need both the JobNumber and the Cust_PK_ID (obtained from the tbl_Customers)

Thoughts about the sort orders?

TIA,

Bill

 

[mrdenny]

Part of your problem would be that you aren't using the natural key for primary key of the Customers table.

I would leave the Primary Key as the clustered index as most of your queries against the CustomerId column are going to be single row selects so why waist the clustered index when it's not going to be used to it's fullest.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog