Clustered vs nonclustered indexes

Overview

Clustered and nonclustered indexes are two core SQL Server rowstore index types. Both help the optimizer find rows more efficiently, but they store and locate data differently. A clustered index defines the logical order of the table's data rows. A nonclustered index is a separate structure that stores index key values plus row locators that point back to the base table.

This difference matters because clustered index choice affects every nonclustered index on the table, range-query performance, insert patterns, storage, fragmentation, and update cost. Nonclustered indexes are more flexible and can be created for many query patterns, but each one adds storage and write overhead.

Interviewers use this topic to test whether candidates understand physical data access rather than just SQL syntax. A strong answer explains not only "one clustered, many nonclustered," but also why clustered keys should usually be narrow, unique, stable, and aligned with important access patterns.

For interviews, you should be able to describe heaps, clustered tables, row locators, key lookups, covering indexes, and how the optimizer chooses between scans, seeks, and lookups.

Core Concepts

What An Index Does

An index is a data structure that helps SQL Server find rows without scanning every row in a table.

Example table:

CREATE TABLE dbo.Orders
(
    OrderId BIGINT IDENTITY(1, 1) NOT NULL,
    CustomerId BIGINT NOT NULL,
    OrderDate DATETIME2 NOT NULL,
    Status NVARCHAR(30) NOT NULL,
    TotalAmount DECIMAL(19, 4) NOT NULL
);

Without a useful index, a query may need to scan the whole table:

SELECT OrderId, OrderDate, TotalAmount
FROM dbo.Orders
WHERE CustomerId = 42;

With an index on CustomerId, SQL Server can seek to the matching key range instead of reading every row.

Clustered Index

A clustered index sorts and stores the table rows based on the clustered index key. There can be only one clustered index per table because the data rows can only be stored in one logical order.

Example:

CREATE CLUSTERED INDEX CX_Orders_OrderId
ON dbo.Orders (OrderId);

After this, dbo.Orders is a clustered table. The leaf level of the clustered index contains the full data rows.

Common clustered index choices:

• Identity primary key such as OrderId.
• Date plus identity key for time-series workloads.
• Tenant plus identity key for some multi-tenant workloads.
• Natural key only when it is narrow, stable, and truly unique.

Nonclustered Index

A nonclustered index is a separate structure from the base table. It stores key values in sorted order and row locators that point to the actual row.

Example:

CREATE NONCLUSTERED INDEX IX_Orders_Customer_OrderDate
ON dbo.Orders (CustomerId, OrderDate DESC);

This index helps queries that search by customer and order by date:

SELECT OrderId, OrderDate, TotalAmount
FROM dbo.Orders
WHERE CustomerId = @CustomerId
ORDER BY OrderDate DESC;

Unlike a clustered index, a table can have many nonclustered indexes. That flexibility is useful, but every additional index must be maintained when data changes.

Heap

A heap is a table without a clustered index. Its data rows are not stored in clustered key order.

Example:

CREATE TABLE dbo.ImportRows
(
    ImportRowId BIGINT IDENTITY(1, 1) NOT NULL,
    BatchId BIGINT NOT NULL,
    RawPayload NVARCHAR(MAX) NOT NULL
);

If no clustered index is created, this table is a heap.

Heaps can be useful for some staging or bulk-load scenarios, but many OLTP tables benefit from a clustered index because it provides stable row organization and efficient row locators for nonclustered indexes.

Row Locators

The row locator in a nonclustered index tells SQL Server how to find the base row.

For a heap:

• The row locator points to the physical row location.

For a clustered table:

• The row locator is the clustered index key.

This is why clustered key design matters. The clustered key is carried in nonclustered indexes. A wide clustered key can make every nonclustered index wider and more expensive.

Clustered Key Design

A good clustered key is usually:

• Narrow.
• Unique.
• Stable.
• Ever-increasing or mostly insert-friendly.
• Frequently useful for access patterns.

Example:

CREATE TABLE dbo.Orders
(
    OrderId BIGINT IDENTITY(1, 1) NOT NULL,
    CustomerId BIGINT NOT NULL,
    OrderDate DATETIME2 NOT NULL,
    Status NVARCHAR(30) NOT NULL,
    TotalAmount DECIMAL(19, 4) NOT NULL,
    CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED (OrderId)
);

OrderId is narrow, unique, and stable. It is usually a reasonable clustered key.

Less ideal clustered key:

CREATE CLUSTERED INDEX CX_Users_Email
ON dbo.Users (Email);

Email can be long, can change, and may make nonclustered indexes wider. It might be a good unique nonclustered index, but it is often a poor clustered key.

Primary Key Vs Clustered Index

A primary key is a constraint. A clustered index is a storage/access structure. They are often created together, but they are not the same thing.

Example:

CREATE TABLE dbo.Users
(
    UserId BIGINT IDENTITY(1, 1) NOT NULL,
    Email NVARCHAR(320) NOT NULL,
    CONSTRAINT PK_Users PRIMARY KEY NONCLUSTERED (UserId)
);

