SQL Server Partition Existing Table

Summary: in this tutorial, you’ll learn how to partition an existing table in SQL Server using T-SQL.

Partitioning an existing table using T-SQL #

The steps for partitioning an existing table are as follows:

Create filegroups
Create a partition function
Create a partition scheme
Create a clustered index on the table based on the partition scheme.

We’ll partition the sales.orders table in the BikeStores database by years.

Create filegroups #

First, create two new file groups will store the rows with the order dates in 2016 and 2017:

ALTER DATABASE bikestores
ADD FILEGROUP salesorders_2016;

ALTER DATABASE bikestores
ADD FILEGROUP salesorders_2017;Code language: SQL (Structured Query Language) (sql)

Second, map the filegroups with the physical files. Note that you need to have the D:\data folder in the server before executing the following statements:

ALTER DATABASE bikestores    
ADD FILE     (
    NAME = salesorders_2016,
    FILENAME = 'D:\data\salesorders_2016.ndf',
        SIZE = 10 MB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP salesorders_2016;

ALTER DATABASE bikestores    
ADD FILE     (
    NAME = salesorders_2017,
    FILENAME = 'D:\data\salesorders_2017.ndf',
        SIZE = 10 MB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP salesorders_2017;Code language: SQL (Structured Query Language) (sql)

Create a partition function #

Create a partition function that accepts a date and returns three partitions:

CREATE PARTITION FUNCTION sales_order_by_year_function(date)
AS RANGE LEFT 
FOR VALUES ('2016-12-31', '2017-12-31');Code language: SQL (Structured Query Language) (sql)

Create a partition scheme #

Create a partition scheme based on the sales_order_by_year_function partition function:

CREATE PARTITION SCHEME sales_order_by_year_scheme
AS PARTITION sales_order_by_year_function
TO ([salesorders_2016], [salesorders_2017], [primary]);Code language: SQL (Structured Query Language) (sql)

Create a clustered index on the partitioning column #

The orders table has the order_id as the primary key. This primary key column is also included in a clustered index.

To partition the orders table by the order_date column, you need to create a clustered index for the order_date column on the partition scheme sales_order_by_year_scheme.

To do that, you need to change the clustered index that includes the order_id column to a non-clustered index so that you can create a new clustered index that includes the order_date column.

But the order_id is referenced by a foreign key in the order_items table. Therefore, you need to perform these steps:

First, remove the foreign key order_id from the order_items table:

ALTER TABLE [sales].[order_items] 
DROP CONSTRAINT [FK__order_ite__order__3A81B327]Code language: SQL (Structured Query Language) (sql)

Note that the constraint name FK__order_ite__order__3A81B327 may be different in your database.

Second, remove the primary key constraint from the orders table:

ALTER TABLE [sales].[orders] 
DROP CONSTRAINT [PK__orders__46596229EDE70106];Code language: SQL (Structured Query Language) (sql)

Third, add the order_id as a non-clustered primary key on the PRIMARY partition:

ALTER TABLE [sales].[orders] 
ADD PRIMARY KEY NONCLUSTERED([order_id] ASC) 
ON [PRIMARY];Code language: SQL (Structured Query Language) (sql)

Fourth, create a clustered index that includes the order_date column:

CREATE CLUSTERED INDEX ix_order_date 
ON [sales].[orders]
(
	[order_date]
) ON [sales_order_by_year_scheme]([order_date])Code language: SQL (Structured Query Language) (sql)

Fifth, drop the clustered index:

DROP INDEX ix_order_date 
ON [sales].[orders];Code language: SQL (Structured Query Language) (sql)

Finally, add the foreign key constraint back to the order_items table:

ALTER TABLE [sales].[order_items]  
WITH CHECK ADD FOREIGN KEY([order_id])
REFERENCES [sales].[orders] ([order_id])
ON UPDATE CASCADE
ON DELETE CASCADE;Code language: SQL (Structured Query Language) (sql)

It’s better to run all the statements above in a transaction like this:

BEGIN TRANSACTION;

ALTER TABLE [sales].[order_items] 
DROP CONSTRAINT [FK__order_ite__order__3A81B327];

ALTER TABLE [sales].[orders] 
DROP CONSTRAINT [PK__orders__46596229EDE70106];


ALTER TABLE [sales].[orders] ADD PRIMARY KEY NONCLUSTERED 
(
	[order_id] ASC
) ON [PRIMARY];

CREATE CLUSTERED INDEX ix_order_date 
 ON [sales].[orders]
(
	[order_date]
) ON [sales_order_by_year_scheme]([order_date]);

DROP INDEX ix_order_date 
ON [sales].[orders];

ALTER TABLE [sales].[order_items]  
WITH CHECK ADD FOREIGN KEY([order_id])
REFERENCES [sales].[orders] ([order_id])
ON UPDATE CASCADE
ON DELETE CASCADE;

COMMIT TRANSACTION;Code language: SQL (Structured Query Language) (sql)

To check the number of rows in each partition, you use the following query:

SELECT 
	p.partition_number AS partition_number,
	f.name AS file_group, 
	p.rows AS row_count
FROM sys.partitions p
JOIN sys.destination_data_spaces dds ON p.partition_number = dds.destination_id
JOIN sys.filegroups f ON dds.data_space_id = f.data_space_id
WHERE OBJECT_NAME(OBJECT_ID) = 'orders'
order by partition_number;Code language: SQL (Structured Query Language) (sql)

Summary #

Create a clustered index on a partitioning column based on a partition scheme to partition an existing table.

Was this tutorial helpful?