Solved

10775: partition table (primary key or cluster index)

Posted on 2014-11-24
15
105 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
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 47

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 47

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 334 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 47

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 83 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 47

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 334 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 47

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 334 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 83 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 47

Accepted Solution

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Slow Connectivity over ODBC 8 38
Tsql query 6 22
SSRS Enable Remote Errors 4 26
Each Group By expression must contain at least one column that is not an outer reference 5 21
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

821 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