CREATE CLUSTERED INDEX CX_Users_UserId
ON dbo.Users (UserId);

SQL Server often creates a clustered index for a primary key by default unless told otherwise, but a primary key can be nonclustered and a clustered index can be created on different columns.

Index Seek Vs Index Scan

An index seek navigates directly to a key or key range.

Example:

SELECT OrderId, CustomerId, OrderDate
FROM dbo.Orders
WHERE OrderId = @OrderId;

With a clustered index on OrderId, this can become a clustered index seek.

An index scan reads a larger portion of an index or the entire index.

Example:

SELECT OrderId, CustomerId, OrderDate
FROM dbo.Orders
WHERE YEAR(OrderDate) = 2026;

Applying a function to OrderDate can make a normal index on OrderDate less useful for seeking. A sargable range is usually better:

WHERE OrderDate >= '20260101'
  AND OrderDate < '20270101';

Key Lookup

A key lookup happens when SQL Server uses a nonclustered index to find matching keys, then looks up missing columns from the clustered index.

Example index:

CREATE INDEX IX_Orders_CustomerId
ON dbo.Orders (CustomerId);

Query:

SELECT OrderId, CustomerId, OrderDate, TotalAmount
FROM dbo.Orders
WHERE CustomerId = @CustomerId;

If OrderDate and TotalAmount are not in the nonclustered index, SQL Server may seek IX_Orders_CustomerId and then perform key lookups to fetch missing columns from the clustered index.

Key lookups are not automatically bad. They are fine for small result sets. They become expensive when many rows require many lookups.

Covering Nonclustered Index

A covering index contains all columns needed by a query, either as key columns or included columns.

Example:

CREATE INDEX IX_Orders_Customer_OrderDate
ON dbo.Orders (CustomerId, OrderDate DESC)
INCLUDE (TotalAmount, Status);

This can cover:

SELECT OrderDate, Status, TotalAmount
FROM dbo.Orders
WHERE CustomerId = @CustomerId
ORDER BY OrderDate DESC;

The query can be satisfied directly from the nonclustered index without looking up the base row.

Clustered Index Range Queries

Clustered indexes are strong for range queries that align with the clustered key.

Example:

CREATE CLUSTERED INDEX CX_OrderEvents_EventTime_EventId
ON dbo.OrderEvents (EventTime, OrderEventId);

Query:

SELECT *
FROM dbo.OrderEvents
WHERE EventTime >= @StartTime
  AND EventTime < @EndTime
ORDER BY EventTime, OrderEventId;

The clustered order supports the date range and output ordering. The trade-off is that inserts may concentrate on the latest page if data is always inserted by current time.

Insert And Update Costs

Indexes speed reads but slow writes.

When a row is inserted:

• The clustered index is updated.
• Every relevant nonclustered index is updated.
• Page splits can occur if inserts land in the middle of an index.

When indexed columns are updated:

• SQL Server may need to move entries within one or more indexes.
• If the clustered key changes, every nonclustered index row locator can be affected.

That is why stable clustered keys matter.

Clustered Index Width And Nonclustered Indexes

Because a clustered key is used as the row locator in nonclustered indexes, clustered key width affects storage and memory use across the table's indexes.

Problematic pattern:

CREATE CLUSTERED INDEX CX_Documents_Tenant_Slug_Title
ON dbo.Documents (TenantId, Slug, Title);

This clustered key may be wide, and Title can change.

Often better:

CREATE TABLE dbo.Documents
(
    DocumentId BIGINT IDENTITY(1, 1) NOT NULL,
    TenantId INT NOT NULL,
    Slug NVARCHAR(200) NOT NULL,
    Title NVARCHAR(300) NOT NULL,
    CONSTRAINT PK_Documents PRIMARY KEY CLUSTERED (DocumentId),
    CONSTRAINT UX_Documents_Tenant_Slug UNIQUE (TenantId, Slug)
);

The clustered key is narrow and stable. The business uniqueness rule is enforced separately.

Common Mistakes

Common mistakes include:

• Thinking primary key and clustered index always mean the same thing.
• Creating a clustered index on a wide, mutable natural key.
• Creating many nonclustered indexes without considering write cost.
• Ignoring key lookups that happen for many rows.
• Assuming every index seek is good and every scan is bad.
• Creating duplicate or overlapping indexes.
• Forgetting that nonclustered indexes on clustered tables contain the clustered key.
• Indexing tiny tables without measuring benefit.
• Letting missing-index suggestions create overly broad indexes.

Best Practices

Best practices:

• Use a clustered index on most OLTP tables unless a heap is intentional.
• Prefer narrow, unique, stable clustered keys.
• Keep nonclustered indexes targeted to real query patterns.
• Use included columns to cover important queries without widening key columns.
• Watch for key lookups that run once per many rows.
• Do not create indexes speculatively.
• Review index usage and remove redundant or unused indexes.
• Test index changes against representative read and write workloads.
• Consider filtered indexes for well-defined subsets.
• Read execution plans to verify how indexes are actually used.