One Step To Understand Relationship Of Primary Key And Non-Clustered Indexes.

Keys and indexes are key fundamental concepts of any RDBMS and play very crucial rule to design good and efficient database. If you have to design performance oriented database then you should have to implement keys and indexes in proper manner in your database.

Developers often do not specify which column should have clustered index. As we know by default Primary Key becomes Clustered Index but vice versa is not true that means you can create clustered index on non primary key column also. This practice is now extremely common and lots of people have forgotten that Primary Key and Clustered Index are two different things.

Another common misconception most people have is “Creating a primary key on a table makes reads fast”.

I want to clarify, Primary key is a constraint, and it has nothing to do with fast reads. “Primary Key” behind the scenes creates a clustered Index which makes the reads fast. Optionally we can also create non-clustered for primary key which contributes to faster reads.

One common and simplest question asked by any interviewer that “what is a Primary key” and most of us reply very quickly with following facts:

  • The primary key constraint uniquely identifies each record in a database table.
  • Primary keys must contain unique values.
  • A primary key column cannot contain NULL values.
  • Most tables should have a primary key, and each table can have only ONE primary key.

But there are few more questions comes in mind about the primary key and indexes as per below:

  1. Can we create primary key and clustered index two different columns?
  2. Is it mandatory to create clustered index on primary key?
  3. Can we create non-clustered index on primary key?

These are the question that creates the confusion and most of the candidates are not confident to say anything.

First of all we have to remember below statement and always keep in mind.

“Primary Key can be clustered or Non-clustered but it is a common best practice to create a Primary Key as Clustered Index. “

In short we can create primary key and clustered index in two different columns and it is not mandatory to apply both of things in same column.

It is not also mandatory to create clustered index on primary key and clustered index can be created on non primary column.

Yes definitely non-clustered index can be created on primary key.

There are following scenario about primary key and Clustered Index:

1. Primary key default to Clustered Index:

This is common scenario in which developers always create primary key and clustered index automatically apply on primary key column. There is no explicitly need to create clustered index on primary key column.

This is a main reason that developers not aware that primary key and clustered index are two different things.

1

You can see in above output result that clustered index is created automatically whenever primary key applied but always remember that vice-verse is not true.

2. Primary key default to Non-Clustered Index:

This is not mandatory that primary key and clustered key is co-related to each other and clustered index only created on primary key. In this scenario we will explicitly defined Primary Key as a non-clustered and it will create it as a non-clustered index.

You can clearly identify that non-clustered index is created on primary key and stored in Heap.

 2

Note: Heaps (Tables without Clustered Indexes) A heap is a table without a clustered index. One or more no-clustered indexes can be created on tables stored as a heap. Data is stored in the heap without specifying an order.

3: Primary Key defaults to Non-Clustered Index with another column defined as a Clustered Index.

In this scenario we will explicitly defined unique Key as a clustered index and another primary key column automatically assign a non-clustered key. It means if any table already have clustered index and primary key is created on the table then by default non-clustered index created on primary key.

3

In above result we can see that no clustered key is created on primary key while clustered key is created on unique key column.

4: Primary Key defaults to Clustered Index with other index defaults to Non-clustered index.

If any table has primary key and unique key and not applied any index explicitly then in this case clustered key is goes with primary key column and non-clustered key goes with unique column.

4

As per the above scenarios we can say that primary key is not strictly bound with clustered key. Developers have to apply both of things as per the situation.

To achieve optimal performance, it is better to go with primary key along with clustered key.

Advertisements

One thought on “One Step To Understand Relationship Of Primary Key And Non-Clustered Indexes.

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