Search code examples
sql-serverperformanceprimary-keyclustered-indexphysical-design

How does the `primary key` keyword relate to clustered indexes in SQL Server?

How does the PRIMARY KEY keyword relate to clustered indexes in SQL Server?

(Some people seem to want to answer this question instead of a different question I asked, so I am giving them a better place to do so.)

Solution

  • The clustered index of a table is normally defined on the primary key columns.

    This, however is not a strict requirement.

    From MSDN:

    When you create a PRIMARY KEY constraint, a unique clustered index on the column or columns is automatically created if a clustered index on the table does not already exist and you do not specify a unique nonclustered index.

    And:

    You can create a clustered index on a column other than primary key column if a nonclustered primary key constraint was specified.