question

equipe9 avatar image
equipe9 asked

Clustered vs Non Clustered Indexes for a Primary Key

0 down vote favorite

I have a large table with around 19 Million records in it.

The primary key is a unique identifier column called ID (data type is unique identifier NOT int) and there is a non clustered index setup on this ID column.

The table currently has no Clustered Index on it.

I am considering changing this column from a non clustered index to a clustered index since the table currently has no clustered index but am wondering what are the pros and cons of doing this or if I should.

  1. Leave things as is
  2. Find another column to create the clustered index on
  3. Change this ID column over from a non clustered index to a clustered index.

If anyone can give me some guidance as to what are some considerations for making this decision, I would appreciate that greatly.

Thanks in advance,

sql serverindexesprimary-keyclustered-indexnonclustered-index
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jon Crawford avatar image
Jon Crawford answered

do you have queries that use the ID to find the objects exclusively? Is there a business reason to need a key that is not based on the ID? Do you really need all 19 million rows? Can you partition the table and index in a way that makes more sense for how it is actually being used?

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

equipe9 avatar image
equipe9 answered

I inherited the database from someone else and the application uses this GUID as a primary key with relationships to matching foreign keys in respective child tables so the queries use the uniqueidentifier id's to make joins. I don't think partitioning would help us in this case and just wonder if making the current ID as a clustered index instead of as a non clustered index which it is currently would help or not performance wise?

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.