[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 607
  • Last Modified:

Primary Key - Clustered Index - SQL Server

Hi,

I thought that when you create a primary key, clustered index is automatically created. Am i correct or wrong ? But here is what i have got:

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.

Please explain me on detail manner whether i am correct or wrong. Provide simple examples to understand it better.

I did not follow this statement: and you do not specify a unique nonclustered index => Please explain in detail with example.

Thanks
0
milani_lucie
Asked:
milani_lucie
3 Solutions
 
cyberkiwiCommented:
-- specifically stated as nonclustered
CREATE TABLE TEST1 ( ID INT NOT NULL PRIMARY KEY NONCLUSTERED )

-- not stated, so it will default to clustered
CREATE TABLE TEST2 ( ID INT NOT NULL PRIMARY KEY )


CREATE TABLE TEST3 ( ID INT NOT NULL)

-- not stated, so it will default to clustered
Alter table Test3 add constraint pk_test1 primary key(ID)
     or
-- specifically stated as nonclustered
Alter table Test3 add constraint pk_test1 primary key nonclustered(ID)


CREATE TABLE TEST4 ( ID INT NOT NULL, X INT NOT NULL UNIQUE CLUSTERED)

-- the following will not be clustered because Test4 already has a clustered index
Alter table Test4 add constraint pk_test1 primary key(ID)

-- the following won't even work
Alter table Test4 add constraint pk_test1 primary key clustered(ID)
0
 
ThakurVinayCommented:
When you create a primary key unique cluster index automatically creates but it is not necessory you can create a primary key with out unique cluster index.

if you try to create primary key with GUI you will understand it better.

HTH
Vinay
0
 
dportasCommented:
No a clustered index is not ncessarily created automatically. If you don't specify the type of index for the PRIMARY KEY column(s) then a clustered index will be created unless a clustered index is specified for some other column(s).
If you want to be sure, then it's best to specify whether you want a CLUSTERED or NONCLUSTERED index. The primary key isn't necessarily the best choice for a clustered index.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now