Results 1 to 9 of 9

Thread: table partitioning issue....

  1. #1
    Join Date
    Mar 2003
    Posts
    383

    table partitioning issue....

    Hi:

    SQL 2005 sp2 Ent. Need to partition on some large tables.
    Issues:

    1. Could we defined multiple functions for different range, and apply them to one table?

    2. The target table must provide unique key. Now, it is a composite pk with 4 fields together. How can I define the uniqe range for them?

    3. All mdf files are on E:\SQL_Data, which is SAN. how could I declare file groups on the same E:\ drive?

    4. Even if above are resolved, how can I convert existing tables to the new partitioned tables, or I don't need to do so?

    5. All all the fk relationships still carried over?

    Thanks for the help
    David

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    1. no.
    2. pkey is unique, but don't have to partition table based on pkey.
    3. why can't?
    4. recreate clustered index on partition schema.
    5. should be.

    You may like to recreate all non-clusted indices on partition schema too.

  3. #3
    Join Date
    Mar 2003
    Posts
    383
    Hi Rimao:

    It is getting somewhat clear now.

    A. thus, for existing ones, after partition, only need to recreate clustered index. No down-time for clients at all?

    B. Does it mean to drop all partition related func/schem binding..., will put back to un-partition mode?

    C. the range value should be focused on linking field, the most frequently to be used?

    Thanks for your help.
    -D

  4. #4
    Join Date
    Mar 2003
    Posts
    383
    It looks like that the existing tables must copy data to the new partition tables. Thus, there will be a tableName swapping issue, and will require certain 'down-time' for the application.

  5. #5
    Join Date
    Sep 2002
    Posts
    5,938
    B. not that easy, need recreate table.
    C. what's 'linking field', you mean partition column?

  6. #6
    Join Date
    Mar 2003
    Posts
    383
    C. Yes, I mean the partition column.

    A. thus, for existing ones, after partition, only need to recreate clustered index. No down-time for clients at all?

    It looks fro existing ones, need to create new partition tables on the new file groups, then insert all data, rename the existing ones to _old, get all FKs, Triggers, rename the new partition tables to the production name, and rebuilt FKs, indexes on it. Many steps involved..

  7. #7
    Join Date
    Sep 2002
    Posts
    5,938
    A. yes, there's down time if you want to convert regular table to partitioned one.
    C. choose partition column based on how you like to partition the table.

  8. #8
    Join Date
    Mar 2003
    Posts
    383
    Thanks Rmiao:

    You have answered my following question with "No".
    1. Could we defined multiple functions for different range, and apply them to one table?

    Maybe I was not quite clear on this question, Can we create a partitioned table, on the partitionSchema to more than 1 column?

    I guess the answer is No also, is there some difference on SQL2008 about it?

    Thanks
    -D

  9. #9
    Join Date
    Sep 2002
    Posts
    5,938
    Can't partition single table by multiple columns in sql2k5 nor sql2k8.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •