Partitioning an existing table

I am running SQL Server 2005 and am interested in partitioning a multi-
million row table, that contains a clustered index (which is comprised of two
columns), but the partitioning key is not part of that clustered index.
I have read about partitioning using ALTER TABLE on BOL and have searched the
web for examples of partitioning existing tables, but have had no success.
The only true examples I have come across use a CREATE TABLE statement. I
assume the ALTER TABLE would contain such a mechanism, but apparently I do
not understand. Is this possible using the ALTER TABLE statement where the
partitioning key is not part of the clustered index?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200706/1Hi cbrichards
The way to partition an existing table is to rebuild the clustered index on
a partition scheme. If the index is unique, partition keys must be a subset
of the index keys. So since you are rebuilding the index anyway, you can
redefine it to include the partitioning keys, or to make it nonunique. The
index rebuild would look something like this:
CREATE UNIQUE CLUSTERED INDEX your_index_name ON your_table
(original_index_key1, origininal_index_key2, partitioning_column)
WITH DROP_EXISTING ON your_partitioning_scheme (partitioning_column)
GO
-- OR --
CREATE CLUSTERED INDEX your_index_name ON your_table (original_index_key1,
origininal_index_key2)
WITH DROP_EXISTING ON your_partitioning_scheme (partitioning_column)
GO
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:73b0eb04f61ce@.uwe...
>I am running SQL Server 2005 and am interested in partitioning a multi-
> million row table, that contains a clustered index (which is comprised of
> two
> columns), but the partitioning key is not part of that clustered index.
> I have read about partitioning using ALTER TABLE on BOL and have searched
> the
> web for examples of partitioning existing tables, but have had no success.
> The only true examples I have come across use a CREATE TABLE statement. I
> assume the ALTER TABLE would contain such a mechanism, but apparently I do
> not understand. Is this possible using the ALTER TABLE statement where the
> partitioning key is not part of the clustered index?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200706/1
>