SQL Server Performance

How to archive old data on top of existint table partition?

Discussion in 'ALL SQL SERVER QUESTIONS' started by Thanh Nguyen, Jan 16, 2013.

  1. Thanh Nguyen New Member

    Hi Experts,

    Can someone give me some high-level direction on how to approach this?

    I'm given a task to do these 2 things:
    - 1. Separate customers data across multiple filegroups using table partition (based on CustomerID)
    - 2. Archive customers data (using table partition if possible) (based on CreatedDate)

    I have a very clear idea on how to do either 1 or 2, but not both. Is that even possible to partition on top of partition? If not, what's the best way to approach this?

    Thanks,
    Thanh
    Thanh Nguyen, Jan 16, 2013
    #1

  2. Shehap Moderator

    Fundamentally, partitioning is one of the best ways for archiving that can’t impact tangibly on production DB systems and you have mainly 2 choices of partitioning function :

    · Either to partition your table on an identity column and specify an appropriate number of partitions and range of each partitioning according to your archiving strategy such as :

    CREATE PARTITION FUNCTION [Identity_function](int) AS RANGE LEFT FOR VALUES (1000000, 2000000,3000000,4000000)

    · OR to partition your table on a data columns but you should specify precisely the date borders of archiving either monthly , quarterly, yearly or whatever is suitable for your archiving strategy such as :

    CREATE PARTITION FUNCTION [Identity_function](date) AS RANGE LEFT FOR VALUES ('2013-01-01', '2013-02-01','2013-03-01','2013-04-01')

    Then , you can switch out easily and transparently the needed partitions at a time ..

    Please let me know if any further help is needed
    Shehap, Jan 17, 2013
    #2

Share This Page