Take the 2-minute tour ×
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. It's 100% free, no registration required.

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 
ADD CONSTRAINT PK_Sample_SeqGUID_Col1 PRIMARY KEY NONCLUSTERED (SeqGUID_Col1)

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
add comment

2 Answers

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
add comment

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:

CLUSTERED | NONCLUSTERED

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
add comment

Your Answer

 
discard

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.