primary key is always indexed in sql server?

Question

You can create a clustered index on a column other than primary key column if a nonclustered primary key constraint was specified. http://msdn.microsoft.com/en-us/library/ms186342.aspx

So the above told me: I can create a clustered index on columns other than primary key.

I think it also conveys that a primary key should either be a nonclustered primary key or clustered key. Is it possible a primary key is not indexed?

What's more:

When you create a UNIQUE constraint, a unique nonclustered index is created to enforce a UNIQUE constraint by default. You can specify a unique clustered index if a clustered index on the table does not already exist.

Does this mean a unique constraint has to create a clustered index or a nonclustered index?

Solution 1

Is it possible a primary key is not indexed?

No, it's not.

Some kind of an index is required to police the PRIMARY KEY, otherwise it would require scanning the whole table on each insert (to ensure uniqueness).

From the docs:

The Database Engine automatically creates a unique index to enforce the uniqueness requirement of the PRIMARY KEY constraint. If a clustered index does not already exist on the table or a nonclustered index is not explicitly specified, a unique, clustered index is created to enforce the PRIMARY KEY constraint.

---

Does this mean a unique constraint has to create a clustered index or a nonclustered index?

Yes, for the same reasons.

---

PRIMARY KEY and UNIQUE are logical concepts, while the index just has a side effect of implementing them efficiently. So, strictly speaking, ALTER TABLE ADD CONSTRAINT UNIQUE and CREATE UNIQUE INDEX mean different things.

If, in the future, some bright head invents a way to use Pauli principle or quantum entanglement or whatever to enforce uniqueness on physical level, SQL Server 2155 may employ it to enforce the constraint, but it will still have to create the B-Tree for the index.

Solution 2

Short answer: No,

Every table in SQL Server should have only one clustered index. Others indexes (non-clustered) use from clustered index. Primary key is clustered index commonly. But primary-key is always indexed unlike foreign key.

http://www.sqlfiddle.com/#!6/972f0/1