Partition Table Monthly Bases using Computed Column in SQL Server Database

Database developers can partition a SQL Server database table according to months using computed column in partition scheme and partition function as shown in this SQL tutorial like storing January records in one partition, February records into an other partition based on OrderDate column for example.

If you have worked with table partitioning on SQL Server tables, you will know that the partition function does not accept parameterized values or a SQL function like DATEPART(), SUBSTRING() etc.

In this SQL Server partition table tutorial, we need to map month part of OrderDate column to boundary values used in the partition function. Since boundary values defining the partitioning function can not reference expressions like SQL functions, etc and must be constant expressions, we will use computed column in database table and apply partition schema on this computed column. Since we can use user-defined functions in definition of computed colums, it is possible to build the bridge between main partitioning column (OrderDate in this example) to fix-boundary values in partition function.

One important issue related with using computed column to partition a database table is that, the computed column definition should be done with PERSISTED.
Persisted computed column values are physically stored in the database table. If any of the dependend columns of computed column are affected by an update the physical computed column value is also updated.
MSDN documentation for BOL (Books OnLine) defines especially that if a computed columns is being used as partitioning column for a partitioned table, the computed column should be created with PERSISTED option.

Let's make an example on partitioning an existing database table according to month names like January, February, ... and December using a date column.

I'm working on SQL Server 2014 for this SQL tutorial and I have AdventureWorks2014 sample database installed on my local SQL Server instance.

Below SQL code script creates a new database for test purposes named "SQLPartitionTableDB" and creates a new table named "PartitionTableByMonth" with data populated from SalesOrderHeader table in AdventureWorks2014 database.

Now as SQL Server database administrators or T-SQL programmers, our aim is to partition records according to months which requires 12 partitions for each month.

Database administrators can create one data file for each month and add these data files into a new data file group seperately.
To summarize for in this partitioning tutorial for the sake of our target, we have to create 12 data file groups and one new data file in these data file groups in our database.

Please note that according to where you keep your SQL Server database files, following script should be updated for the filename parameters. I kept the default file folder for data files of SQL Server databases.

SQL Server programmers can configure data file size and file growth options as seen in above script.

Let's check database properties in SQL Server Management Studio.
We can see the data files and data filegroups as follows in the SSMS database properties screen

Database data files for partitioning table monthly bases

Database data filegroups

Now SQL programmers can create the database table partition function.
Please note that 11 boundary values in the partition function creates 12 partitions in the database table.

It is also possible to create partition function with LEFT boundary option as follows if you did not decide to use the RIGHT boundary option.

After partition function is created partition scheme can be created too referencing to the partition function.

For SQL developers to pay attention, in create partition scheme command we passed all of the 12 data file names as input parameter.

SQL Server data platform professionals can display the partition functions and partition schemes created on a database under Storage node in SQL Server Management Studio Object Explorer window as seen in below screenshot.

At this point in this SQL partition table tutorial, we came to a junction.
First SQL developers can see how to partition an existing database table.
As a second step, we code to create a new partition table in our sample database.

First, partition an existing SQL table in the database which is our sample table PartitionTableByMonth.

As beginning add the computed column with SQL Month() function over OrderDate field.
Please do not forget to mark the computed column as PERSISTED.
Otherwise, we cannot use the computed column as partitioning field in our table.

When SQL programmers create a clustered index over partition column using partition scheme option, the table data will be distributed automatically according to the rules defined in partition function and partition scheme.

If cluster index creation is completed without any error, you can check data stored in each table partition using following SQL Select statements.

If you want you can execute following SQL Select statements to compare row counts per month with row counts in table partitions.

You will see that data row counts are equal for each month

Let's now create a new database table and define the partition column and partition scheme at the Create Table script.

Using AdventureWorks2014 database I created the CREATE script for database table SalesOrderHeader.

In order to simplify the SQL tutorial, I made a few simple changes on the Create Table script for user data types, etc.

The most important part with partitioning a new database table in Create Table script is the Computed column with Persisted option and the partition scheme over the partition column.
I marked these two last lines of code with bold in following SQL command.

Please note that we are creating this SalesOrderHeader sample table in database SQLPartitionTableDB, not in AdventureWorks sample database.

Now afterwards, every data record we insert into this database table will automatically be stored in the corresponding table partition file.

Let's insert all table data from AdventureWorks2014 database Sales.SalesOrderHeader table into our new partitioned database table SalesOrderHeader.

Let's double check the partition table rows per partition and data row counts grouped by month on our new database table