Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am partitioning a table based on a column that is not a primary key? I've read some conflicting information today on whether the partition column must be a part of the primary key. My gut says no, but I am not 100% sure. So questions...

  1. Must the partition column be part of the primary? Is it recommended one way or the other?
  2. Do I have to create an index for the partition key, or does the DBMS do it automatically on its own?
share|improve this question
    
In SQL Server 2008 and later, the partitioning key is the leading column of all partitioned indexes. That's how you can get an index seek on a heap sqlblog.com/blogs/paul_white/archive/2011/02/17/…. –  Paul White Sep 12 '12 at 5:37
add comment

3 Answers

up vote 8 down vote accepted

Not at all.

One of the most common scenarios for partitioning is to use a date field, which is totally unrelated to your PK.

For instance, if you have a table Orders with the field OrderDate you would most likely partition based on the month and year of OrderDate.

When records age out and are no longer relevant you can move those partitions off to an archive table or database so they are no longer processed.

Partitioning will work with pretty much any field, but in order for it to work WELL the field(s) you partition on should be used in most, if not all, of your queries. If you don't include your partition keys then you will get essentially an expensive table scan that goes across multiple tables (partitions).

EDIT

For part 2, I think the answer is no as well. The partition key is used to determine which partition to put the row in, but I don't think an index is maintained. There may be stats in the back end on it though.

share|improve this answer
    
... part 2 of the original question? –  ErikE Jul 11 '12 at 0:22
    
@ErikE Not sure but I added a note. I always put an index on it myself. –  JNK Jul 11 '12 at 1:11
add comment

It has to be part of a Candidate Key if not part of the Primary key itself. Idea being, your partitioning should align itself with the primary key.

So the answer is, yes, it is preferred to be part of the PK. If not another key, which is equally good enough to be a PK.

share|improve this answer
    
Never heard of the Candidate Key. How does one specify it in the Create/Alter table statement? –  AngryHacker Jul 9 '12 at 21:53
    
Candidate Key is just another key qualified to be a Primary Key. For example, ID is the primary key. But in the same table, if another column for eg. PERSON_ID also can uniquely identify a row, that is called Candidate Key. The 2nd and 3rd normalization rules should hold against all candidate keys also. –  srini.venigalla Jul 10 '12 at 15:29
    
Understood. What about the 2nd part of my question? –  AngryHacker Jul 10 '12 at 16:50
    
Same as any other index. example: CREATE INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor (BusinessEntityID); –  srini.venigalla Jul 10 '12 at 17:36
4  
This is absolutely incorrect. You can partition on many fields that are not related to the PK at all, such as OrderDate. Do you have anything to back up your claims? –  JNK Jul 10 '12 at 17:45
show 1 more comment

In addition to JNK's answer, you probably should read this article which discusses aligning table partitions and index partitions.

There are many types of scenarios where partitioning scheme does exactly follows the primary key's first column - for instance in a data warehouse scenario where the snapshot date of a fact table is usually the partition column as well as the first column in the primary key.

But equally, in OLTP environments where the PK is an IDENTITY or other surrogate key, it makes little sense to use this for the partition, since partitioning on arbitrary numbers is not normally terribly useful. In OLTP systems, you also tend to partition by date the most (probably not in the PK), but potentially also regionally or by some kind of organizational division (maybe in the PK if you aren't using a surrogate).

But it's not a requirement.

share|improve this answer
    
Well, a whole lot of stuff is not a requirement. Even indexing is not a requirement! To make sense functionally, partitioning has to be done on the leading column of a candidate key. Otherwise how would the app architect use the table? –  srini.venigalla Jul 11 '12 at 18:19
    
@srini.venigalla That's a common case, but another common case (equally so?) is to partition on something which is not a part of a primary or candidate key at all - because partitioning is often used for archiving, an expiry date can be a good partition choice. But there's nothing which implies that might be part of a key. Partitioning is a low-level feature which is pretty generic and there are at least two distinct and conflicting usage patterns here, both of which have legitimate best practices around them. –  Cade Roux Jul 11 '12 at 19:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.