如何按月对表进行分区（“ YEAR& MONTH”）并自动创建月度分区？ [英] How to Partition a Table by Month ("Both" YEAR & MONTH) and create monthly partitions automatically?

问题描述

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

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

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

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

EX：

Sales2010Jan，Sales2010Feb，Sales2011Jan，Sales2011Feb，Sales2012Dec等。

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

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

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

推荐答案

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

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

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

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

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

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

• 首先创建N个文件组：

• 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]

在以下位置创建分区函数datetime2类型（或日期或什至日期时间）：

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

使用分区函数创建分区方案在每个文件组（N + 1）上：

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

在id列中添加PK：

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

• 查看分区方案扩展和分区功能拆分


• 查看使用的DMV



• 查看所有这些内容以及如何使用它创建动态SQL

  • 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
    

  
  

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

  the output of this query is a list of SQL queries that must be run in order.

  
  

  执行动态SQL

  Execute the dynamic SQL

  
  
  

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

  
  

  自动化

  Automation

  
  
  

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

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

  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

  
  
  

  
  

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

    • It will output 4*N rows for the N missing quarters between the last boundary of the function and @endDate:

      
      
      • 创建文件组
      
      
      • 在文件组上创建文件
      
      
      • 扩展分区Scheme的范围
      
      
      • 分割分区函数的范围
      
      
      • 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.

      
      

      链接

      Link

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

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

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

      这篇关于如何按月对表进行分区（“ YEAR& MONTH”）并自动创建月度分区？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！