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.