How to Partition a Table by Month ("Both" YEAR & MONTH) and create monthly partitions automatically?

Question

I'm trying to Partition a Table by both Year and Month. The Column through which I'll partition is a datetime type column with an ISO Format ('20150110', 20150202', etc).

For example, I have sales data for 2010, 2011, 2012. I'd Like the data to be partitioned by year and each year be partitioned by month as well. (2010/01, 2010/02, ... 2010/12, 2011/01, ... 2015/01...)

E.X:

Sales2010Jan, Sales2010Feb, Sales2011Jan, Sales2011Feb, Sales2012Dec, etc.

My Question is: is it even possible? If it is, how an I automate the process using SSIS?

Answer 1

SSIS is an ETL (extract, transform, load). This is not what you want to do. You just need to create DDL statements dynamically .

I work with quarter below but it works as well with 1, 2 or X months if you want.

If you want to partition the table, you first need to create the file, filegroups and partionned table and set the partitionning manually

Creation of N+1 partitions for 2015 Q1 (before, Q1 and after Q2) on a table with an int identity PK and a datetime2 partitioned column. Update it to add months, make it monthly or whatever you need...

• First create N files groups:

  Alter Database [Test] Add Filegroup [Part_Before2015]
  Go
  Alter Database Test Add Filegroup [Part_201501]
  Go
  Alter Database Test Add Filegroup [Part_201504]
  Go
  

• Add a file for each filegroup:

  Alter Database [Test] Add FILE ( NAME = N'Part_Before2015', FILENAME = N'...\Part_Before2015.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO Filegroup [Part_Before2015]
  Alter Database [Test] Add FILE ( NAME = N'Part_201501', FILENAME = N'...\Part_201501.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO Filegroup [Part_201501]
  Alter Database [Test] Add FILE ( NAME = N'Part_201504', FILENAME = N'...\Part_201504.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO Filegroup [Part_201504]
  

• Create a partition function on a datetime2 type (or date or even datetime):

  Create Partition Function RangePartFunction (datetime2)
  as Range Right For Values ('20150101', '20150401') 
  

• Create a partition scheme using the partition function on each filegroup (N+1):

  Create Partition Scheme RangePartScheme as Partition RangePartFunction
  To ([Part_Before2015], [Part_201501], [Part_201504])
  

• Create the partitioned table on its partition scheme:

  Create TABLE [PartitionTable] (id int identity(0, 1) not null, date datetime2 not null, text char(8000))
  On RangePartScheme (date) ;
  

• Add a Clustered index on the partitioned column and partition scheme:

  Create Clustered Index IDX_Part On dbo.PartitionTable(date) 
      On RangePartScheme (date);
  

• Add a PK to the id column:

  Alter Table dbo.PartitionTable Add COntraint PK_Part Primary Key Nonclustered(id, date);
  

Build the query used to add extra file groups after the right boundary and split the last partition

• Review partition scheme extension and partition function split
• Review DMV used

• Review all of this and how to use it to create dynamic SQL

  Declare @currentDate datetime2
  Declare @endDate datetime2 = '20160701' -- new end date
  Declare @dateAdd int = 3 -- Add 3 month = 1 Quarter
  
  -- Get Current boundaries 
  Select @currentDate = DATEADD(MONTH, @dateAdd,Cast(MAX(value) as datetime2)) From sys.partition_range_values as r
      Inner Join sys.partition_functions as f on r.function_id = f.function_id
  Where f.name = 'RangePartFunction'
  
  -- Get all quarters between max and end date
  ; with d(id, date, name) as (
      Select 0, @currentDate, Convert(char(6), @currentDate, 112)
      Union All
      Select id+1, DATEADD(MONTH, @dateAdd, date), Convert(char(6), DATEADD(MONTH, @dateAdd, date), 112)
      From d Where d.date <= @endDate
  )
  Select * From (
      Select id = id*10, query = 'If Not Exists(Select 1 From sys.filegroups Where name = ''Part_'+name+''')
          Begin 
              Print ''Create Filegroup [Part_'+name+']''
              Alter Database [Test] Add Filegroup [Part_'+name+']
          End
          GO'
      From d
      Union All
      Select id*10+1, 'If Not Exists(Select 1 From sys.sysfiles Where name = ''Part_'+name+''')
          Begin 
              Print ''Create File [Part_'+name+'.ndf]''
              Alter Database [Test] Add FILE ( NAME = N''Part_'+name+''', FILENAME = N''C:\DB\MSSQL11.MSSQLSERVER\MSSQL\DATA\Part_'+name+'.ndf'' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO Filegroup [Part_'+name+']
          End
          GO'
      From d
      Union All
      Select id*10+2, 'Print ''Add Range [Part_'+name+']''
          Alter Partition Scheme RangePartScheme Next Used [Part_'+name+']
          Go'
      From d
      Union All
      Select id*10+3, 'Print ''Split Function ['+Convert(char(8), date, 112)+']''
          Alter Partition Function RangePartFunction() Split Range ('''+Convert(char(8), date, 112)+''');
          Go'
      From d
  ) as q order by id
  

the output of this query is a list of SQL queries that must be run in order.

Execute the dynamic SQL

• It can be executed manually (copy and past in SSMS)
• It can be executed in a while loop or with a cursor which will executed each row of the ouput table one by one (use sp_executesql)

Automation

• Create a SQL Server Job which excute SQL queries: run the query used to create the dynamic SQL, save its output to a table variable and then execute each statement with a loop/cursor

If you want to run it monthly and make sure the next 12 months are always created, use this Set @endDate = DATEADD(MONTH, 12, getdate())

Finally

• It will output 4*N rows for the N missing quarters between the last boundary of the function and @endDate:

  • Create Filegroup
  • Create File on Filegroup
  • Extend the range of the partition Scheme
  • Split the range of the partition function

• You can run it row by row with a cursor or a while loop or you can just copy and paste it in SMSS.

• It can be automated with a job as well ie. @endDate = DATEADD(MONTH, 3, getdate() will create the next 3 months
• Change @dateAdd to 1 if you want monthly partitions
• Add you own columns or checks

Link

Create job = https://www.mssqltips.com/sqlservertip/3052/simple-way-to-create-a-sql-server-job-using-tsql/

sp_executesql = https://technet.microsoft.com/en-us/library/ms188001%28v=sql.110%29.aspx

While loop = https://dba.stackexchange.com/questions/57933/can-exec-work-with-while-loop-of-cursor