• two databases: DB_A and DB_Archive with one very big table called tableA.
  • every day, records older than 60 days are deleted from DB_A and moved to DB_Archive mainly to leave thing "separated" because tableA is heavily queried on DB_A for records of the past 2 months.

I want to get rid of this process because it is slow and consumes a lot of resources. I'm thinking of implementing table partitioning on DB_A with a partition function on a date column and storing all records < 2 month on one partition and all records > 2 months on another partition. My questions:

  • is this scenario going to behave like if I had 2 different databases? If I query my tableA for records > getdate() - 30, is it going to read the archiving partition?
  • I supposed I have to partition the indexes as well, right?
  • How do I deal with the fact that tomorrow my partition function will "change", I mean, if I create the function today (2nd of July, its range will be 2nd of May, but tomorrow would be the 3rd of May). Can I create a dynamic partition function?
  • I don't think a dynamic function is a good idea even if it were allowed (I don't think it is)...we can get into more detail shortly but I think you probably should partition based on calendar date and move off one partition at a time...But there are a variety of options here.
    – JNK
    Commented Jul 2, 2012 at 15:04
  • I scripted up an example along the lines of what you want to do last year. It was a somewhat special case whereby we wanted to keep x days of data on a fast (expensive) array and move archive data to cheaper storage. If I can sanitize an example script I'll post it, otherwise it'll just be a summary of the process. Commented Jul 2, 2012 at 18:22
  • hi mark, yes please do, and if you can share your experience as well. was it successful?
    – Diego
    Commented Jul 2, 2012 at 21:05
  • It works but was ultimately unnecessary (we took a simpler route). Perhaps you could expand on why the 60 day boundary exists in your case? Would help everyone point you in the right direction. Commented Jul 2, 2012 at 21:31

4 Answers 4


With partitioning you would have to do a partition per day, which puts the Pre-SQL 2012 limit of 1000 paritions in a new perspective as it would only allow for 3 years archive. With SQL Server 2012 you get 15000 partitions which is plenty for 1 partition per day.

Every day you would add a new partition. If you want to move the 61st past day partition you can do it efficiently, but is still an offline operation. See Move a Partition to a Different File Group Efficiently.

All your indexes would have to be aligned, see Special Guidelines for Partitioned Indexes.

Buying into partitioning is not an easy decission and it may be quite a big bite to chew... see How To Decide if You Should Use Table Partitioning. Specifically you should not expect performance improvements from partitioning. You should approach performance problems on time seriest by clustering by datetime.

  • The new limit is available in 2008 SP2 and 2008 R2 SP1. blogs.msdn.com/b/hanspo/archive/2010/11/29/…
    – Jon Seigel
    Commented Jul 2, 2012 at 16:23
  • @Jon: the in 2008 SP2, 2008R2 SP1 implementation comes with a big warning . As explained in this white paper, there are implications on certain features, including performance.. The SQL 2012 support comes with no warnings. Commented Jul 2, 2012 at 16:30
  • Thanks for pointing that out; it's true there are some caveats to using it on 2008/2008 R2, but it's an available option if necessary.
    – Jon Seigel
    Commented Jul 2, 2012 at 16:49
  • thanks for your comment. I'll read the material comment later on
    – Diego
    Commented Jul 4, 2012 at 9:30

I don't know if the partition function can be dynamic but I doubt it. Some options for you without going that route:

1 - Partition on calendar DATE and move off the oldest partition each day

2 - Create a view that filters on date, and point all your existing queries there (this can be easily managed by renaming the underlying table to something else and naming the view what the current table's name is). This can be optimized as well with index changes.

Bear in mind that the first option above will work a LOT better if you use the date field in your queries. If you don't it'll still be quicker than the current process but queries won't have a huge improvement. Partitioning in general works best if you can filter on your partition field and the optimizer knows which partition to look at.

  • I would like to avoid "each day" manual operations
    – Diego
    Commented Jul 4, 2012 at 9:30

Here's what should work for you: DB_A - tableA with a different partition for each of the last 60 days - stagingTable to move data from oldest partition

DB_Archive tableA - stores all data older than 60 days. (not partitioned)

Process: 1. before end of day: alter partition function - split range to add a new partition for the new day. (NB: instead of creating partitions for "today's date + 1 day" you may want to be a few steps ahead. eg: "today's date + 5 day"

  1. After end of each day, you first switch oldest partition in DB_A.tableA to DB_A.stagingTable; Merge the oldest partitions.

  2. Import data from DB_A.stagingTable to DB_Archive.tableA. Finally trunacte DB_A.stagingTable

The above is called Rolling Window and is a pretty common scenario for VLDB's. See this white paper by microsoft on partitioning: Partition table and index strategies or try this specifically on Sliding Window scenario


You can use dynamic approach of archiving and purging data in SQL Server. Please follow below link for that.


  • 1
    Could you please include in your answer the main points of that post? You know, links come and go and when they go, your post will have only a dead link. Commented Oct 17, 2013 at 11:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.