Home > Partitioning, SQLServer > SQL Server – How to partition an existing table…

SQL Server – How to partition an existing table…

It’s fairly simple, create a CLUSTERED INDEX on the table. While creating a clustered index you need to specify the partition scheme to be used in ON clause of CREATE INDEX statement.


We still need to create a partition function and a partition scheme, I have posted earlier about how to create the same here. Step 1 – Creating a partition function and Step 2 – Creating a partition scheme are same, only Step 3 changes as we are partitioning an existing table.


For example, I have a table dbo.Table_Orders which is not partitioned:

SELECT      OrderID, OrderDate, Quantity, Amount

FROM        dbo.Table_Orders


SELECT      partition_id, object_id, partition_number, rows

FROM        sys.partitions

WHERE       object_id = OBJECT_ID('Table_Orders')

Result Set:

OrderID     OrderDate  Quantity    Amount

———– ———- ———– ———————

1           2011-07-22 34          3200.00

2           2010-06-22 98          9800.00

3           2009-05-22 65          6500.00

4           2011-07-24 73          7300.00


(4 row(s) affected)


partition_id         object_id   partition_number rows

——————– ———– —————- ——————–

72057594039631872    5575058     1                4


(1 row(s) affected)


Now, if we want to partition this table, we need to create a clustered index ON a partition scheme. I have a partition scheme in my database ordersPartScheme. So the clustered index should be created using:

CREATE CLUSTERED INDEX [Clust_Orders] ON [dbo].[Table_Orders]


      [OrderID] ASC

) ON ordersPartScheme(OrderDate)


— Check for new partitions

SELECT      partition_id, object_id, partition_number, rows

FROM        sys.partitions

WHERE       object_id = OBJECT_ID('Table_Orders')

Result Set:

partition_id         object_id   partition_number rows

——————– ———– —————- ——————–

72057594039762944    5575058     1                1

72057594039828480    5575058     2                3


(2 row(s) affected)

From the result set, we can see that new partition is added to the table, and required rows are also moved to new partition.


However, if you already have a clustered index created on a table, then you can use DROP_EXISTING clause to re-create the clustered index:

CREATE CLUSTERED INDEX [Clust_Orders] ON [dbo].[Table_Orders]


      [OrderID] ASC


ON ordersPartScheme(OrderDate)

Using DROP_EXISTING = ON specifies that the index is dropped and rebuilt.

For more information, check Horizontally partitioning a SQL Server Database Table.

Hope This Helps!


  1. suresh
    21.06.2012 07:11 | #1

    in table partitioning,can we drop clustered index . non clustered index is it possible.non clustered is possible give me one example.

  2. Vishal
    25.06.2012 06:43 | #2

    Hi Suresh,

    Apologies for delayed response.

    You can surely drop a clustered index on a partitioned table. You partition a table based on a column, it does’t need to have a clustered index.

  3. suresh
    04.07.2012 11:22 | #3

    Hi Vishal,
    i tried partitionit is ok.but clustered index is changed to non clustered.why give me one example.please mail me my mail.

  4. suresh
    14.09.2012 08:50 | #4

    Hi Vishal,
    i faced two issues in partitioning
    1)when i am doing partitioning in primary key,clustered index is not changing.but always i am not dependnig primay key.so
    2)some times i depend othr columes EX: date column, when i am doing partition on date column ,partitioning is ok but clustered index is changed automatically nonclustered index. why it will happen? this time no clistered index is there in my table so duplicates will hapen.
    Please give a suggestion ASAP.

  5. Ionut
    25.09.2012 04:49 | #5

    Hey Vishal,
    I was just wondering, is there any other way to partition an existing table without creating a clustered index on it ? Maybe I have a table that I don’t want to be indexed.
    Thank you, and really great articles man !

  1. No trackbacks yet.