SQL Server Interview: Primary Key Column as Non Clustered Index, Non Primary Key Column as Clustered Index

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am sharing an important interview question for SQL Server Database Developer.

The question is:

Can we apply Non-clustered Index on Primary Key Column, Can we apply Clustered Index on Non-Primary key column?

Last year we arranged a walk in interview for SQL Server Developer. I had taken around 19 f2f interviews and prepared list of questions for another 88 f2f interviews.

I asked this question to all 19 candidates and 16 candidates said, we cannot create a Non-clustered Index on Primary Key because Primary Key has a default Clustered Index.
This is wrong, You can create Non-clustered Primary key and can apply Clustered Index on Non-Primary key column.

The main reason for this confusion is, SQL Server creates default Clustered Index on the Primary Key column.

Our answer should be like: Yes, we can define Non-Clustered Primary key column and can define Clustered index on other Non-Primary Key column.

But the best practice is to define a Primary Key as Clustered Index because Unique value + Physical Order of data returns good query performance.

In most of the cases, we are joining data using our Primary Key column, but If we require our main joining on other Non-Primary column and also require records basis on that Non-Primary column data order, we should apply Clustered Index on that Non-Primary key column.

T-SQL Script to create NonClustered Primary Key and Clustered Index Non-Primary key column: