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
Toggle Comment visibility. Current Visibility: Viewable by all users

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

2 Answers

·
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
Toggle Comment visibility. Current Visibility: Viewable by all users

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
Toggle Comment visibility. Current Visibility: Viewable by all users

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.