Solved

10775: partition table (primary key or cluster index)

Posted on 2014-11-24
15
102 Views
Last Modified: 2014-11-28
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
0
Comment
Question by:enrique_aeo
  • 6
  • 4
  • 4
  • +1
15 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
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
 

Author Comment

by:enrique_aeo
Comment Utility
So it is better to create the primary key?
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 334 total points
Comment Utility
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
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
@Phillip: that only happens if he creates the PK as clustered otherwise won't happen like that.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:enrique_aeo
Comment Utility
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
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 83 total points
Comment Utility
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
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 334 total points
Comment Utility
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
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 334 total points
Comment Utility
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
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 83 total points
Comment Utility
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
 

Author Comment

by:enrique_aeo
Comment Utility
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
 

Author Comment

by:enrique_aeo
Comment Utility
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
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 334 total points
Comment Utility
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now