In SQL Server indexing, which index type stores the actual data rows at the leaf (bottom) level and keeps those rows physically ordered the same as the index key?
-
ANonclustered
-
BClustered
-
CPrimary
-
DSecondary
Answer
Correct Answer: Clustered
Explanation
Introduction / Context:Choosing between clustered and nonclustered indexes requires knowing where the data lives in the B-tree and how row order is maintained.
Given Data / Assumptions:
- We compare clustered vs nonclustered leaf storage.
- We care about physical order of rows on disk/pages.
- SQL Server implementation is assumed.
Concept / Approach:
In a clustered index, the table data itself is the leaf level of the index, ordered by the clustering key. In a nonclustered index, the leaf contains only keys and locators; data resides elsewhere.
Step-by-Step Solution:
1) If an index stores rows at the leaf → clustered.2) If leaf stores locators that point to rows → nonclustered.3) The prompt states rows are stored at leaf and ordered the same as the index → clustered.Verification / Alternative check:
Creating a clustered index on a heap converts it into a clustered table; scanning shows order by clustering key.
Why Other Options Are Wrong:
Nonclustered: leaf has pointers, not rows. Primary/Secondary: naming does not define storage semantics.
Common Pitfalls:
Assuming a primary key must be clustered; SQL Server allows a nonclustered primary key.
Final Answer:
Clustered