SQL 如何按月（“YEAR 和 MONTH”）对表进行分区并自动创建每月分区？

Question

提问by Amr Tharwat

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

我正在尝试按Year和Month对表进行分区。我将通过其分区的列是具有 ISO 格式（'20150110'、20150202' 等）的日期时间类型列。

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...)

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

E.X:

前任：

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

Sales2010Jan、Sales2010Feb、Sales2011Jan、Sales2011Feb、Sales2012Dec，等等。

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

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

Answer 1

回答by Julien Vavasseur

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

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

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

我使用下面的季度，但如果您愿意，它也适用于 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

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

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...

在具有 int 标识 PK 和 datetime2 分区列的表上为 2015 Q1（之前、Q1 和 Q2 之后）创建 N+1 个分区。更新它以增加月份，每月或任何您需要的...

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

首先创建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);

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

回顾分区方案扩展和分区函数拆分
查看使用的 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 = 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.

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

Execute the dynamic SQL

执行动态 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)

可以手动执行（在SSMS中复制和粘贴）
它可以在 while 循环中执行，也可以使用游标执行，该游标将逐行执行输出表的每一行（使用 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

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

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())

如果您想每月运行一次并确保始终创建接下来的 12 个月，请使用此 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

它将为函数的最后一个边界和@endDate 之间的 N 个缺失的四分之一输出 4*N 行：
- 创建文件组
- 在文件组上创建文件
- 扩展分区方案的范围
- 分割分区函数的范围
您可以使用游标或 while 循环逐行运行它，也可以将其复制并粘贴到 SMSS 中。
它也可以通过工作自动化，即。@endDate = DATEADD(MONTH, 3, getdate()将创建未来 3 个月
如果您想要每月分区，请将 @dateAdd 更改为 1
添加您自己的列或检查

Link

关联

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

创建作业 = 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

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

SQL 如何按月（“YEAR 和 MONTH”）对表进行分区并自动创建每月分区？

提问by Amr Tharwat

回答by Julien Vavasseur

相关推荐

最近更新

标签

SQL 如何按月（“YEAR 和 MONTH”）对表进行分区并自动创建每月分区？

提问by Amr Tharwat

回答by Julien Vavasseur

相关推荐

SQL 如果值为空，如何将值返回给 sqldatareader？

SQL ORA-01791: 不是 SELECTed 表达式

在 SQL Server 中的文本字段上使用 LEFT

如何在 Oracle SQL 查询中的数字中添加前导零？

相关推荐

最近更新

标签