Is the primary key always the clustered index?
Quick Answer
No. The primary key gets a clustered index by default, but you can declare PRIMARY KEY NONCLUSTERED and cluster the table on a different column. They're separate concerns: the PK enforces unique identity; the clustered index defines physical order and is duplicated into every non-clustered index.
Not necessarily. When you create a primary key, SQL Server makes it the clustered index unless you say otherwise, but you can write PRIMARY KEY NONCLUSTERED and cluster the table on something else. They do different jobs — the PK is about unique identity, the clustered index is about physical order and is copied into every non-clustered index as the row locator. The case where I'd separate them is when the natural key is a random GUID: I'd keep the PK non-clustered and cluster on a narrow, increasing identity column, because a random clustered key causes constant page splits and bloats all the other indexes.
- When to separate them: the natural PK is wide or random (a random GUID) — cluster on a narrow, ever-increasing column (an
int/bigintidentity or a sequential GUID) instead, and keep the PK as a non-clustered unique constraint. - Why it matters: a random-GUID clustered key causes page splits on insert and bloats every non-clustered index (which stores the clustered key as its row locator).
- The ideal clustering key is narrow, unique, static and ever-increasing.