Nonclustered Primary Key – SQL in Sixty Seconds #119

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

Nonclustered Primary Key - SQL in Sixty Seconds #119 119-NonclusteredPrimaryKey-yt-500x281 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)

, , , , ,
Previous Post
Recent Execution of Stored Procedure – SQL in Sixty Seconds #118
Next Post
Split Screen for SSMS Efficiency – SQL in Sixty Seconds #120

Related Posts

Leave a Reply

Menu