Question 230
A reporting table repeatedly scans date ranges but its clustered index key creates high fragmentation from random writes. Initial testing shows: Typical date-range queries improve without unacceptable write regression. Which limitation should the team still consider?
Explanation
Correct Answer
Clustering cannot replace partition elimination or fix nonsargable predicates. Clustered key choice and supporting indexes influence physical access and insert patterns. Test a stable clustering strategy with targeted date-supporting access paths.
Incorrect Answers
Physical maintenance cannot substitute for matching index keys to query and insert patterns; benchmark both workloads.
Uniqueness and clustering locality differ; random insert distribution can increase page splits and scan overhead.
Correctness is necessary but not enough; targeted date access and insertion behavior should determine index selection.