SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



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.

use master
go
Create Database SQLPartitionTableDB
go
use SQLPartitionTableDB
go
select * into dbo.PartitionTableByMonth from AdventureWorks2014.Sales.SalesOrderHeader

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.

ALTER DATABASE SQLPartitionTableDB ADD FILEGROUP January
ALTER DATABASE SQLPartitionTableDB ADD FILE (
 NAME = N'January',
 FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DataFileJanuary.ndf'
) TO FILEGROUP January

ALTER DATABASE SQLPartitionTableDB ADD FILEGROUP February
ALTER DATABASE SQLPartitionTableDB ADD FILE (
 NAME = N'February',
 FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DataFileFebruary.ndf',
 SIZE = 3072KB , FILEGROWTH = 1024KB
) TO FILEGROUP February

ALTER DATABASE SQLPartitionTableDB ADD FILEGROUP March
ALTER DATABASE SQLPartitionTableDB ADD FILE (
 NAME = N'March',
 FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DataFileMarch.ndf'
) TO FILEGROUP March

ALTER DATABASE SQLPartitionTableDB ADD FILEGROUP April
ALTER DATABASE SQLPartitionTableDB ADD FILE (
NAME = N'April',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DataFileApril.ndf'
) TO FILEGROUP April

ALTER DATABASE SQLPartitionTableDB ADD FILEGROUP May
ALTER DATABASE SQLPartitionTableDB ADD FILE (
NAME = N'May',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DataFileMay.ndf',
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP May

ALTER DATABASE SQLPartitionTableDB ADD FILEGROUP June
ALTER DATABASE SQLPartitionTableDB ADD FILE (
NAME = N'June',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DataFileJune.ndf',
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP June

ALTER DATABASE SQLPartitionTableDB ADD FILEGROUP July
ALTER DATABASE SQLPartitionTableDB ADD FILE (
NAME = N'July',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DataFileJuly.ndf',
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP July

ALTER DATABASE SQLPartitionTableDB ADD FILEGROUP August
ALTER DATABASE SQLPartitionTableDB ADD FILE (
NAME = N'August',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DataFileAugust.ndf',
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP August

ALTER DATABASE SQLPartitionTableDB ADD FILEGROUP September
ALTER DATABASE SQLPartitionTableDB ADD FILE (
NAME = N'September',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DataFileSeptember.ndf',
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP September

ALTER DATABASE SQLPartitionTableDB ADD FILEGROUP October
ALTER DATABASE SQLPartitionTableDB ADD FILE (
NAME = N'October',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DataFileOctober.ndf',
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP October

ALTER DATABASE SQLPartitionTableDB ADD FILEGROUP November
ALTER DATABASE SQLPartitionTableDB ADD FILE (
NAME = N'November',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DataFileNovember.ndf',
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP November

ALTER DATABASE SQLPartitionTableDB ADD FILEGROUP December
ALTER DATABASE SQLPartitionTableDB ADD FILE (
NAME = N'December',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DataFileDecember.ndf',
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP December

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 on SQL Server Management Studio
Database data files for partitioning table monthly bases

SQL Server database data filegroups
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.

CREATE PARTITION FUNCTION partition_function_ByMonth (int) AS RANGE RIGHT FOR VALUES (2,3,4,5,6,7,8,9,10,11,12);

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.

CREATE PARTITION FUNCTION partition_function_ByMonth (int) AS RANGE LEFT FOR VALUES (1,2,3,4,5,6,7,8,9,10,11);

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

CREATE PARTITION SCHEME partition_scheme_ByMonth
AS PARTITION partition_function_ByMonth
TO (January, February, March, April, May, June, July, August, September, October, November, December);

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.

Partition Schemes and Partition Functions

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.

-- existing table
ALTER TABLE PartitionTableByMonth ADD PartitionColumn as MONTH(OrderDate) PERSISTED

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.

CREATE CLUSTERED INDEX PK_PartitionTableByMonth
 ON dbo.PartitionTableByMonth(PartitionColumn)
 ON partition_scheme_ByMonth (PartitionColumn)

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

SELECT * FROM dbo.PartitionTableByMonth WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 1
SELECT * FROM dbo.PartitionTableByMonth WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 2
SELECT * FROM dbo.PartitionTableByMonth WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 3
SELECT * FROM dbo.PartitionTableByMonth WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 4
SELECT * FROM dbo.PartitionTableByMonth WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 5
SELECT * FROM dbo.PartitionTableByMonth WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 6
SELECT * FROM dbo.PartitionTableByMonth WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 7
SELECT * FROM dbo.PartitionTableByMonth WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 8
SELECT * FROM dbo.PartitionTableByMonth WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 9
SELECT * FROM dbo.PartitionTableByMonth WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 10
SELECT * FROM dbo.PartitionTableByMonth WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 11
SELECT * FROM dbo.PartitionTableByMonth WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 12

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

SELECT 1 [month], COUNT(*) [count] FROM dbo.PartitionTableByMonth WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 1
union all
SELECT 2 [month], COUNT(*) [count] FROM dbo.PartitionTableByMonth WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 2
union all
SELECT 3 [month], COUNT(*) [count] FROM dbo.PartitionTableByMonth WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 3
union all
SELECT 4 [month], COUNT(*) [count] FROM dbo.PartitionTableByMonth WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 4
union all
SELECT 5 [month], COUNT(*) [count] FROM dbo.PartitionTableByMonth WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 5
union all
SELECT 6 [month], COUNT(*) [count] FROM dbo.PartitionTableByMonth WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 6
union all
SELECT 7 [month], COUNT(*) [count] FROM dbo.PartitionTableByMonth WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 7
union all
SELECT 8 [month], COUNT(*) [count] FROM dbo.PartitionTableByMonth WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 8
union all
SELECT 9 [month], COUNT(*) [count] FROM dbo.PartitionTableByMonth WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 9
union all
SELECT 10 [month], COUNT(*) [count] FROM dbo.PartitionTableByMonth WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 10
union all
SELECT 11 [month], COUNT(*) [count] FROM dbo.PartitionTableByMonth WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 11
union all
SELECT 12 [month], COUNT(*) [count] FROM dbo.PartitionTableByMonth WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 12

select DATEPART(MM,OrderDate) [month], Count(*) [count]
from PartitionTableByMonth
group by DATEPART(MM,OrderDate)
order by DATEPART(MM,OrderDate)

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

data row counts of partitioned table in SQL Server database

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.

create table script using SQL Server Management Studio

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.

CREATE TABLE [SalesOrderHeader](
 [SalesOrderID] [int] IDENTITY(1,1),
 [RevisionNumber] [tinyint] DEFAULT ((0)),
 [OrderDate] [datetime] DEFAULT (getdate()),
 [DueDate] [datetime] NOT NULL,
 [ShipDate] [datetime] NULL,
 [Status] [tinyint] DEFAULT ((1)),
 [OnlineOrderFlag] [tinyint] DEFAULT ((1)),
 [SalesOrderNumber] nvarchar(40),
 [PurchaseOrderNumber] varchar(20) NULL,
 [AccountNumber] varchar(20) NULL,
 [CustomerID] [int] NOT NULL,
 [SalesPersonID] [int] NULL,
 [TerritoryID] [int] NULL,
 [BillToAddressID] [int] NOT NULL,
 [ShipToAddressID] [int] NOT NULL,
 [ShipMethodID] [int] NOT NULL,
 [CreditCardID] [int] NULL,
 [CreditCardApprovalCode] [varchar](15) NULL,
 [CurrencyRateID] [int] NULL,
 [SubTotal] [money],
 [TaxAmt] [money],
 [Freight] [money],
 [TotalDue] AS (isnull(([SubTotal]+[TaxAmt])+[Freight],(0))),
 [Comment] [nvarchar](128) NULL,
 [rowguid] [uniqueidentifier],
 [ModifiedDate] [datetime] DEFAULT (getdate()),
 PartitionColumn as MONTH([OrderDate]) PERSISTED,
) ON partition_scheme_ByMonth ( [PartitionColumn] );

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.

set identity_insert SalesOrderHeader on

insert into SalesOrderHeader (
SalesOrderID, RevisionNumber, OrderDate, DueDate,
ShipDate, Status, OnlineOrderFlag, --SalesOrderNumber,
PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID,
TerritoryID, BillToAddressID, ShipToAddressID,
ShipMethodID, CreditCardID, CreditCardApprovalCode,
CurrencyRateID, SubTotal, TaxAmt, Freight, --TotalDue,
Comment, rowguid, ModifiedDate --PartitionColumn
)
select
SalesOrderID, RevisionNumber, OrderDate, DueDate,
ShipDate, Status, OnlineOrderFlag, --SalesOrderNumber,
PurchaseOrderNumber,
AccountNumber, CustomerID, SalesPersonID, TerritoryID,
BillToAddressID, ShipToAddressID, ShipMethodID,
CreditCardID, CreditCardApprovalCode, CurrencyRateID,
SubTotal, TaxAmt, Freight, --TotalDue,
Comment, rowguid, ModifiedDate
from AdventureWorks2014.Sales.SalesOrderHeader

set identity_insert SalesOrderHeader off

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

SELECT 1 [month], COUNT(*) [count] FROM dbo.SalesOrderHeader WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 1
union all
SELECT 2 [month], COUNT(*) [count] FROM dbo.SalesOrderHeader WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 2
union all
SELECT 3 [month], COUNT(*) [count] FROM dbo.SalesOrderHeader WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 3
union all
SELECT 4 [month], COUNT(*) [count] FROM dbo.SalesOrderHeader WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 4
union all
SELECT 5 [month], COUNT(*) [count] FROM dbo.SalesOrderHeader WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 5
union all
SELECT 6 [month], COUNT(*) [count] FROM dbo.SalesOrderHeader WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 6
union all
SELECT 7 [month], COUNT(*) [count] FROM dbo.SalesOrderHeader WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 7
union all
SELECT 8 [month], COUNT(*) [count] FROM dbo.SalesOrderHeader WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 8
union all
SELECT 9 [month], COUNT(*) [count] FROM dbo.SalesOrderHeader WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 9
union all
SELECT 10 [month], COUNT(*) [count] FROM dbo.SalesOrderHeader WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 10
union all
SELECT 11 [month], COUNT(*) [count] FROM dbo.SalesOrderHeader WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 11
union all
SELECT 12 [month], COUNT(*) [count] FROM dbo.SalesOrderHeader WHERE $PARTITION.partition_function_ByMonth(PartitionColumn) = 12

select DATEPART(MM,OrderDate) [month], Count(*) [count]
from SalesOrderHeader
group by DATEPART(MM,OrderDate)
order by DATEPART(MM,OrderDate)






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums







Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems