Partition Function to an existing table

  • Table with 120 Million rows in SQL2005. This table grows around 400, 000 rows everyday. Insertion is made by a package every morning. In order to improve performance in the insertion into the table, I’m planning to do a partition. Table has clustered index (id, date).

    What's the best partition function for this case????

    Here is what I'm trying to do:

    -------------------------------

    -- partition function

    CREATE PARTITION FUNCTION function (date)

    AS RANGE LEFT

    FOR VALUES (week1, week2, week 3) ----> what I should write exactly in "week1"....

    -- partition scheme

    CREATE PARTITION SCHEME scheme

    AS PARTITION function

    ALL TO (week1, week2.....) -- file groups will be located in different drives

    -- move existing table/data to new partition

    ALTER TABLE

    DROP CONSTRAINT PK_constraint WITH (MOVE TO [scheme] (Date))

    note: how I move the data to the exact week ????? am I using the correct syntax?

    ALTER TABLE

    ADD CONSTRAINT PK_Constraint PRIMARY KEY(ID, Date)

    I hope to hear your advices

  • This is EXACTLY what I am trying to do, only by month rather than week. Did you ever get help with this?


    - JH

  • Please take this for an example:

    CREATE PARTITION FUNCTION [myPF1] (datetime)

    AS RANGE LEFT FOR VALUES ('20110731', '20110807', '20110814', '20110821');

    CREATE PARTITION SCHEME myPS2

    AS PARTITION myPF1

    TO ( fg1, fg2, fg3, fg4 );

    This should be what will each filegroup(fg) contain:

    FG1= DATA <=20110731

    FG2= DATA > 20110731 AND DATA <= 20110807

    FG3= DATA > 20110807 AND DATA <= 20110814

    FG4= DATA > 20110821

    You can also modify the partition scheme to include a next filegroup. hope this helps.

  • J Hines (8/25/2011)


    This is EXACTLY what I am trying to do, only by month rather than week. Did you ever get help with this?

    1) Please don't post a "me too" to a 2 year old thread - start a new one.

    2) Partitioning is a VERY advanced topic where you can get yourself into trouble (and even make things much worse) if you don't know what you are doing. PLEASE do yourself a favor and get some professional help for a little bit to make sure you know what you really need to do and how to properly implement/test/maintain it.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 4 posts - 1 through 3 (of 3 total)

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