如何按月对表进行分区（“年”和“月”）并自动创建月度分区？

Question

我正在尝试按 年 和 月
对表进行分区。我将通过其划分分区的列是具有ISO格式（‘20150110’，20150202’等）的日期时间类型列。

例如，我有2010年，2011年和2012年的销售数据。我希望数据按年份进行分区，并且每年也按月份进行分区。（2010/01，2010/02，…
2010/12，2011/01，… 2015/01 …）

前任：

Sales2010Jan，Sales2010Feb，Sales2011Jan，Sales2011Feb，Sales2012Dec等

我的问题是：有可能吗？如果是，我如何使用SSIS自动执行该过程？

Answer 1

SSIS是一个ETL（提取，转换，加载）。这不是您想要做的。您只需要动态创建DDL语句。

我在以下季度工作，但如果需要，它也可以在1、2或X个月使用。

如果要对表进行分区，则首先需要创建文件，文件组和分区表并手动设置分区

在具有int标识PK和datetime2分区列的表上为2015 Q1（在Q1之前和Q2之后）创建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’ , 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]

• 在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);
  

构建用于在右边界后添加额外文件组的查询，并拆分最后一个分区

• 查看分区方案扩展和分区功能拆分
• 查看使用的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), @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 = id10, 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 id10+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 id10+2, ‘Print ‘’Add Range [Part_’+name+’]’‘
  Alter Partition Scheme RangePartScheme Next Used [Part_’+name+’]
  Go’
  From d
  Union All
  Select id10+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

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

执行动态SQL

• 可以手动执行（SSMS中的复制和过往操作）
• 它可以在while循环中执行，也可以使用游标执行，游标将逐一执行输出表的每一行（使用sp_executesql）

自动化

• 创建一个执行SQL查询的SQL Server作业：运行用于创建动态SQL的查询，将其输出保存到表变量中，然后使用循环/游标执行每个语句

如果要每月运行一次并确保始终创建接下来的12个月，请使用此命令 Set @endDate = DATEADD(MONTH, 12, getdate())

最后

• 它将为函数的最后一个边界和@endDate之间的N个丢失的四分之一输出4 * N行：

  • 创建文件组
  • 在文件组上创建文件
  • 扩展分区方案的范围
  • 分割分区功能的范围
  • 您可以使用光标或while循环逐行运行它，也可以将其复制并粘贴到SMSS中。

• 它也可以通过工作自动化，即。@endDate = DATEADD(MONTH, 3, getdate()将在接下来的3个月内创建

• 如果要每月分区，请将@dateAdd更改为1
• 添加您自己的列或检查

关联

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

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

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