Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com May 15 '12 at 16:17

This question came from our site for professional and enthusiast programmers.

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 May 15 '12 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. –  Keith Walton Apr 30 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.