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?

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).


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.

| improve this answer | |
  • 10
    I know this is old, but it lead me down a wrong path so I thought I would comment for others. The Partition Column has to be in the Primary key if you want to use the SWITCH abilities of the Partition feature. If it is not in the primary key you will get this error: Partition columns for a unique index must be a subset of the index key. – Vaccano Jun 4 '15 at 21:21
  • I agree with @Vaccano – san Aug 20 '19 at 0:55

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.

| 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

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.

| 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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