SQL SERVER 分区表的总结--分区表的维护和管理

Love The Way You Lie 2022-03-30 07:58 255阅读 0赞

在依据需求建立好分区表之后，就要实现对分区表的管理维护。主要内容就是两点：

1.  利用滑动窗口方案(Sliding Window Scenario)，实现分区表与数据移动中间表的互切。

2.  分区表本身的结构变更管理。

当然以上两点，都是理论上的点。我工作中主要是遇到两种需求：

1.  过时分区数据的快速归档管理：即把某些不活跃分区的数据切到数据归档的表中去。

2.  分区表由于数据增长需要添加新分区来处理数据：即已分区的表它所有的数据都会处于活动状态，需要新的分区来承载新增的数据。

这两种需求将会在下面的代码体现出来。分区表沿用我[上一篇][Link 1]中的表**Product**,但是这里称为**Products**。因为[上一篇][Link 1]中的表，测试系统在用，我不能移动数据，就只好克隆出一张表。

** **创建一张**表结构**和**主键聚集索引**跟Products**一模一样**的表Products\_Tmp(用来做数据移转的中间临时表);这张表还**必须**跟要移出的分区位于同一个文件组(否则将不能应用Partition Switch进行快速切换)。

首先在新文件组上创建存档表Products\_Archive，用于存档后面中间临时表的数据。

USE [master]
    
    GO
    
    ALTER DATABASE [TEST]
    
    ADD FILEGROUP [FG_TEST_Products_Archive]
    
    GO
    
    ALTER DATABASE [TEST]
    
    ADD FILE ( NAME = N'FG_TEST_Products_Archive_data_1',
    
              FILENAME = N'D:\Data\FG_TEST_Products_Archive_data_1.ndf' ,
    
              SIZE = 50MB ,
    
              FILEGROWTH = 10% )
    
    TO FILEGROUP [FG_TEST_Products_Archive]
    
    GO
    
     
    
    USE TEST
    
    GO
    
    CREATE TABLE [dbo].[Products_Archive](
    
        [ID] [int] NOT NULL,
    
        [PName] [nvarchar](100) NULL,
    
        [AddDate] [datetime2](3) NULL
    
    )ON [FG_TEST_Products_Archive];
    
    GO

接下来，就是移转数据的操作了。封装成存储过程，方便调用。本来是想把导数据也写在里面，可是考虑到生产环境数据量较大，会采用其它的导数据方案，就舍弃了。

CREATE PROCEDURE usp_TransferPartitionData_ForArchive
    
      @PartitonNumber INT --要移转分区编号
    
    AS
    
    DECLARE @filegroup NVARCHAR(100)=N'',   --要移转分区所在文件组
    
            @SQL NVARCHAR(4000)=N'',        --创建中间临时表的动态语句
    
            @rangeValue INT;                --要移转分区边界值上限
    
     
    
    SET @rangeValue=(SELECT CAST(VALUE AS INT) FROM sys.partition_range_values WHERE boundary_id=@rangeValue);
    
    --这里的赋值,使用我上篇中的自定义函数fn_GetFileForPartition.
    
    SET @filegroup=(SELECT [FILEGROUP_NAME] FROM dbo.fn_GetFileForPartition(N'Sch_Product_ID',@rangeValue));
    
    SET @SQL=N'CREATE TABLE [dbo].[Products_Tmp](
    
                [ID] [int] NOT NULL,
    
                [PName] [nvarchar](100) NULL,
    
                [AddDate] [datetime2](3) NULL,
    
              CONSTRAINT [PK_Products_Tmp] PRIMARY KEY CLUSTERED
    
             (
    
                [ID] ASC
    
             )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
    
             )ON '+QUOTENAME(@filegroup);
    
            
    
    IF OBJECT_ID('dbo.Products_Tmp') IS NOT NULL
    
      RAISERROR('已经存在数据库对象Products_Tmp!',16,1);
    
    ELSE
    
     BEGIN
    
        --PRINT @SQL;
    
      EXEC(@SQL);
    
      --把指定分区中的数据切到Products_Tmp
    
      ALTER TABLE dbo.Products
    
       SWITCH PARTITION @PartitonNumber
    
       TO dbo.Products_Tmp;
    
     END
    
    --因为数据量少又是堆表，直接使用INSERT INTO导入数据。
    
    --如果是大数据量的话可能就会使用BULK+BCP或者SSIS来做。
    
    --INSERT INTO [dbo].[Products_Archive]
    
    --([ID],[PName],[AddDate])
    
    -- SELECT [ID],[PName],[AddDate] FROM [dbo].[Products_Tmp];
    
     
    --DROP TABLE [dbo].[Products_Tmp
    
    GO
    
     
    
    --移转数据，并删除Products_Tmp。
    
     
    
    EXEC usp_TransferPartitionData_ForArchive 1;--指定移转第一个分区
    
    --因为数据量少又是堆表，直接使用INSERT INTO导入数据。
    
    --如果是大数据量的话可能就会使用BULK+BCP或者SSIS来做。
    
    INSERT INTO [dbo].[Products_Archive]
    
    ([ID],[PName],[AddDate])
    
     SELECT [ID],[PName],[AddDate] FROM [dbo].[Products_Tmp];
    
    DROP TABLE [dbo].[Products_Tmp];

现在旧数据移出去了，但是每天还有新数据进来，最后一个分区越来越大，所以我们要加入新文件组来承接这个分区。但是这里，就不新建文件组，而是使用在上面移转数据时”空”出来的PRIMARY文件组。

首先分区既然空了，就要从分区表中移除它。然后再将其做为新文件组加进分区表。其实下面的代码也可以整合到上面的SP里去的。

DECLARE @rangeValue INT, --要删除分区边界值的上限
    
            @splitValue INT,--要分割给新分区用的边界值下限
    
            @filegroup NVARCHAR(100)=N'',   --要合并的分区所属的文件组
    
            @SQL NVARCHAR(4000);--动态语句
    
     
    
    --合并空分区，也就是删除分区。    
    
    SET @rangeValue=80000;
    
    ALTER PARTITION FUNCTION fn_Partition_Product_ID()
    
     MERGE RANGE (@rangeValue);
    
     
    
    --修改架构，添加文件组
    
    SET @filegroup=(SELECT [FILEGROUP_NAME] FROM dbo.fn_GetFileForPartition(N'Sch_Product_ID',80000));
    
    SET @SQL=N'ALTER PARTITION SCHEME Sch_Product_ID
    
                 NEXT USED '+QUOTENAME(@filegroup)+N';';
    
    --PRINT @SQL;
    
    EXEC(@SQL);
    
     
    
    --增加新分区，来接受新数据
    
    SET @splitValue=380000;
    
    ALTER PARTITION FUNCTION fn_Partition_Product_ID()
    
      SPLIT RANGE (@splitValue);

[Link 1]: http://www.cnblogs.com/Joe-T/archive/2011/11/30/2269662.html