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

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.

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

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.

B. not that easy, need recreate table.
C. what's 'linking field', you mean partition column?

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..

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.

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

Can't partition single table by multiple columns in sql2k5 nor sql2k8.