As u can c the idea is simple...
...an assignment has an INDEX as PK, a start date, an end date and other fields;
...an assignment can have one or more tasks; the relationship is 1:N and is identifying (see next point)
...a task has a SUB INDEX inside the assignment; that is the PK is the assigment it belongs to (also a FK) and an index for that assignment
...a task has also a TYPE, which is a FK to the TASK_TYPE table
Consider also that...
...ASSIGNMENT contains > 1 millions rows
...TASK contains < 10 rows for each assignment (so an average of 5 millions rows)
...TASK_TYPE contains < 10 rows
As far as I know SQL-server creates a CLUSTERED INDEX for any PK, that is
Do I have to add any other NON CLUSTERED INDEX? I would say I should add the following:
But of course TASK(Assignment) is already part of the CLUSTERED INDEX ASSIGNMENT(Assignment, SubIndex), so I shouldn't add it, right?
What about TASK(Type)?
Or maybe there's a completely different solution?
My main problem is due to the fact that TASK has a composite PK wher one field is also a FK.