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:

Timestamp (partition key!)



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.

  • 1
    I don't think so...part of the point of partitioning is that your function can determine which partition to put a record in (or where it is currently) based on the field in the scheme. If the field in the scheme isn't in the partitioned table, how will it know where to put it? What will happen if you try to insert a row and the instances table is renamed or offline?
    – JNK
    Commented May 15, 2012 at 13:12
  • How are other people handling this? I have the same issue. 36 tables related 1 or more levels below a root table with a time stamp. I want to use partitioning to archive data and reduce my database size, but adding my time stamp to 36 tables seems like a step in the wrong direction. Commented Apr 30, 2014 at 23:29

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.