CODEWITHSUNDEEP
sql-serverpartitioningpartitioned-view
Vlad
Vlad

Reputation: 10780

How to create and maintain a partitioned view with last 7 days and archive records in SQL Server 2005?

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)?

Upvotes: 0

Views: 568

Answers (1)

Paweł Przybysz
Paweł Przybysz

Reputation: 26

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

Upvotes: 0

Related Questions