Steps to Create a Partition Table in SQL Server

Leave a reply

Creating a partitioned table in SQL Server involves several steps, from setting up the partition function to creating the partition scheme and applying it to a table. Below are the steps with commands and examples:

Steps involved in creating a Partition Table:

  1. Create filegroups for storage.
  2. Create a partition function for date-based partitioning.
  3. Create a partition scheme to map partitions to filegroups.
  4. Create a partitioned table on the scheme using a column like OrderDate.
  5. Insert data, and SQL Server will automatically assign rows to the correct partition.
  6. Add a new partition yearly using ALTER PARTITION FUNCTION.
  7. Monitor partitions with queries.

Step 1: Create Filegroups

Before creating partitions, you need to create filegroups where each partition will store its data. This step is optional but useful for large databases that benefit from storage distribution.

ALTER DATABASE [YourDatabase] ADD FILEGROUP [FileGroup1];
ALTER DATABASE [YourDatabase] ADD FILEGROUP [FileGroup2];
ALTER DATABASE [YourDatabase] ADD FILEGROUP [FileGroup3];
ALTER DATABASE [YourDatabase] ADD FILEGROUP [FileGroup4];

Next, add files to these filegroups:

ALTER DATABASE [YourDatabase]
ADD FILE (NAME = 'File1', FILENAME = 'C:\SQLData\File1.ndf') TO FILEGROUP [FileGroup1];

ALTER DATABASE [YourDatabase]
ADD FILE (NAME = 'File2', FILENAME = 'C:\SQLData\File2.ndf') TO FILEGROUP [FileGroup2];

ALTER DATABASE [YourDatabase]
ADD FILE (NAME = 'File3', FILENAME = 'C:\SQLData\File3.ndf') TO FILEGROUP [FileGroup3];

ALTER DATABASE [YourDatabase]
ADD FILE (NAME = 'File4', FILENAME = 'C:\SQLData\File4.ndf') TO FILEGROUP [FileGroup4];

Step 2: Create a Partition Function

The partition function defines how data will be distributed across partitions based on a specific column. In this example, we’ll partition by year using a date column.

CREATE PARTITION FUNCTION [DateRangePartitionFunction] (DATE)
AS RANGE RIGHT FOR VALUES ('2020-12-31', '2021-12-31', '2022-12-31', '2023-12-31');

Note:
RANGE RIGHT: means that the boundary value is included in the partition to the right.
Values: These are the boundary points. In this example, we create yearly partitions for dates.

Step 3: Create a Partition Scheme

A partition scheme maps the partitions from the partition function to specific filegroups. This maps the partitions to different filegroups, distributing data storage across physical locations.

CREATE PARTITION SCHEME [DateRangePartitionScheme]
AS PARTITION [DateRangePartitionFunction]
TO ([FileGroup1], [FileGroup2], [FileGroup3], [FileGroup4], [PRIMARY]);

Step 4: Create a Partitioned Table

Now, create the partitioned table by specifying the partition scheme and the column that determines the partition. The table will now be partitioned based on the OrderDate column.The ON [DateRangePartitionScheme]([OrderDate]) clause ensures that rows are distributed into partitions based on the OrderDate.

CREATE TABLE [SalesOrders] (
    [OrderID] INT PRIMARY KEY,
    [OrderDate] DATE,
    [OrderAmount] DECIMAL(10,2)
) ON [DateRangePartitionScheme]([OrderDate]);

Step 5: Insert Data into the Partitioned Table

You can now insert data into your newly partitioned table. SQL Server will automatically place the rows in the correct partition based on the OrderDate.

INSERT INTO [SalesOrders] ([OrderID], [OrderDate], [OrderAmount])
VALUES 
(1, '2020-06-15', 100.00),
(2, '2021-07-20', 150.50),
(3, '2022-08-05', 200.75);

Step 6: Adding New Partitions Dynamically

At the end of each year, you can add a new partition for the upcoming year using the ALTER PARTITION FUNCTION command.

ALTER PARTITION FUNCTION [DateRangePartitionFunction]
SPLIT RANGE ('2024-12-31');

Step 7: Querying Data in the Partitioned Table

SQL Server automatically handles which partition to query based on the OrderDate. However, you can use the sys.partitions view to see which partition stores specific data.

SELECT p.partition_id, p.partition_number, p.rows
FROM sys.partitions AS p
WHERE object_id = OBJECT_ID('SalesOrders') AND index_id IN (0,1);

Step 8: Checking Partition Usage

To check the filegroups and partition distribution:

SELECT 
    t.name AS TableName,
    p.partition_id,
    p.partition_number,
    f.name AS FileGroupName
FROM sys.partitions p
JOIN sys.filegroups f ON p.data_compression = f.data_space_id
JOIN sys.tables t ON p.object_id = t.object_id
WHERE t.name = 'SalesOrders';

Step 9: Dropping Partitions

To merge or remove a partition:

ALTER PARTITION FUNCTION [DateRangePartitionFunction]
MERGE RANGE ('2023-12-31');

Leave a Reply