Myth: Primary Key and Clustered index

Myth:
“When you create a PRIMARY KEY on table, Clustered index is creating by default”
Or
“Primary Key will create a clustered index by default”

Explanation:
I heard these statements many times and recenty, l heard from one of my collegues as well. So thought of sharing with you that the statement is a myth. Though, MSDN article says it very clearly, some of people are not learning or reading it with its completeness.

Please find the Excerpt from MSDN Article:

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..

Now, Let us look at something practical.

First, I would like to create a table with no clustered index(Heap) and define a primary key on the table.[Heap is a table without clustered index.]

Create Table PRIMARYKEY_MYTH(Column1 int NOT NULL,Column2 int)

Alter Table PRIMARYKEY_MYTH Add constraint PK__PRIMARYKEY_MYTH__Column1 PRIMARY KEY(Column1)

sp_help 'PRIMARYKEY_MYTH'

PK__PRIMARYKEY_MYTH__Column1	clustered, unique, primary key located on PRIMARY	

Drop table PRIMARYKEY_MYTH

From the above, we can clearly say that Primary Key is creating a Clustered index on a table.

Second, we need to create a Primary key on a clustered table(Already a clustered index is created on a table)

Create Table PRIMARYKEY_MYTH(Column1 int NOT NULL,Column2 int)

create clustered index IX__PRIMARYKEY_MYTH__Col on PRIMARYKEY_MYTH(Column2)

Alter Table PRIMARYKEY_MYTH Add constraint PK__PRIMARYKEY_MYTH__Column1 PRIMARY KEY(Column1)

sp_help 'PRIMARYKEY_MYTH'
--Results of the sp_help

IX__PRIMARYKEY_MYTH__Col	clustered located on PRIMARY		                
PK__PRIMARYKEY_MYTH__Column1	nonclustered, unique, primary key located on PRIMARY	

Drop table PRIMARYKEY_MYTH

Here, it is very clear, Primary Key on a clustered table is only creating a nonclustered unique index on the table, not the Clustered index.

Hence, I would request you to pronounce the complete statement as

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 non clustered index..

I would like to request you to share your thoughts on the same.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s