none
SQL Partitioning

    Question

  • Hi,

    i Have SQL DWH where fact tables are partition on data. i have notices that from some time my new partition are not growing and all data in coming in to one partition.

    How i can rebuild the partition for some tables or all tables for which partition is defined?

    Monday, August 19, 2013 6:24 AM

Answers

  • For Rebuild You need to do below task

    --Drop index clus_index_Indexname on PK_Name (index Which is using in Partion)
    --For droping partion function and scheme first need to drop index which use partion scheme then create a index
    --then it make possiblle to drop partion function and scheme.
    --Create a index clus_1
    --Drop index [clus_1]  on tablename

    --DROP PARTITION scheme PT_SCH_Name

    --DROP PARTITION FUNCTION PT_FUN_Name

    CREATE PARTITION FUNCTION [PT_FUN_CRV_CurveData](datetime) AS RANGE LEFT FOR --Change as per your requirement
    VALUES (
    N'2007-01-01T00:00:00.000',
    N'2008-01-01T00:00:00.000',
    N'2009-01-01T00:00:00.000',
    N'2010-01-01T00:00:00.000',
    N'2011-01-01T00:00:00.000',
    N'2012-01-01T00:00:00.000',
    N'2013-01-01T00:00:00.000',
    N'2013-02-01T00:00:00.000',
    N'2013-03-01T00:00:00.000',
    N'2013-04-01T00:00:00.000',
    N'2013-05-01T00:00:00.000',
    N'2013-06-01T00:00:00.000',
    N'2013-07-01T00:00:00.000',
    N'2013-08-01T00:00:00.000',
    N'2013-09-01T00:00:00.000',
    N'2013-10-01T00:00:00.000',
    N'2013-11-01T00:00:00.000',
    N'2013-12-01T00:00:00.000',
    N'2014-01-01T00:00:00.000',
    N'2014-02-01T00:00:00.000',
    N'2014-03-01T00:00:00.000'
    )
    GO

    CREATE PARTITION SCHEME [PT_SCH_CRV_Name] AS PARTITION [PT_FUN_CRV_Name] TO --Change as per your requirement
    (
    [Partion1],
    [Partion2],
    [Partion3],
    [Partion4],
    [Partion5], 
    [Partion6],
    [Partion7],
    [Partion8],
    [Partion9],
    [Partion10],
    [Partion11],
    [Partion12],
    [Partion13],
    [Partion14],
    [Partion15],
    [Partion16],
    [Partion17],
    [Partion18],
    [Partion19],
    [Partion20],
    [Partion21],
    [PRIMARY])
    GO

    Tuesday, August 20, 2013 6:53 AM
  • If you wan't to change the boundaries of the partition you don't have to drop the partition index, scheme and function.

    You can simply use split or merge on partition function. More details on this in BOL Alter Partition Function.


    Regards, Dean Savović

    Tuesday, August 20, 2013 7:01 AM

All replies

  • For Rebuild You need to do below task

    --Drop index clus_index_Indexname on PK_Name (index Which is using in Partion)
    --For droping partion function and scheme first need to drop index which use partion scheme then create a index
    --then it make possiblle to drop partion function and scheme.
    --Create a index clus_1
    --Drop index [clus_1]  on tablename

    --DROP PARTITION scheme PT_SCH_Name

    --DROP PARTITION FUNCTION PT_FUN_Name

    CREATE PARTITION FUNCTION [PT_FUN_CRV_CurveData](datetime) AS RANGE LEFT FOR --Change as per your requirement
    VALUES (
    N'2007-01-01T00:00:00.000',
    N'2008-01-01T00:00:00.000',
    N'2009-01-01T00:00:00.000',
    N'2010-01-01T00:00:00.000',
    N'2011-01-01T00:00:00.000',
    N'2012-01-01T00:00:00.000',
    N'2013-01-01T00:00:00.000',
    N'2013-02-01T00:00:00.000',
    N'2013-03-01T00:00:00.000',
    N'2013-04-01T00:00:00.000',
    N'2013-05-01T00:00:00.000',
    N'2013-06-01T00:00:00.000',
    N'2013-07-01T00:00:00.000',
    N'2013-08-01T00:00:00.000',
    N'2013-09-01T00:00:00.000',
    N'2013-10-01T00:00:00.000',
    N'2013-11-01T00:00:00.000',
    N'2013-12-01T00:00:00.000',
    N'2014-01-01T00:00:00.000',
    N'2014-02-01T00:00:00.000',
    N'2014-03-01T00:00:00.000'
    )
    GO

    CREATE PARTITION SCHEME [PT_SCH_CRV_Name] AS PARTITION [PT_FUN_CRV_Name] TO --Change as per your requirement
    (
    [Partion1],
    [Partion2],
    [Partion3],
    [Partion4],
    [Partion5], 
    [Partion6],
    [Partion7],
    [Partion8],
    [Partion9],
    [Partion10],
    [Partion11],
    [Partion12],
    [Partion13],
    [Partion14],
    [Partion15],
    [Partion16],
    [Partion17],
    [Partion18],
    [Partion19],
    [Partion20],
    [Partion21],
    [PRIMARY])
    GO

    Tuesday, August 20, 2013 6:53 AM
  • If you wan't to change the boundaries of the partition you don't have to drop the partition index, scheme and function.

    You can simply use split or merge on partition function. More details on this in BOL Alter Partition Function.


    Regards, Dean Savović

    Tuesday, August 20, 2013 7:01 AM