Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

In Sql Server (2008), is it possible to have a PRIMARY KEY on a set of columns without either CLUSTERED or NONCLUSTERED indexes on the same set of columns?

I am aware of the fact that PRIMARY KEY and CLUSTERED INDEX key are separate concepts and that we can create PRIMARY KEY without CLUSTERED INDEX on it (see below).

ALTER TABLE dbo.Sample 

But my question is to see if it is possible to create PRIMARY KEY on a table without a CLUSTERED or NONCLUSTERED index on it.

share|improve this question
Thank Mark for the editing. I will keep those points in mind for my next post. – UB01 Apr 26 '12 at 0:26
up vote 12 down vote accepted

In short, no. A primary key by definition requires uniqueness, an index on the primary key field is the database engines route to enforcing this constraint.

From BOL:

When you specify a PRIMARY KEY constraint for a table, the Database Engine enforces data uniqueness by creating a unique index for the primary key columns. This index also permits fast access to data when the primary key is used in queries. Therefore, the primary keys that are chosen must follow the rules for creating unique indexes.

share|improve this answer
Thank you, that is very helpful. – UB01 Apr 26 '12 at 0:24

No, a primary key constraint is always enforced in SQL-Server by a unique index. The index may be clustered or unclustered. If you don't specify which type, default is CLUSTERED for the primary key. From MSDN documentation, CREATE TABLE:


Indicate that a clustered or a nonclustered index is created for the PRIMARY KEY or UNIQUE constraint. PRIMARY KEY constraints default to CLUSTERED and UNIQUE constraints default to NONCLUSTERED.

In a CREATE TABLE statement, CLUSTERED can be specified for only one constraint. If CLUSTERED is specified for a UNIQUE constraint and a PRIMARY KEY constraint is also specified, the PRIMARY KEY defaults to NONCLUSTERED.

share|improve this answer
Thank you for more information. It is very helpful too. – UB01 Apr 26 '12 at 0:24

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.