Table Partitioning In SQL Server With Example

As a senior SQL developer working with a reputed MNC, my daily activities involved dealing with large tables, and that’s where table partitioning helped me a lot. In this article, I will walk you through all the complete steps to implementing table partitioning in SQL servers with real-time examples.

1. Creating the Partition Function

The initial step is to create a partition function. Let us consider a real-time example: my company’s Product Sales data per year. To create the partition function, we can execute the query below.

CREATE PARTITION FUNCTION pf_MyProductSalePerYear (DATETIME)
AS RANGE RIGHT FOR VALUES 
('2022-01-01', '2023-01-01', '2024-01-01');

The command executed successfully, and the partition function was created successfully, as shown in the screenshot below.

Check out How To Find Temp Table In SQL Server

2. Creating a Partition Scheme

Next, we must create a Partition Scheme that maps the partitions to the filegroups. For this purpose, we need to execute the query below.

CREATE PARTITION SCHEME ps_MyProductSalePerYear
AS PARTITION pf_MyProductSalePerYear
ALL TO ([PRIMARY]);

After executing the above query, the Partition Scheme was created successfully, as shown in the screenshot below.

3. Creating the Partitioned Table

Now, let us create the partitioned table named MyProduct using the partition scheme we created above using the below query.

CREATE TABLE MyProduct (
    SalesID INT IDENTITY(1,1),
    ProductSaleDate DATETIME,
    CustomerID INT,
    ProductID INT,
    TotalQuantity INT,
    TotalAmount DECIMAL(10,2)
    CONSTRAINT PK_Sales PRIMARY KEY NONCLUSTERED (SalesID, ProductSaleDate)
) ON ps_MyProductSalePerYear(ProductSaleDate);

The command was executed successfully after executing the above query, as shown in the screenshot below.

Check out Partition Columns For A Unique Index Must Be A Subset Of The Index Key. If you get this error after executing the above script.

4. Inserting data into the Partitioned table

We can execute the query below to insert data into the partitioned table we created above.

INSERT INTO MyProduct (ProductSaleDate, CustomerID, ProductID, TotalQuantity, TotalAmount)
VALUES 
('2022-04-15', 1, 501, 3, 101.9),
('2023-05-22', 2, 301, 2, 32.45),
('2024-06-30', 3, 701, 1, 103.78);

After executing the above query, the mentioned data was inserted successfully, as shown in the screenshot below.

Check out How To Save Query Results As Table In SQL Server

5. Querying the data from a specific partition

Now, let us use the below query to query to a specific partition.

SELECT * FROM MyProduct
WHERE ProductSaleDate >= '2022-01-01' AND ProductSaleDate < '2024-01-01';

After executing the above query, I got the expected output below.

Conclusion

Table partitioning is a great way to manage large tables. The steps above can be used to do it in real-time.

You may also like following the articles below.

• How To Create A Table Valued Function In SQL Server
• How To Get Table Count In SQL Server
• How to Find Table Dependencies in SQL Server