Opions on Clustered Index Use
When I create my database tables, the clustered index is automatically created on the identity (Pkey) Column.
It has been suggested to me that I need to create a clustered index on a different set of columns, researching this, I read that it should always include the primary key of the table - that is a contraction to what I understood.
Here's the situation. I have report tables, REPORTS - REPORTPRODUCTS - REPORTPRODUCTYEARS, etc. When a user is creating a new report, he gets a report_ID from the top table Reports. Based on his reporting criteria, several child tables are populated. If he changes his criteria, the child records are deleted an repopulated with the new data. Then he can create PDF files and Excel files from the tabulated Child Tables.
It has been suggested that I should create CLUSTERED indexes on the child tables like this...
For the ReportProduct table (a child of Reports)...
REPORT_ID (parent), PRODUCT_ID
And for the ReportProductYears table (grandchild...)
REPORT_ID (grandparent), REPORTPRODUCT_ID (parent), YEAR
My question - seems like a good strategy. Any concerns doing this? This would mean the primary key of the child and grandchild tables would NOT have a clustered index on them. I would then create just a unique index on these primary key fields, yes??
Advice...? Thank you!
It has been suggested to me that I need to create a clustered index on a different set of columns, researching this, I read that it should always include the primary key of the table - that is a contraction to what I understood.
Here's the situation. I have report tables, REPORTS - REPORTPRODUCTS - REPORTPRODUCTYEARS, etc. When a user is creating a new report, he gets a report_ID from the top table Reports. Based on his reporting criteria, several child tables are populated. If he changes his criteria, the child records are deleted an repopulated with the new data. Then he can create PDF files and Excel files from the tabulated Child Tables.
It has been suggested that I should create CLUSTERED indexes on the child tables like this...
For the ReportProduct table (a child of Reports)...
REPORT_ID (parent), PRODUCT_ID
And for the ReportProductYears table (grandchild...)
REPORT_ID (grandparent), REPORTPRODUCT_ID (parent), YEAR
My question - seems like a good strategy. Any concerns doing this? This would mean the primary key of the child and grandchild tables would NOT have a clustered index on them. I would then create just a unique index on these primary key fields, yes??
Advice...? Thank you!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@HuaMinChen - thank you for your post!
I think I understand what you're saying, but I think you reversed FK and PK ? So the master table can still have on clustered index on the Master Primary Key (Not the FK). And on the Child table the clustered index could be the FKey to the Master plus the PKey of the child. If that is true, then I understand, thank you.
The clustered index definitely is help to quicken the mapping of both master and detail tables, based on their common key, which is FK of master table and PK of detail table.
So on master table, you still can have one clustered index on this FK column.
I think I understand what you're saying, but I think you reversed FK and PK ? So the master table can still have on clustered index on the Master Primary Key (Not the FK). And on the Child table the clustered index could be the FKey to the Master plus the PKey of the child. If that is true, then I understand, thank you.
Yes, even for Child table, you can have one other index, specifically for the PK only!
ASKER
@MlandaT, any follow up from you? See previous post.
Also question - can I just drop my cluster index and create a new one and SQL Server will reorganize everything? Is it that easy to change?
Also question - can I just drop my cluster index and create a new one and SQL Server will reorganize everything? Is it that easy to change?
Yes, you can re-create the clustered index.
Yes, you could just drop the existing clustered index and create a new one.
However, you may not need the nonclustered indexes you have once the clustered index changes. Therefore, I'd recommend instead that you:
1) drop all nonclus indexes
2) create the new clus index
3) wait a while, then look at missing index and index usage stats and see if (A) the new clus index is effective and (B) a nonclus index(es) are needed.
However, you may not need the nonclustered indexes you have once the clustered index changes. Therefore, I'd recommend instead that you:
1) drop all nonclus indexes
2) create the new clus index
3) wait a while, then look at missing index and index usage stats and see if (A) the new clus index is effective and (B) a nonclus index(es) are needed.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you all - probably the best answers I have ever gotten on a question. Wish I had more points to share!
Glad to have helped.
ASKER
I have lots of large tables that would only be access based on their parent ID (foreign key) and a date or other attribute. For example, Product Pricing - we keep a history. So the price of a product is a large table ProductPrices with its own primary key productPriceID, but I would always fetch the price based on Product ID and Effective Date. I think you are saying these two fields should be my clustered index.
The same is for my reports. All the child and grandchildren table would ONLY be touched if they are part of the REPORT_ID. One user running a report NEVER touches a record of another ReportID, so it seems that the REPORT_ID should be the first element of my clustered index, then add other fields that may help narrow the search. These other fields may vary though based on the criteria of the search (show all products by date, all products by category, by location). What would I do for a clustered key here? REPORT_ID seems obvious, but what next in line?
Implementation - can I just drop my cluster index and create a new one and SQL Server will reorganize everything? Is it that easy to change?
Thank for the help!!