Post reply

table partitioning

  • March 16, 2013 at 3:37 am

    #1597795

    ratheesh4sql (3/16/2013)

    Based on the article from the link i hope we can do the table partition based on region into 3 partition and after a month we can archive into other three partition using Sliding Window Table Partitioning.....

    At the end of a month, you delete an entire region? Really? Are you sure that's what's done?

    You don't archive into other partitions with the sliding window, you archive into another *table* and delete that data from the main table completely.

    But here am confused beginning itself to do the partition using Region by group wise

    Region ID 1,2,3 = Partition1

    Region ID 4,5,6 = Partition2

    Region ID 7,8,9 = Partition3

    With that partitioning, at the end of a month, you delete completely regions 1,2 and 3?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 16, 2013 at 4:07 am

    #1597796

    I dont want to delete the data from the table i just want to archive into different partition since.

    Here the table partition

    A,B,C based on the region and then after one month need archive into D,E,F partition.

    Is this is possible ?

    So then A,B,C Partition will have only the latest data

  • March 16, 2013 at 4:28 am

    #1597797

    That's not how partitioning works. Please go and do some reading on partitioning.

    The sliding window method moves entire partitions from one table to another table. Emphasis on *move*. It's a quicker version of insert data into the archive table and delete from the main table.

    Having data moving from one partition to another in one table should be avoided wherever possible.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 16, 2013 at 5:24 am

    #1597799

    ratheesh4sql (3/13/2013)

    planning to do the partitioning for the main table with region wise.....So that we can split the data into a different file group and the data accessible also will be more fast ?

    Partitioning is primarily a data management tool. It can provide some performance benefits, but it usually doesn't. I would heed Kevin's advice and be sure you need this before you do it. It's a ton of labor to set up and will be even harder to break down if you don't see any benefits from it.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply