Choosing a clustered index

There are two broad kinds of index in SQL Server: clustered and nonclustered.

Their structure is the same, a B-tree search tree.

• A nonclustered index copies the index key data into a physical structure separate from the table. It is the equivalent of the index at the end of a book: it does not mix with the body of the book itself.
• A clustered index structures the table. Physically, the table rows are stored at the leaf level of the tree. It is the equivalent of a dictionary, where the whole book is ordered on a sorted search key.

When to choose a clustered index

A clustered index shapes the table itself. A clustered table has the following properties:

• It has to be kept in the order of the clustered index key at all times.
• The reference to a row is the clustered index key. That means every nonclustered index has to contain the clustered index key.

Choosing the clustered index therefore matters. The principles:

• You can only have one clustered index on a table, since the table is structured by that index.
• Strongly prefer putting the clustered index on an auto-incrementing or timestamp column — one that is monotonically increasing. This avoids table fragmentation and the insert slowdowns that come with it. A GUID (UNIQUEIDENTIFIER) is therefore a poor choice.
• Since the clustered index key is contained in every nonclustered index on the table, being the row reference, it had better be small so it does not inflate the size of every index. An INT or a BIGINT, not a UNIQUEIDENTIFIER.
• The clustered index key should identify a row uniquely. You can create a non-unique clustered index with CREATE CLUSTERED INDEX, but that forces SQL Server to add a hidden extra integer (INT) to disambiguate duplicate values. Always create a clustered index with CREATE UNIQUE CLUSTERED INDEX.

Choosing the clustered index for performance

The best solution is generally to leave the clustered index on the primary key: the primary key is unique, and lookups on the primary key are frequent, in join clauses. The clustered index optimizes those joins by avoiding lookups — the extra step of fetching the rows matching an index key reference.