November 10, 2005 at 3:09 pm
Hello!
I am experimenting with the new partitioned table feature in 2005. I'm working with a particularly poor-performing database from one of our 3rd party vendor applications.
I already have my partition function, filegroups, and partition scheme created. My question is this - can I now partition one of the existing tables in this database, or do I need to create a new table using the partition scheme, move the data, and rename the table?
Any help or advice is greatly appreciated. If I find the answer, I will post it as well!
Thanks,
Maria
November 14, 2005 at 4:03 am
Try looking into this:
ALTER TABLE....
SWITCH PARTITION..
TO...
November 17, 2005 at 12:18 pm
Thanks for the suggestion. It looks as if this will only work if you switch the data to a table with only 1 partition. I need 6 partitions.
I found the following (below) in the SQL 2005 BOL; this may be useful to others but unfortunately, the clustered index option isn't a valid one for me. I'm working with a poorly-written vendor application that performs horribly with clustered indexes.
Thanks! Maria
Converting a Nonpartitioned Table to a Partitioned Table
You can turn an existing nonpartitioned table into a partitioned table in one of two ways.
One way is to create a partitioned clustered index on the table by using the CREATE INDEX statement. This action is similar to creating a clustered index on any table, because SQL Server essentially drops the table and re-creates it in a clustered index format. If the table already has a partitioned clustered index applied to it, you can drop the index and rebuilding it on a partition scheme by using CREATE INDEX with the DROP EXISTING = ON clause.
For information about clustered indexes, see Clustered Index Design Guidelines.
Another way is to use the Transact-SQL ALTER TABLE SWITCH statement to switch the data of the table to a range-partitioned table that has only one partition. This partitioned table must already exist before the conversion occurs, and its single partition must be empty. For more information about switching partitions, see Transferring Data Efficiently by Using Partition Switching. After the table is modified as a partitioned table, you can modify its partition function to add partitions, as described previously in Modifying a Partition Function.
To convert an existing table to a partitioned table
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply