Solved

10775: partition table (primary key or cluster index)

Posted on 2014-11-24
15
107 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 49

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 49

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 49

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 49

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 49

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 49

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Search query matching words 20 38
MS SQL Server connect issues 4 37
MS SQL Sever Import/export problem 7 44
SQL Server Trigger 8 33
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

679 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