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

Junior MS SQL

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.

Want to bookmark, take notes, or join discussions?

Sign in to access all features and personalize your learning experience.

Sign In Create Account

Source: SugharaIQ

Ready to level up? Start Practice