Post reply

Table Partition - SQL Server 2005

  • March 18, 2009 at 4:01 pm

    #129900

    Hello All,

    I have a situation. The tables' size in my database(SQL Server 2005) is growing larger day by day with the addition of new regions resulting in performance degrade in Data Loads. I'm validating on Table Partition feature in SQL Server 2005 to partition the data region wise for the better performance.

    Could anyone please help me by clarifying on following items on Table Partition.

    1. Pros and Cons of Table Partition.

    2. Can I at all do this with out having to modify the existing code(SPs, SSIS packages) or object name changes

    3. How do I move my existing tables to newly created Partition Schemes without dropping and re- creating them.

    4. Do I need to create different filegroups for each of the partition. If so, how can I redirect the data to go into specific filegroup.

    Your help is greatly appreciated.

    Thanks in advance,

    -Amith Vemuganti

  • March 18, 2009 at 4:28 pm

    #962330

    Starting Point: http://msdn.microsoft.com/en-us/library/ms345146.aspx


    * Noel

  • March 19, 2009 at 10:04 am

    #962850

    Thanks for the link Noel! Could you tell me how I can move the existing non-partitioned table to a partitioned scheme. I read an article saying we can do that by dropping the clustered index on the table and recreating the index on a partition scheme will move all the data to the specific partitions. So, is it not possible to create a table partition w/o the clustered index? Please advise.

    Thanks again,

    -Amith Vemuganti

  • March 19, 2009 at 2:27 pm

    #963131

    Amith Vemuganti (3/19/2009)

    Thanks for the link Noel! Could you tell me how I can move the existing non-partitioned table to a partitioned scheme. I read an article saying we can do that by dropping the clustered index on the table and recreating the index on a partition scheme will move all the data to the specific partitions. So, is it not possible to create a table partition w/o the clustered index? Please advise.

    Thanks again,

    -Amith Vemuganti

    The clustered index MUST exists for partitioning.

    When you re-create the clustered index you are basically migrating the data in to the partitioned structure.


    * Noel

  • March 26, 2009 at 10:24 am

    #966827

    Could anyone please tell me if a partition table can use identity column. Also, can foreign keys refer the partition table.

    Thanks,

    -Amith Vemuganti

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

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