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?

Solution

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

Comments

[Amr Tharwat]

Isnt there a possible way to create an automated SSIS Maintenance Package that will keep updating the partitions based on the dates? Meaning that for example if it runs yearly, then it will create the partitions for the new year and its month..

[Julien Vavasseur]

Sql server can automatically execute a task which contains SQL or SSIS package. In this case you have sql. There is no need to use an SSIS package. If you run this script and replace the fixed date at the beginning by get date + 12 or 13 months. It will do the job. You need to add a loop which will execute each statement from the last select. I explained it all at the end of my answer. All is done using SQL.

[Amr Tharwat]

Please excuse me as i'm not an expert yet i'm still new to all this :) I understood the concept of all what you wrote so far and thank you for that. However, what i didnt get is what the last part of your query (-- Get all quarters between max and end date) when i run it, it doesnt create any file groups between the 2 dates. just prints queries.. what is the purpose of this? sorry again

[Julien Vavasseur]

more details and links added at the end. it generates dynamic SQL (what you saw). Each row then still has to be executed (manually or automatically) one by one. if you last partition is 201504 (set to @currentDate) and you want to create up until 201604 (value of @endDate), it will create queries for 201507, 201510, 201601 and 201604. Change @dateAdd to 1 and it will generate a script for each month between 201504 and 201604.

[Amr Tharwat]

Thank you so much for the detailed explanation :) ill get right on the edited solution and let you know how it goes :)

[Amr Tharwat]

I appologize for the delay as i was away due to sick-leave. Anyways, Thank you so much for your effort it worked perfectly :)

[bsplosion]

It's crazy that SQL Server doesn't have automatic date interval partitioning, even today. While this answer is great for those who want to schedule a job that runs frequently, other platforms (e.g., Oracle and Teradata) have had this functionality automated since as long as 14 years ago (Oracle 11g launch). Microsoft has really dropped the ball on some features.