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
) 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.
Reference : http://SqlAndMe.com
Filed under: Database, Partitioning, SQL, Sql And Me, SQL FAQ, SQLServer, TSQL
