One of my clients of Comprehensive Database Performance Health Check recently faced a very interesting situation. I will describe the situation in detail a bit later on during this blog post. Today we are going to learn that it is possible to have a nonclustered primary key.
Real-World Story
One of my client who is running a large data warehouse recently asked to help them with the SQL server Performance. We started to investigate their performance and very quickly realized the issue was the primary key of their one of the tables.
As many of you probably know that SQL Server by default creates a clustered index on a primary key created on the table. This was the case with my client as well. They had a primary key that contained 10 different columns and now that was the primary problem they were facing.
Nonclustered Index Including Clustered Index Key
One of the very important concepts which we all keep on forgetting is that if we have a clustered index on the table and we create a nonclustered index, in that case, the nonclustered index will include the entire clustered index in it. This will increase the width and size of the nonclustered index very much which will eventually create many problems while reading and writing data to it. It is recommended that we create a clustered index on the narrow key as well as have the least number of columns in it.
Best Practices
A very common misconception is out there is that the Primary Key has to be clustered index only, which is not true. The primary Key can be a nonclustered index as well. You can watch this video which discusses the three different scenarios about primary key and clustered indexes.
One thing I must clarify that I do not encourage that Primary Key should be nonclustered index always. What I am trying to say here is that every case is different and based on your business requirements and performance benchmarking you may need a different solution.
In most cases, I am totally fine with the Primary Key as a Clustered Index Key.
Reference: Pinal Dave (https://blog.sqlauthority.com)
