SQL Server › Indexes

What is the difference between a clustered index and a nonclustered index?

Beginner

Answer

A clustered index determines the actual physical order in which data is stored on disk, meaning a table can only have one clustered index, while a nonclustered index creates a separate structure that points back to the actual data, allowing a table to have many nonclustered indexes in addition to its single clustered index.

-- Clustered index, often on the primary key
CREATE CLUSTERED INDEX IX_Orders_OrderId ON Orders(OrderId);

-- Nonclustered index for a different search pattern
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId ON Orders(CustomerId);

Real-world example An orders table uses a clustered index on OrderId since that matches how data is typically inserted and retrieved, while a separate nonclustered index on CustomerId speeds up a common query that looks up all orders for a specific customer.

Common follow-ups: Why can a table only have one clustered index?;Does creating a primary key automatically create a clustered index?

Query Optimization & Plans;Constraints (Primary Key Foreign Key Check & Unique)