0

I have a rather large table which I need to query for a reporting application. Most of the time users will be interested in last 7 days worth of data but they want to be able to query the older (archive data) every now and then. Since they want everything to be blazing fast for recent data but don't mind the wait for archive records, my guess is I should be fine with a partitioned view (data) and two tables (data_current and data_archive).

I know how to created the initial tables (constraints and all) and the view. How do I go about automating the daily maintenance (move older data from_current to _archive)?

Daniel Widdis
  • 8,424
  • 13
  • 41
  • 63
Vlad
  • 10,602
  • 2
  • 36
  • 38
  • Partitioned Views do exist (and have done for some time), but they're an ugly crutch. Have you considered a [Partitioned Table](http://msdn.microsoft.com/en-us/library/ms188730(v=sql.90).aspx)? – Damien_The_Unbeliever Mar 26 '12 at 10:22
  • I thought it was easier to achieve what I want using a partitioned view. It will also be easier for my coworkers to work with (since they are already familiar with these). – Vlad Mar 26 '12 at 11:13

1 Answers1

0

You should use sliding window pattern. Write a stored procedure to swap partitions between tables and use a job to schedule execution.

DaveShaw
  • 52,123
  • 16
  • 112
  • 141
Paweł Przybysz
  • 26
  • 1
  • Nice example but since the view doesn't have to be updatable (all inserts come via `_current`, there are no updates) I might be better off dropping the constraints on both tables, moving the rows from `_current` to `_archive` and adding new constraints back to the tables. I can do this off-peakhours. – Vlad Mar 26 '12 at 11:24
  • Note that swapping rows between partition tables is not actually moving data but switch whole partition from one table to another. It is very fast and scalable. So I would rather use a partition table _current and _archive and a view as a union of those tables. – Paweł Przybysz Apr 04 '12 at 17:02