September 24, 2008 at 1:10 pm
Assume I have the following tables where PK = primary key:
Table A - PK_A INT, attr_1 VARCHAR(5000)
Table A_B - PK_A INT, PK_B INT
Table B - PK_B INT, attr_2 VARCHAR(5000)
What should the clustered and/or nonclustered indexes be for Table A_B? Should the clustered index be (1) PK_A, PK_B; (2) PK_A; or (3) PK_B? Should there be any non-clustered indexes?
September 25, 2008 at 9:19 am
As always, the answer is "it depends".
Depends on volume of Insert/Update/Delete activity on the table versus Select.
Depends on how the data is typically accessed ie, always looking at the possible values of B for a small range of values of A or vice versa.
I would typically have a clustered index on (PK_A, PK_B) and a reverse non-clustered covering index on (PK_B, PK_A).
But as always, monitor and adjust as necessary. If there is heavy fragmentation due to values of A being inserted/updated in a non-ascending order causing page-splits, then maybe the clustering should be on (PK_B, PK_A).
September 25, 2008 at 9:37 am
PW (9/25/2008)
As always, the answer is "it depends".Depends on volume of Insert/Update/Delete activity on the table versus Select.
Depends on how the data is typically accessed ie, always looking at the possible values of B for a small range of values of A or vice versa.
I would typically have a clustered index on (PK_A, PK_B) and a reverse non-clustered covering index on (PK_B, PK_A).
But as always, monitor and adjust as necessary. If there is heavy fragmentation due to values of A being inserted/updated in a non-ascending order causing page-splits, then maybe the clustering should be on (PK_B, PK_A).
Would you ever create an index (clustered or nonclustered) containing only PK_A OR PK_B?
September 25, 2008 at 9:44 am
Typically never a non-clustered on only 1 column - in a many-to-many intersection table, an index on just 1 of the columns would never be selective enough to be considered.
Clustered on 1 of the columns ? Possibly, depends on situation. If one of the values in the clustered index is changing frequently, causing fragmentation and statistics to get stale quickly, then maybe.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply