enrique_aeo
asked on
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
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
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.
ASKER
So it is better to create the primary key?
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
But I take your point.
@Phillip: that only happens if he creates the PK as clustered otherwise won't happen like that.
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Now I have another question, to unite tablascon JOIN primary keys, foreign or cluster index is used ?.
I'm really confused.
ASKER
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.
Now I have another question; to join tables with "JOIN" primary keys, foreign or cluster index is used ?.
I'm really confused.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.