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自动化流程？

解决方法

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查询列表.