none
Partitioning Fact Table

    Question

  • Afternoon (London GMT)
    Just a quick question / advice

    Been asked to partition a rather large table (fact) . I have done some reading on partitioning and understand how it works
    but I'm just confused on how you would partition a table that already exists ??

    Ive set up the function and schema but not i want to break this table up (weekly)

    Do i have to do a massive insert into (could take forever) OR is there some Alter table sql that I'm missing

    Cheers everyone


    Akiraikonics
    Tuesday, September 22, 2009 10:57 AM

All replies

  • You basically have to create the partitioned tables, and then move the data from your main table to the new partition using insert into..select.
    You also need to drop the index on your existing table and create a partitioned index.


    Abdallah, PMP, MCTS
    Tuesday, September 22, 2009 11:26 AM
  • okay slightly annoying but if thats the way

    once the insert is done is there a way of seeing how the partition have been broken up - because to me even tho the function specifies how to break the data
    you still get one table


    Akiraikonics
    Tuesday, September 22, 2009 11:41 AM
  • Hi Akiraikonics

    You don't need to create a new table. You need to re-create the clustered index on your new partition scheme. This partitions the data.

    CREATE INDEX ix_name
    ON table(column)
    WITH (DROP_EXISTING = ON)
    ON partition_scheme_name ( column_name )

    http://msdn.microsoft.com/en-us/library/ms188783.aspx

    You'll also need to do the non-clustered indexes if they need to be aligned.

    HTH

    Ewan

    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    Tuesday, September 22, 2009 12:03 PM
  • I think that partitioning the clustered index could do well in your case, unless you have enough space and time to use the insert approach.

    Be aware that the number of partitions is limited to 1000.

    Check "Converting a Nonpartitioned Table to a Partitioned Table" in BOL.

    Modifying Partitioned Tables and Indexes
    http://technet.microsoft.com/en-us/library/ms175864.aspx


    You will find good stuff in Dan Guzman's Blog, that could help you to get familiar with this theme.

    Sliding Window Table Partitioning

    Automating Sliding Window Maintenance

    Automating RANGE RIGHT Sliding Window Maintenance

    Partition Details and Row Counts
    Tuesday, September 22, 2009 12:09 PM
    Moderator
  • His requirement is to partition the table, and tables are only partitioned when they are created.
    Abdallah, PMP, MCTS
    Tuesday, September 22, 2009 1:07 PM
  • Cheers i will try this and let you know how i get on


    Akiraikonics
    Tuesday, September 22, 2009 1:10 PM
  • Hello Abdallah

    OPs requirement for partitioning (in SQL2005/2008) can be achieved by rebuilding the clustered index.

    Please read the links provided in the post above by Hunchback.

    Regards

    Ewan

    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    Tuesday, September 22, 2009 1:15 PM
  • I see. But that basically means dropping the table and recreating it using the new clustered index in one step, right?
    Abdallah, PMP, MCTS
    Tuesday, September 22, 2009 1:40 PM
  • Hi Abdallah

    Well, that's one way of looking at it - but only if you consider rebuilding a clustered index to be same as dropping and recreating the table.

    The difference is that FKs, constraints and other NC indexes are preserved during the rebuild operation.

    HTH

    Ewan
    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    Tuesday, September 22, 2009 1:53 PM
  • Thank you Ewan.
    Abdallah, PMP, MCTS
    Tuesday, September 22, 2009 3:28 PM
  • Hey everyone i did the following  


    -- first create partition function and its range

     

    CREATE PARTITION FUNCTION

    Facttable_function

     

    (int)

     

    AS RANGE LEFT

     

    FOR VALUES

     

    (20070101, 20070201, 20070301, 20070401, 20070501, 20070601, 20070701, 20070801, 20070901, 20071001, 20071101, 20071201 -- =< 2007

     

    ,20080101, 20080201, 20080301, 20080401, 20080501, 20080601, 20080701, 20080801, 20080901, 20081001, 20081101, 20081201 -- = 2008

     

    ,20090101, 20090201, 20090301, 20090401, 20090501, 20090601, 20090701, 20090801, 20090901)--, 20091001, 20091101, 20091201 -- = 2009

     

     

     

    -- Create schema

    CREATE

     

    PARTITION SCHEME

    Facttable_Scheme

     

    AS PARTITION Factcdr_total_function

     

    TO ([PRIMARY], [PRIMARY]...etc how ever many is needed for the function-- recreate indx

    -- recreate indx

     


    CREATE

     


    CREATE


    CREATE

     

    INDEX ix_kdate

     

    ON dbo.FactTABLE(kdate)

     

    ON Facttable_Scheme (kdate) (nonclustered)


    Also found a hand report that analyzes this http://ssmspartcustomrpt.codeplex.com/


    Akiraikonics
    Tuesday, September 22, 2009 4:00 PM
  • Hi Akiraikonics

    This only creates a partitioned nonclustered index on the table. The table itself is still unpartitioned until you do the clustered index.

    HTH

    Ewan


    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    Wednesday, September 23, 2009 7:06 AM
  • Hi

    So created the clustered inx (it will took a while) my main issue is visibility as it is slightly different from cube partitions but of the same flavour

    What would be the best way in Updating the partitions on in coming data ?

    CREATE

     

    CLUSTERED INDEX indx_kdate

    ON

     

    dbo.FactTABLE(kdate)

    WITH

     

    (DROP_EXISTING = ON)

    ON

     

    FactTABLEScheme (kdate)


    Akiraikonics
    Wednesday, September 23, 2009 8:36 AM
  • Hi Akiraikonics

    After you partition the table, all new inserted/updated data is automatically distributed according to the partition scheme.

    HTH

    Ewan

    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    Wednesday, September 23, 2009 9:55 AM
  • Hi Akiraikonics

    If your question has been answered can you mark any helpful posts, and any answers, to close the thread. This keeps the forums tidy and helps those who experience the issue in future to find relevant answers.

    Regards

    Ewan

    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    Tuesday, September 29, 2009 3:01 PM