SQL Server 2005 Partition table by foreign referenced data

Question

is there a canonical way to partition a table by referenced data to another table?

for example

timetable
   id
   datetime

bigtable
   id
   timetable_id -- foreign key
   .. other data ..

i want to partition bigtable by the datetime in timetable. thankx.

Answer 1

As noted here, he partitioning column must be a part of all unique indexes on the table, which would include your primary key.

I think your only choice here would be to denormalize the timetable.datetime column into bigtable so that the column is available for partitioning.

Answer 2

Partitioning key must be a column of the table being partitioned. If you have a correlation between id and datetime in timetable then you can partition by that id:

id          datetime
1           20091001
2           20091002
3           20091003
...
32          20091101
33          20091102
...
62          20091201
...

then the partitioning ranges (2009-10-01, 2009-10-31), (2009-11-01, 2009-11-30) etc can be expressed in terms of id values: (1,31), (32,62), ... However, this requires that the rank of the id values matches exactly the rank of the datetime values. If you don't have this correlation, then you must either move the datetime column in the bigtable, or rearrange your id so that they correlate wqitht he datetime.