Should my primary key be clustered?

The default in Management Studio is to make the primary key clustered, but is this a good idea? What are the pros and cons of making the primary key clustered v nonclustered?

more ▼

asked Oct 09, 2009 at 12:00 PM in Default

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 78 79 82

(comments are locked)
10|1200 characters needed characters left

7 answers: sort newest

The classic DBA answer - it depends.

Strictly speaking every table should have both a PK and a clustered index, but not always necessarily using the same field.

If you are using GUIDs (shudder) as your PK, then most definitely no! The fragementation can become horrendous.

If using identity, then combining the PK and clustered won't do you much harm.

If using some other natural/surrogate key then you will have to make an informed decision depending on the values in those keys, if they are as random as GUIDs, then you'll have the same issues as GUIDs.

more ▼

answered Oct 11, 2009 at 06:41 AM

Kev Riley gravatar image

Kev Riley ♦♦
53.9k 47 49 76

(comments are locked)
10|1200 characters needed characters left

My answer is: It depends, see clustered index performance

Sorry for reffering to a different site, but it is a good explanation.

more ▼

answered Oct 19, 2009 at 06:22 PM

Håkan Winther gravatar image

Håkan Winther
15.7k 35 37 48

(comments are locked)
10|1200 characters needed characters left

I vote: It depends. "Primary key" tells us nothing about how many columns or what type of columns or how they will be used, etc. Therefore there is no sound basis for saying that a primary key should or should not be clustered. Other considerations would decide. The fact of being a primary key is mostly irrelevant to choosing an indexing strategy.

more ▼

answered Oct 13, 2009 at 06:24 PM

David 1 gravatar image

David 1
1.8k 1 3

(comments are locked)
10|1200 characters needed characters left

Most of the time, yes. Since the cluster is the primary avenue of data access inside SQL Server and in most cases, when you're pulling from a particular table you're going first to the primary key, it makes the most sense to have that as the cluster as well. However, other circumstances can lead to different conclusions. If, for example, the most frequently access path, the column(s) used most often to retrieve data from the table was a foreign key, to eliminate the key lookups necessary to retrieve the data from the table, it might make more sense to make the foreign key the cluster.

In my opinion, data access paths define where the cluster should be.

more ▼

answered Oct 10, 2009 at 08:22 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

(comments are locked)
10|1200 characters needed characters left

I'd mildly disagree with Matt's answer. He's not necessarily wrong, but with the items table putting the clustered index on TransactionID might cause fragmentation because you can't be guaranteed that the items will be inserted in order of TransactionID meaning there will be more page splits.

Also a non-clustered index on TransactionID in the items table would suffice for joining to the Transactions table.

more ▼

answered Oct 09, 2009 at 06:15 PM

Jack Corbett gravatar image

Jack Corbett
1.1k 2 2 3

I should have probably mentioned using a fill factor to overcome that - very true!
Oct 09, 2009 at 06:59 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 09, 2009 at 12:00 PM

Seen: 24828 times

Last Updated: Oct 15, 2009 at 11:02 AM