Strategy for SQL Server Partitioning

Question

I have the following requirement:

• We store daily order details for 10 companies for the past year.

Date              Company     Order
01-Jan-2010   Comp A       Order # 1
01-Jan-2010   Comp A       Order # 2
01-Jan-2010   Comp B       Order # 1
..
31-Dec-2010   Comp A       Order # 1
31-Dec-2010   Comp A       Order # 2
31-Dec-2010   Comp B       Order # 1

• The database is loaded with new or updated order details for one or more companies every hour. (NB: Each company could have potentially thousands of orders arriving in every hourly cycle)

• Currently we deal with this through SQL Server 2005 partitions as follows:

  • Create an archive table partitioned by the date column. This table will look like the table above.
  • Every hour collect together all newly arrived data into a separate table, also add unmodified rows from the archive, switch-out the current date partition in the archive table, then finally switch-in the newly prepared table.

This works well. However, since we are adding "unmodified rows from the archive" every hour this process is not optimal. For instance say we receive an order from only 1 company in a particular hour, according to the current implementation we end up copying accross the orders from all 9 other companies in order to populate the hourly table.

Can someone recommend a better way of doing this?

We have been considering creating a partition by Company instead of Date but then how do we handle the hourly switching process?

Answer 1

Ideally you would want to only switch in the newly added hour, w/o copying nor touching the previous data. But that would require 1 partition per hour and you would run out of partitions in 4 days. Even with SQL 2008 SP2 increased partitions support you could only store less than 2 years of data. As a stretch, with 3 tables and switching partitions from current to older and then to even older one you'd be able to cover 5 years, which is a typical data retention policy requirement, but I would not recommend this lightheartedly.

For the situation you're in, I actually think you are doing the best possible solution. What I would consider would be to prepare the next hour partition ahead of time:

• at H hour you start receiving the latest data and store it in table T. This table was prepared ahead of time with the existing archived data for the current partition (in other words, is a copy of the current partition).
• When the data receiving is complete, create a copy T' of table T, using a fast bulk operation (select into, bcp, insert bulk, openrowset bulk etc). This T' copy becomes the table to receive new data for the next hour.
• switch in the table T
• rename T' to T, you're ready for the next data to arrive.

Of course, I made an assumption here that once imported, the data is not modified, which is fairly common in most ETL scenarios.