sql – 如何按月分区表(“两年”和“年”)并自动创建每月分区？

我正在尝试按年和月分区表.我将分区的列是具有ISO格式(‘20150110’,20150202’等)的日期时间类型列.

例如,我有2010年,2011年,2012年的销售数据.我希望按年划分数据,每年也按月划分数据. (2010 / 01,2010 / 02,… 2010 / 12,2011 / 01,… 2015/01 ……)

E.X：

Sales2010Jan,Sales2010Feb,Sales2011Jan,Sales2011Feb,Sales2012Dec等

我的问题是：它甚至可能吗？如果是,我如何使用SSIS自动化流程？

@H_404_10@

解决方法

SSIS是ETL(提取,转换,加载).这不是你想要做的.
您只需要动态创建DDL语句.

我在下面的四分之一下工作,但是如果你愿意,它可以在1,2或X个月工作.

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

在具有int标识PK和datetime2分区列的表上为2015 Q1(之前,第1季度和第2季之后)创建N 1个分区.
更新它以添加月份,每月或任何你需要的…

>首先创建N个文件组：

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

>为每个文件组添加文件：

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',FILEGROWTH = 1024KB ) TO Filegroup [Part_201501]
Alter Database [Test] Add FILE ( NAME = N'Part_201504',FILENAME = N'...\Part_201504.ndf',FILEGROWTH = 1024KB ) TO Filegroup [Part_201504]

>在datetime2类型(或日期甚至日期时间)上创建分区函数：

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

>使用每个文件组(N 1)上的分区函数创建分区方案：

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

>在其分区方案上创建分区表：

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

>在分区列和分区方案上添加聚簇索引：

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

>将PK添加到id列：

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

>查看分区方案扩展和分区功能拆分
>回顾使用的DMV
>查看所有这些以及如何使用它来创建动态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),112)
    Union All
    Select id+1,DATEADD(MONTH,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'',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),112)+']''
        Alter Partition Function RangePartFunction() Split Range ('''+Convert(char(8),112)+''');
        Go'
    From d
) as q order by id

此查询的输出是必须按顺序运行的SQL查询列表.