Post reply

Table partitioning question

  • March 27, 2018 at 8:45 am

    #410365

    Standard steps for adding a new partition are create the file group, add the file, change partition scheme and function, rebuild tables (clustered index). But, if you don't have any data yet that would belong in the new partition, do you really need to rebuild the tables? I don't think so..

    Thanks!
    Marius

  • March 27, 2018 at 1:29 pm

    #1984715

    Can you give a reference for those 'standard' steps for splitting a partition?

    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 27, 2018 at 6:33 pm

    #1984738

    I've been trying to find what I read, now I think I must have misinterpreted the fact that you can/ have to rebuild a table on the newly created partition scheme in order to partition it. I must have extrapolated that, getting to wrong conclusion that if you add a new partition by ALTER ... SPLIT you then have to do a REBUILD to move the data to the new partition. Now I know this is wrong, the ALTER... SPLIT will actually do that... Thanks!

  • March 28, 2018 at 6:00 am

    #1984759

    That makes sense.

    Yes, to implement the partitioning, you need to rebuild the heap/clustered index, as well as any nonclustered indexes you want partition-aligned, onto the partition scheme.
    Splitting a partition just requires altering the scheme and function.

    P.S. When you do split, it's recommended that you ensure that no data will belong in the newly created partition. You don't want data moving as a result of a split.

    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 28, 2018 at 7:31 am

    #1984784

    Thank you. Yes, I'm aware of that, Microsoft says the SPLIT can be resource intensive, but it doesn't say it moves the data, hence my confusion.
    I am having the PRIMARY partition as the highest partition number in a RANGE LEFT, containing no production data, and splitting it every time I need to add a new partition (so it should only contain the system tables, I know the other partitions are holding the correct data, I tested it). I hope that's the right way to do it, this is the first time I'm partitioning... Thanks again!

  • March 28, 2018 at 7:39 am

    #1984790

    Marius.D - Tuesday, March 27, 2018 8:45 AM

    Standard steps for adding a new partition are create the file group, add the file, change partition scheme and function, rebuild tables (clustered index). But, if you don't have any data yet that would belong in the new partition, do you really need to rebuild the tables? I don't think so..

    Thanks!
    Marius

    You don't need to rebuild the CI to add a new partition to an already partitioned table.  You certainly don't need to do such a thing on a new table because, ostensibly, you building a new table and the related clustered index that goes with it.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 28, 2018 at 11:01 am

    #1984857

    Thank you!

Viewing 7 posts - 1 through 6 (of 6 total)

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