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
) WITH (DROP_EXISTING = ON)
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.