SQL Server Partitioning - Parent / Child tables

Question

I'm currently investigating implementing partitioning for a fairly hefty (800GB and growing) SQL Server database. All of the data in the tables is relatable to a date/time, however this date/time is often referenced in another table. For example, I have:

*Instances*
Timestamp (partition key!)
LocationID
...

*Events*
InstanceID
...

*Location*
ID
...

Here, instances contains the partition key. This references a location (LocationID > Location.ID). Also, there is the Events table which references the Instance (InstanceID > Instance.ID).

Is there any way I can apply partitioning to this structure without having to denormalise my tables and have the 'Timestamp' column copied everywhere?

I'm using SQL Server 2008 R2. Thanks in advance.

Answer 1

Well, I'm not saying this is pretty, but it might work.

First, partition the instance table by pseudo-datetime. Each day/week/month, load the new instance information. Then assign a new primary key id to this data. Normally, you would want this to be an identity, but in this case, you have to guarantee uniqueness. Now, place this into its own partition.

This is almost by date time. However, other tables that reference instances will be able to join to the correct partition.

Now, if the events and locations are relatively small tables, then perhaps this is enough.

Otherwise, you can repeat for these tables. However, it gets harder if you add a new event that refers to an older instance. To handle this, you'll want to assign a new event id range to each partition, but leave a big gap at the end. Then, when you load event data, you need to determine the correct partition to place it in, based on the partition of the instance id. You may end up loading events into multiple partitions.

This is an idea.