?
Solved

10775: partition table (primary key or cluster index)

Posted on 2014-11-24
15
Medium Priority
?
113 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 4
  • +1
15 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40462383
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
ID: 40462398
So it is better to create the primary key?
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40462410
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40462416
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 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1336 total points
ID: 40462425
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
ID: 40462436
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 51

Expert Comment

by:Vitor Montalvão
ID: 40462470
@Phillip: that only happens if he creates the PK as clustered otherwise won't happen like that.
0
 

Author Comment

by:enrique_aeo
ID: 40462512
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 332 total points
ID: 40462520
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 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1336 total points
ID: 40462541
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 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1336 total points
ID: 40462567
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:Scott Pletcher
Scott Pletcher earned 332 total points
ID: 40462694
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
ID: 40470110
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
ID: 40470115
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 51

Accepted Solution

by:
Vitor Montalvão earned 1336 total points
ID: 40470146
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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

777 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