Explain the internal structure of a SQL Server index (Clustered vs Non-Clustered). How does it affect performance?

Junior MS SQL Published: Nov 21, 2025

Quick Answer Clustered index stores the actual data rows in leaf pages, sorted by the key â€” the table IS the index. Non-clustered index stores key values plus a pointer (row locator) to the actual row. Clustered is optimal for range queries. Non-clustered adds lookup overhead unless it covers all needed columns.

Answer

Clustered Index: Defines the physical order of rows in the table. Implemented as a B-Tree with root, intermediate, and leaf nodes. The leaf level contains actual data rows.

Non-Clustered Index: Also a B-Tree, but leaf nodes store index keys and row locators (RID or clustered key).

Performance Impact: Clustered indexes help range queries, while non-clustered indexes help quick lookups. Poor clustered key choice can make indexes large and slow.

SugharaIQ Editorial Team Verified Answer

This answer has been peer-reviewed by industry experts holding senior engineering roles to ensure technical accuracy and relevance for modern interview standards.

Bookmark Add to Set Notes

Want to bookmark, take notes, or join discussions?

Source: SugharaIQ

Explain the internal structure of a SQL Server index (Clustered vs Non-Clustered). How does it affect performance?

Answer

Want to bookmark, take notes, or join discussions?

Related Questions in MS SQL