7

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.

  • Thank Mark for the editing. I will keep those points in mind for my next post. – UB01 Apr 26 '12 at 0:26
12

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.

8

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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