convert a non-partitioned tables into partitioned Options

Hi,
we have a large database containing many tables and lots of data. now
because of the number of records we decided to use partitions.
for this we have created partition function and partition scheme. the
problem is : How can we convert non-partitioned table into
partitioned one according to a partition scheme?
in the documentation it introduced 2 methods but I don't know are the
usable or not? if they are, how can I use them?
the clustered index is the primary key and there are some
foreign keys which are dependent on this one so it can't be droped.
and the base field which the table is going to be partitioned on that
is not primary key.
the other question is: when creating a table if we do not have primary
key , it
accepts the "ON PartitionScheme(FieldName)" but whenever we have
primary key then it says it is created on [primary] istead of the
partition scheme provided.
by the way, the field which the table is going to be partitioned
based
on it is a foreign key.
Thanks a lot,
Ali> we have a large database containing many tables and lots of data. now
> because of the number of records we decided to use partitions.
> for this we have created partition function and partition scheme.
Just because you have a lot of rows doesn't mean you need to partition.
Partitioning can help manageability of tables that are difficult to manage
due to size and can help performance of specialized operations. If your
tables are large and performance is slow, index and query tuning is the best
initial approach. Adding partitioning to the mix without tuning can slow
queries further.
> the clustered index is the primary key and there are some
> foreign keys which are dependent on this one so it can't be dropped.
> and the base field which the table is going to be partitioned on that
> is not primary key.
The definition of a partitioned table is either a partitioned heap or a
table with a partitioned clustered index. If you want to partition this
table on the FK column, you'll need to either add the partitioning column to
the primary key or create a partitioned clustered index that includes the
partitioning column and change the primary key to a non-partitioned
non-clustered index. Note that when you partition non-clustered indexes
differently than the base table, you lose some of the manageability
benefits.
> the other question is: when creating a table if we do not have primary
> key , it
> accepts the "ON PartitionScheme(FieldName)" but whenever we have
> primary key then it says it is created on [primary] istead of the
> partition scheme provided.
The clustered primary key specification takes precedence over the table
create ON clause. If the primary key is non-clustered, then you'll end up
with a partitioned table and a non-partitioned index.
In addition to the Books Online, see Kimberly Tripp's white paper for a
thorough partitioning discussion:
http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ali" <nikzad.a@.gmail.com> wrote in message
news:4bace43a-e047-4305-bf7a-de96542feb5b@.v4g2000hsf.googlegroups.com...
> Hi,
> we have a large database containing many tables and lots of data. now
> because of the number of records we decided to use partitions.
> for this we have created partition function and partition scheme. the
> problem is : How can we convert non-partitioned table into
> partitioned one according to a partition scheme?
> in the documentation it introduced 2 methods but I don't know are the
> usable or not? if they are, how can I use them?
> the clustered index is the primary key and there are some
> foreign keys which are dependent on this one so it can't be droped.
> and the base field which the table is going to be partitioned on that
> is not primary key.
> the other question is: when creating a table if we do not have primary
> key , it
> accepts the "ON PartitionScheme(FieldName)" but whenever we have
> primary key then it says it is created on [primary] istead of the
> partition scheme provided.
>
> by the way, the field which the table is going to be partitioned
> based
> on it is a foreign key.
>
>
> Thanks a lot,
> Ali
>sqlsql