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 ins开发者_开发知识库tead of a different question I asked, so I am giving them a better place to do so.)

---

How does the PRIMARY KEY keyword related to clustered indexes in MS SqlServer?

By default, a PRIMARY KEY is implemented as a clustered index. However, you can back it by an unclustered index as well (specifying NONCLUSTERED options to its declaration)

A clustered index is not necessarily a PRIMARY KEY. It can even be non-unique (in this case, a hidden column called uniqueifier is added to each key).

Note that a clustered index is not really an index (i. e. a projection of a table ordered differently, with the references to original records). It is the table itself, with the original records ordered.

When you create a clustered index, you don't really "create" anything that you can drop apart from the table. You just rearrange the table itself and change the way the records are stored.

---

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.

---

A primary key is, as the name implies, the primary unique identifier for a row in your table. A clustered index physically orders the data according to the index. Although SQL Server will cluster a primary key by default, there is no direct relationship between the two.