Clustered Index Design Guidelines

What is a Clustered Index?

A clustered index determines the physical order of data rows in a table. Each table can have at most one clustered index because the data rows can be sorted in only one order.

CREATE CLUSTERED INDEX IX_Employee_ID
ON Employee(EmployeeID);

Guideline 1 – Choose a Narrow, Unique Key

Why narrow matters

A narrow key reduces the size of the index key and the amount of data that must be moved when the index is rebuilt. Uniqueness prevents the engine from adding a hidden uniqueifier.

Prefer INT or BIGINT over VARCHAR(50).
Use surrogate keys when natural keys are wide.

Guideline 2 – Keep the Key Immutable

Impact of changing key values

Changing a clustered index key forces a row movement, which can be costly. Choose columns that rarely change.

Guideline 3 – Align with Query Patterns

Design the clustered index to support the most common range queries and sorting operations.

Query Pattern	Recommended Key
Retrieve recent orders	`OrderDate DESC`
Lookup by primary key	`PrimaryKey`
Range scan on `CustomerID`	`CustomerID, OrderDate`

Guideline 4 – Avoid Large Row Sizes

If a table contains large variable-length columns, consider placing them off-row to keep the clustered index key compact.

CREATE TABLE Orders(
    OrderID BIGINT PRIMARY KEY,
    OrderDate DATETIME,
    CustomerID INT,
    OrderDetails NVARCHAR(MAX) -- will be stored off-row
);

Guideline 5 – Consider Fill Factor

Set a lower fill factor for tables with frequent inserts to reduce page splits.

ALTER INDEX IX_Orders_OrderDate ON Orders
REBUILD WITH (FILLFACTOR = 80);