An Orders table is currently a heap with no indexes. Most queries retrieve every column for a range of order dates, and a smaller set of lookups finds orders by CustomerID. Which indexing design best supports the primary access path?
Select an answer to reveal the explanation.
Short Explanation
Think of a clustered index like a phone book: the pages themselves are in order, so you only get one way to sort the book. Put that one physical order on the column your big range queries use, OrderDate, and hang a separate nonclustered index off CustomerID for the occasional lookup. The trap is thinking you can have two clustered indexes; you can't.
Full Explanation
A clustered index determines the order in which the table's rows are stored, so a table can have only one. Because the dominant workload reads every column across a contiguous range of order dates, clustering on OrderDate lets the engine locate the start of the range and read adjacent rows sequentially, with no extra lookup to fetch the remaining columns. A nonclustered index is a separate structure holding the key plus a pointer (row locator) back to the data, which suits the narrower, less frequent CustomerID lookups. Defining two clustered indexes is impossible because rows cannot be physically ordered two ways at once. Keeping the heap with only nonclustered indexes forces every row in a wide date range to be fetched through a separate pointer lookup, which becomes expensive as the range grows. Clustering on CustomerID optimizes the minor workload and leaves the major range query paying a lookup per row. Exam caveat: in many engines the primary key is clustered by default, but that default can be changed when another column is the main range-access path. Operational check: compare execution plans and logical reads for the date-range report before and after the change in a test copy, and confirm the plan shows a range seek on the clustered index rather than a scan plus lookups.