[RESOLVED] SQL Server, Clustered Index

[DKenny]

Table, with 18M records, has a compound PK of ORDER_NUM and ORDER_LINE_NUM. (Every order can have many lines)
There are many time that I will need to query for the lines of a single order, so am considering a clustered index.

My questions is:
Do I make the index associated witht the compound PK clustered or should I have a seperate clustering index which only includes the ORDER_NUM column?

[brucevde]

Using the same columns in a clustered and non-clustered index is a waste of disk space because all non-clustered indexes contain the clustered Index. Plus, I doubt the non-clustered index would ever be used by sql server.

If the most often used query looks something like

Select * From TableName Where Order_Num = 12345 Order By Order_Line_Num

Then I would Cluster the PK.

However, how often does the Order_Line_Num column change (I never understood why detail data contains a "line number")? Does your application allow users to change line numbers. If a line number is deleted do you have to re-set all other line numbers.

If the answer is "often", then using the Order_Line_Num in a Clustered Index would not be a good idea. When a change to a clustered index causes a new sort order all other indexes pointing to the original clustered index are updated as well.

[DKenny]

...how often does the Order_Line_Num column change (I never understood why detail data contains a "line number")? Does your application allow users to change line numbers. If a line number is deleted do you have to re-set all other line numbers.

If the answer is "often", then using the Order_Line_Num in a Clustered Index would not be a good idea. When a change to a clustered index causes a new sort order all other indexes pointing to the original clustered index are updated as well.

The line num can never change, this is warehousing/reporting app, not the prime system, so the data never changes.

I will cluster on the PK.

Thanks again