10775: partition table (primary key or cluster index)

Hi experts:

I have a table with data. I created the partition function and partition scheme. What is most advisable to complete the partitioning
1. Create the cluster index? (note that I will not have primary key)
or
2. Create the primary key ?, then automatically cluster is created prior to admission
enrique_aeoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Do the Primary Key first. If you do the cluster index first, then SQL Server may have to re-order when you create the PK.
0
enrique_aeoAuthor Commented:
So it is better to create the primary key?
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Yes - it helps SQL Server in so many different ways. Otherwise you have what it technically known as a "heap", which is generally best used for a table where you are constantly adding data to, and speed of adding data is the main concern.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
My recommendation is to create an index (don't need to be clustered) on the same columns that you use for partitioning the data. Then use the same function to partition the index so it will be aligned with your partitioned data.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you do the cluster index first, then SQL Server may have to re-order when you create the PK.
A SQL Server table has the rows ordered by the cluster index (when exists one) so this statement isn't true. Tables aren't ordered by PK's but by clustered index and that's why you can only have one clustered index by table.
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
I'm thinking if he creates a clustered index, and then has to drop it if then wants to create a PK and sort it on that index.

But I take your point.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
@Phillip: that only happens if he creates the PK as clustered otherwise won't happen like that.
0
enrique_aeoAuthor Commented:
experts:

Finally what is the most recommended? when should use one or the other? Stay tuned to your early reply. I understand that the creation of a primary key helps me to join my tables so optimized
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Here's some guidelines about using clustered indexes: http://technet.microsoft.com/en-us/library/ms190639(v=sql.105).aspx

Note that it says "When you create a PRIMARY KEY constraint, a unique index on the column, or columns, is automatically created." though, as Vitor has pointed out, you can specify a nonclustered index at creation table.

My vote - unless you have a special reason, do the Primary Key with a clustered index.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Taking the good article that Phillip posted, I just want to call attention for the following:
Clustered indexes are not a good choice for the following attributes:

•Columns that undergo frequent changes

This causes in the whole row to move, because the Database Engine must keep the data values of a row in physical order. This is an important consideration in high-volume transaction processing systems in which data is typically volatile.


•Wide keys

Wide keys are a composite of several columns or several large-size columns. The key values from the clustered index are used by all nonclustered indexes as lookup keys. Any nonclustered indexes defined on the same table will be significantly larger because the nonclustered index entries contain the clustering key and also the key columns defined for that nonclustered index.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
There's no recommendation to use a clustered index for partitioning a table. But if there's any clustered index (PK or not) in the table, the recommendation is that the partition column should be part of the clustered index.
0
Scott PletcherSenior DBACommented:
The first thing you should do is determine the best clustering index for a table, partitioned or not.  All performance depends on that first.  Don't even worry initially if the clustering key is not unique by itself -- that can be tweaked later if needed -- just make sure you get the best key column(s).

You can add a PK later, if you need to.  Many tables don't need explicit PKs (except on a theoretical basis).
0
enrique_aeoAuthor Commented:
I had understood that join tables using JOIN is much better if we use primary keys or foreign, so in the partitioned table is better to create the primary key for me automatically create the index cluster.

Now I have another question, to unite tablascon JOIN primary keys, foreign or cluster index is used ?.

I'm really confused.
0
enrique_aeoAuthor Commented:
I had understood that join tables using JOIN is much better if we use primary keys or foreign, so in the partitioned table is better to create the primary key for me automatically create the index cluster.

Now I have another question; to join tables with "JOIN" primary keys, foreign or cluster index is used ?.

I'm really confused.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You "Join" tables by the fields that are common in two tables (an ID, a name, a date, ...). Typically a FK represents a relation to another table PK and that's why those fields are the ones to be chosen when you join tables but in cases that this rule doesn't apply you need to chose a non FK field.
Indexing the fields that are used in a JOIN will have a positive impact in the query performance, so it's recommended but not a demand.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.