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!