SQL Server Interview Question #123

What is a nonclustered index?

Indexes & Query Performance Senior Advanced

Quick Interview Answer

A nonclustered index is a separate B-tree structure containing indexed key values and row locators that allow SQL Server to find the underlying rows. A table can have multiple nonclustered indexes. They are commonly created on columns used frequently in WHERE clauses, JOIN predicates, ORDER BY operations, or selective lookups. Too many nonclustered indexes increase storage and write cost, so indexes should be designed around actual workload requirements.

Detailed Explanation

A nonclustered index is a separate B-tree structure containing indexed key values and row locators that allow SQL Server to find the underlying rows.

A table can have multiple nonclustered indexes. They are commonly created on columns used frequently in WHERE clauses, JOIN predicates, ORDER BY operations, or selective lookups.

Too many nonclustered indexes increase storage and write cost, so indexes should be designed around actual workload requirements.

Code Example

CREATE NONCLUSTERED INDEX IX_Products_CategoryId_Price
ON dbo.Products(CategoryId, Price);