Jignesh Raiyani
Table Partitioning in SQL Server

How to automate Table Partitioning in SQL Server

July 7, 2020 by

In this article, we will demonstrate specific ways to automate table partitioning in SQL Server. This article aims to help you avoid manual table activities of partition maintenance by automating it with T-SQL scripts and SQL Server jobs.

Automation with the partition task is required when the range of partition function is not enough to proceed for the newly inserted data. For example, when a partition function does not have a range for new rows and inserting rows into the table is out of the existing range. However, those rows will be inserted into the table but do not move to the appropriate filegroup and its files. Most probably, a partition is required on tables that are having a large number of rows. For those tables, the partition function separates the rows by month, year, or any other valuable column.

Initially, a database team will plan the predicted maximum range by adding a filegroup to the partition scheme and adding a range to the partition function. A new range with partition function and filegroup with the partition scheme is required when the existing range is near about to the ending. Adding a new range and filegroup to the current partitioning is the manual activity, and that can be performed by the database administrator while monitoring the range. A database administrator will split the range when partition’s range is near about the end. Here, this article suggests some ordinary solutions to automate with the help of the SQL Server scheduler.

SQL Server scheduler can help with splitting a new partition range and adding new filegroups as it is required for the partition functions and schemes. Users can write a short program to automate the partition with the help of T-SQL, and that program can be executed using a SQL Server job. Before designing the maintenance for partitioning in SQL Server, users should be aware of what is table partitioning in SQL Server? And why is partitioning required in SQL Server?

Table Partitioning in SQL Server

What is Table Partitioning in SQL Server? Why is partitioning required in SQL Server?

Table Partition is the logical division of information with the physical distribution in the filegroups. A table can be partitioned by applying the partition schema over the table schema. The role of the partition function is to divide the information in a logical division by partition range and partition scheme indexing the partition range to the filegroup. After applying the partition on the table, rows of the table will be distributed in different secondary files by the filegroup.

Day by day density of the information is increasing more and more, and the database size increases with storing more and more pieces of information. Ideally, companies maintain several databases for the product to generate a query result set from multiple tables to fulfill the business requirements. With the increase in the size of databases, table performance becomes slower for the client queries. But, proper indexes and index maintenance will exist in the database, but at last, records will be scanned out from the broad set of the index pointer value. What can the user do more? And How?

Partition is one of the beneficial approaches for query performance over the large table. Table Index will be a part of each partition in SQL Server to return a quick query response. Actual partition performance will be achieved when you use a query with the partition column because the partition column will target those partitions only, which are required by the partition column. It wouldn’t scan the information in all filegroups.

Data distribution

Partitioning in SQL Server divides the information into the smaller storage groups; It is about table data and indexes. Partition function can be used with the table column when a table creates. A partition can be defined with the name and its storage attributes. Let’s have a sample partition before setting up an automated task on table partitioning in SQL Server.

Table Partitioning in SQL Server – Step by Step

Partitioning in SQL Server task is divided into four steps:

  • Create a File Group
  • Add Files to File Group
  • Create a Partition Function with Ranges
  • Create a Partition Schema with File Groups

Creating a sample database to perform Table Partitioning in SQL Server:

Adding File Groups to the database:

Here, we are going to set up a month-wise partition for the table. So, initially, we are creating 12 File Groups for the year 2020.

Adding Files to each File Group

Here, adding one file to each filegroup for data distribution to the physical storage to perform the table partitioning in SQL Server. If you have crores of row per day, then you can add multiple files to the single file group as well, and data will be distributed to the number of files in the filegroup.

Adding a Partition Function with Month wise range

Here, 12 ranges are defined with the last day of the month and last ms of the day. Users can use the month and year combination as well to perform the table partitioning in SQL Server. But I would recommend to define it with full datetime to perform insert operations quickly. In the above sample code, the partition function is defined with the Right direction.

Adding a Partition Scheme with File Groups to the Partition Function

Adding a filegroup to the partition schema with attaching the partition function

Here, the Primary filegroup is an additional filegroup in the partition scheme definition. The primary filegroup is used to store those rows which are exceeding the partition range in the function. It works when users forget to add new ranges and new filegroups with the file.

Creating a Table with the Partition Scheme

Here, the table is defined with applying the Partition to the column [order_date] of table orders.

Inserting data into Table [orders]

We have inserted sample rows with each partition range of the partition function. Now let’s check how many rows exist with partition and its filegroups.

Partition details with Row count

Below are the DMVs that return the number of rows that exist in the filegroup with partition range.

Partition Details for Table Partitioning in SQL Server

Here, the partition range was not defined for January 2021. But we added the last file group with the Primary. Therefore, if rows are exceeding the range, then those will be allocated to the primary filegroup.

Table Rows with Partition Number

Users can find the partition number with each row of the table as well. Users can bifurcate the row allocation to the logical partition number with the help of a $PARTITION() function.

Logical Partition reference for Table Partitioning in SQL Server

Automate the Partition flow

We have explained the above examples to understand the requirement of post activities and maintenance on the partition function. A primary filegroup is defined to manage those rows which are out of the partition range. However, the database team has to monitor that the range of any partition function is ending or not? To avoid some manual tasks, users can set up the SQL Server job to perform it automatically.

SQL Server job will be executed in a pre-defined scheduled time (monthly or weekly) and helps to find out the partition functions which are needed to be maintained. In the above sample, we used the DATETIME column type for the partition range. Users can write a program with T-SQL statements as below:

Find the Partition Function whose range does not exist for the next month. For example, in the above example, partition range is defined till Dec 2020, and a current timestamp is 2020-12-27 16:27:09.500. After three days, the partition range will be exceeded because the maximum range is 2020-12-31 23:59:59.997 for the order table. Now, we will find the partition functions which are required to be maintained using the below T-SQL.

Get maximum partition range of Table Partitioning in SQL Server

Here, the above result set returned the partition function (PF_MonthlyPartition) for adding the new range.

The following code helps to insert information to the new temp table for those partition functions that are required to SPLIT.

We can also generate a dynamic script to create File Group, File, add a new file group to partition scheme, and new range to the partition function as below:

Here, the script is generated with individual variables, and the dynamic code is executed in the sequence. We have utilized most of the static values, but users can prepare them completely dynamic too. The above snippets should be taken in a single procedure, and that procedure can be configured with the SQL Server job.


Adding a new range to the partition function should be an automated task always. The partition range is always depending on the row size in the partition function. Table partitioning in SQL Server is always helpful in terms of maintaining large tables.

Jignesh Raiyani