Can we create Non clustered index on Primary Key column?

6
615

Aim :- This article will hit on the topic related to Indexes in SQL Server. Question discussed in this post is that Can we create a Non clustered index on Primary Key column?

Solution :-  YES

Description :- Absolutely YES, we can create non clustered index on primary key column. I have seen this question in one of the Facebook’s group. A big thanks to the person who asked this question. As rightly said, “The more you ask, the more you get”. This is the reason we made Question Laboratory (Q Lab), so as to discuss scenarios practically.

Many of the beginners or intermediates or even experts to SQL Server still have confusion about Indexes (Clustered/Non clustered) in SQL Server. There is a reason why I am saying that many people have the same doubt. The reason behind this is when we start our journey with Constraints, at that time if we create a “Primary key” on any column then automatically SQL Server creates “Clustered index” on that particular column.

So many people thinks that Primary key column only creates “Clustered Index” and it cannot create “Non-Clustered index” in SQL Server. But, the true answer is that it can also create Non-clustered index on Primary key column.

  1. Case 1 Clustered index on primary key || When we do not specify any index explicitly after the “Primary Key” keyword. This is a default behavior i.e. Clustered index will be created automatically once a primary key is created for the table.
  2. Case 2 Non-Clustered index on primary key || When we explicitly specify the “Non-clustered” key word after Primary keyword.

Steps to implement above 2 cases

CASE 1 :- Create Clustered Index on Primary key (Default behavior).

We are taking a Primary Key on ID column without specifying any Index keyword after Primary key and will see what type of Index will be created.

  • Open SSMS (SQL Server Management Studio) and create one table called “Phpring_Employee” with two columns.
  • Name these 2 columns as say ID and NAME .
  • Now, we need to check for Index on Phpring_Employee table whether it is a clustered or non-clustered index.
  • To do this, we can use SP_HELPINDEX proc to check for indexes on any table, but make sure that you are in correct database (where your table exists).
  • If you get any doubts while doing the above steps, take a look at the below screen shot
Create Clustered index on primary key column
Create Clustered index on primary key column
  • In the above figure, you can observe that the table “Phpring_Employee” has been created.
  • We can also see that the index is created on the table and type of index is “Clustered” index (Check in “Index _Description” column).
  • So, this is the default behavior of “Primary Key” i.e. Automatically a clustered index will be created once a primary key is created for the table.

CASE 2 :- Create Non Clustered Index on Primary key

We are taking a Primary Key on ID column and explicitly specifying Nonclustered Index keyword after Primary key. Now, we will see what type of Index it will create.

  • Drop table “Phpring_Employee” ( As this was created for above Case 1) or create a new table with different name.
  • I have dropped this table and now will again create “Phpring_Employee” table by taking Primary key on ID column along with Non Clustered keyword.
  • Use SP_HELPINDEX proc to find out the type of indexes created on the Phpring_Employee table.
  • Just follow the below picture, if you get any doubts while performing the above steps.
Create Non clustered index on primary key column
Create Non clustered index on primary key column
  • In this picture, you can observe that we have specified the NonClustered keyword along with primary key (ID column).
  • Also, we can observe that the Non-clustered index has been created on ID column (Check index_description column).

Conclussion :- Finally, we can conclude that it is possible to create Non clustered index on primary key column if we specify the non-clustered keyword after primary key explicitly. Else, by default it will create a clustered index whenever primary key is created for a table.

This is the end of the article on Can we create Non clustered index on primary key column in SQL Server. I hope you enjoyed the above practical implementation. If you have any questions which you want to discuss practically, just ask below in the form of comment or you can post it on our Facebook page too.

Edited by – Chander Sharma

View articles written by Chander Sharma

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 9.6/10 (8 votes cast)
VN:F [1.9.22_1171]
Rating: +5 (from 5 votes)
Can we create Non clustered index on Primary Key column?, 9.6 out of 10 based on 8 ratings

6 COMMENTS

  1. Hi Avinash,

    Great posting. Once we created primary key as non-clustered index, can we create clustered index on other columns of the table?

    Thanks,
    Praneeth.

Comments are closed.