Can I partition table which is referenced by another table.

It depends on what you want to accomplish with your partitioning. If you don't want "partition switching", you can use FK relations as long as you follow the rule below.

Qoute from books online:

Source and target tables must have the same FOREIGN KEY constraints. If the target table has any FOREIGN KEY constraints, the source table must have the same foreign keys defined on the corresponding columns, and these foreign keys must reference the same primary key as those of the target table. The foreign keys of the source table cannot be marked is_not_trusted (viewable in the sys.foreign_keys catalog view), unless the corresponding foreign key of the target table is also marked is_not_trusted. For more information about this setting, see Guidelines for Disabling Indexes. SQL Server applies any CASCADE rules that are defined on the foreign keys of the target table to the newly moved partition.

But if you plan to do fast "partition switching" you have some additional requirements regarding foreign keys:

• No primary key/foreign keys defined if source table has the primary key.
  There can be no active primary
  key/foreign key relationship between
  the source table and the target table in which the source table holds the
  primary key.
• No primary key/foreign keys defined if target table has the foreign key.
  There can be no active primary
  key/foreign key relationship between
  the source table and the target table in which the target table holds the
  foreign key.
• No foreign key from another table can reference the source table. The source table cannot be referenced by
  a foreign key in another table.

As you can see, in this case, you can't use FK relations.

reference : http://technet.microsoft.com/en-us/library/ms191160.aspx

Depending on how you partition them, you may end up dropping the unique constraint/primary key/unique index on Tab1 which allows Tab2 to reference it. If you drop that, then you will have to end up dropping the foreign key while you do it anyway.

My advice to you would be to try to end up with a script which does the partitioning in a transaction before you roll it out to a production system - that way any failures will be backed out.

Hi Matt, Thanks for your advise. What do you mean by script which does the partitioning in a transaction? These tables have continuous bulky inserts; are you suggesting to write a script to remove releationship between them before each insert and recreate it once insert is done? Is there a better way to do it? Thanks, Shailendra