How to handle archiving for seasonal database values on SQL Server

Question

I am on SQL Server 2008 R2 and I am currently developing a database structure which contains seasonal values for some products.

By seasonal I mean that those values won't be useful after a particular date in terms of customer use. But, those values will be used for statistical results by internal stuff.

On the sales web site, we will add a feature for product search and one of my aim is to make this search as optimized as possible. But, more row inside the database table, less fast this search will become. So, I consider archiving the unused values.

I can handle auto archiving with SQL Server Jobs automatically. No problem there. But I am not sure how I should archive those values.

Best way I can come up with is that I create another table inside the same database with same columns and put them there.

Example :

My main table name is ProductPrices and there a primary key has been defined for this database. Then, I have created another table named ProdutcPrices_archive. I created a primary key field for this table as well and the same columns as ProductPrices table except for ProdutPrices primary key value. I don't think it is useful to archive that value (do I think correct?).

For the internal use, I consider putting two table values together with UNION (Is that the correct way?).

This database is meant to use for long time and it should be designed with best structure. I am not sure if I miss something here for the long run.

Any advice would be appreciated.

Answer 1

I'd consider one of two options initially

• Use partitioning to separate the single table into current working set and archive data.
  No need to use an archive table

• Add validForm, ValidTo columns to implement a type 2 SCD
  Then add an indexed view for ValidTo IS NULL to get the current set of data

I wouldn't have 2 separate tables if all data has to be "on-line" in one database.

This leads to a 3rd option: an entirely separate database with all data. Only "current" data stays in live. (as @Mike_Walsh's answer explains)

The indexed view option is easiest and works with standard edition (with NOEXPAND hint)

Answer 2

gbn brings up some good approaches. I think the "right" longer term answer for you is the t3rd option, though.

It sounds like you have two business use cases of your data -

1.) Real time Online Transaction Processing (OLTP). This is the POS transactions, inventory management, quick "how did receipts look today, how is inventory, are we having any operational problems?" kind of questions and keeps the business running day to day. Here you want the data necessary to conduct operations and you want a database optimized for updates/inserts/etc.

2.) Analytical type questions/Reporting. This is looking at month over month numbers, year over year numbers, running averages. These are the questions that you ask as that are strategic and look at a complete picture of your history - You'll want to see how last years Christmas seasonal items did against this years, maybe even compare those numbers with the seasonal items from that same period 5 years ago. Here you want a database that contains a lot more data than your OLTP. You want to throw away as little history as possible and you want a database largely optimized for reporting and answering questions. Probably more denormalized. You want the ability to see things as they were at a certain time, so the Type 2 SCDs mentioned by gbn would be useful here.

It sounds to me like you need to create a reporting database. You can call it a data warehouse, but that term scares people these days. Doesn't need to be scary, if you plan it properly it doesn't have to take you 6 years and 6 million dollars to make ;-)

This is definitely a longer term answer but in a couple years you'll be happy you spent the time creating one. A good book to understand the concept of dimensional modeling and thinking about data warehouses and their terminology is The Data Warehouse Toolkit.