SQL SERVER之分区表创建

SQL SERVER分区表的建立
以某一日志表robot_golds_log为例按月分区ctime分区 从2022年6月1日到-2022年12月1日

一，创建文件组
    2022-06-01    1654012800
    2022-07-01    1656604800
    2022-08-01    1659283200
    2022-09-01    1661961600
    2022-10-01    1664553600
    2022-11-01    1667232000
    2022-12-01    1669824000
    
    N个边界值确定 N+1 个分区
    
   

use sblack
    go
    ALTER DATABASE sblack ADD FILEGROUP order1;
    go
    ALTER DATABASE sblack ADD FILEGROUP order2;
    go
    ALTER DATABASE sblack ADD FILEGROUP order3;
    go
    ALTER DATABASE sblack ADD FILEGROUP order4;
    go
    ALTER DATABASE sblack ADD FILEGROUP order5;
    go
    ALTER DATABASE sblack ADD FILEGROUP order6;
    go
    ALTER DATABASE sblack ADD FILEGROUP order7;
    go
    
    ALTER DATABASE sblack   
    ADD FILE   
    (  
        NAME = order1dat1,  
        FILENAME = 'E:\psdata\order1.ndf',  
        SIZE = 5MB,  
        MAXSIZE = 100MB,  
        FILEGROWTH = 5MB  
    )
    TO FILEGROUP order1;

    go
    ALTER DATABASE sblack
    ADD FILE   
    (  
        NAME = order2dat2,  
        FILENAME = 'E:\psdata\order2.ndf',  
        SIZE = 5MB,  
        MAXSIZE = 100MB,  
        FILEGROWTH = 5MB  
    )
    TO FILEGROUP order2;

    go
    ALTER DATABASE sblack
    ADD FILE   
    (  
        NAME = order3dat3,  
        FILENAME = 'E:\psdata\order3.ndf',  
        SIZE = 5MB,  
        MAXSIZE = 100MB,  
        FILEGROWTH = 5MB  
    )
    TO FILEGROUP order3;

    go
    ALTER DATABASE sblack
    ADD FILE   
    (  
        NAME = order4dat4,  
        FILENAME = 'E:\psdata\order4.ndf',  
        SIZE = 5MB,  
        MAXSIZE = 100MB,  
        FILEGROWTH = 5MB  
    )
    TO FILEGROUP order4;

    go
    ALTER DATABASE sblack
    ADD FILE   
    (  
        NAME = order5dat5,  
        FILENAME = 'E:\psdata\order5.ndf',  
        SIZE = 5MB,  
        MAXSIZE = 100MB,  
        FILEGROWTH = 5MB  
    )
    TO FILEGROUP order5;

    go
    ALTER DATABASE sblack
    ADD FILE   
    (  
        NAME = order6dat6,  
        FILENAME = 'E:\psdata\order6.ndf',  
        SIZE = 5MB,  
        MAXSIZE = 100MB,  
        FILEGROWTH = 5MB  
    )
    TO FILEGROUP order6;

    go
    ALTER DATABASE sblack
    ADD FILE   
    (  
        NAME = order7dat7,  
        FILENAME = 'E:\psdata\order7.ndf',  
        SIZE = 5MB,  
        MAXSIZE = 100MB,  
        FILEGROWTH = 5MB  
    )
    TO FILEGROUP order7;

    go

 

    
二，创建分区函数
 

  CREATE PARTITION FUNCTION PF_Orders_Ctime(ctime)
    AS RANGE RIGHT FOR VALUES
    (
      1654012800,
      1656604800,
      1659283200,
      1661961600,
      1664553600,        
      1667232000,
    )

 

    
三，创建分区方案

    CREATE PARTITION SCHEME PS_Orders
    AS PARTITION PF_Orders_Ctime
    TO (order1, order2, order3, order4,order5,order6,order7);

 

    
四，创建使用分区的表
    --创建分区表语法
    create table <表名> (
    <列定义>
    )on<分区方案名>(分区列名)
    
 

  use sblack
    go
    CREATE TABLE [dbo].[robot_golds_log](
    [id] [BIGINT] IDENTITY(1,1) NOT NULL,
    [robot_id] [INT] NOT NULL,
    [source_type] [SMALLINT] NOT NULL,
    [kind_id] [INT] NOT NULL,
    [room_id] [INT] NOT NULL,
    [score] [BIGINT] NOT NULL,
    [play_time] [INT] NOT NULL,
    [ctime] [INT] NOT NULL,
    [memo] [NVARCHAR](256) NULL,
    [score_type] [TINYINT] NOT NULL,
    CONSTRAINT [PK_robot_golds_log1_tmp] PRIMARY KEY CLUSTERED
    (
    [id] ASC,
    [ctime] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PS_Orders]([ctime])
    ) ON [PS_Orders] ([ctime]) 

 

    
    索引也基本也要创建分区索引,例：
 

  CREATE NONCLUSTERED INDEX [ix_ctime_source_type] ON [dbo].[robot_golds_log]
    (
        [ctime] ASC,
        [source_type] ASC
    )WITH (online = ON) ON [PS_Orders]([ctime])
    GO

 

    
五，查看依据分区列所在的分区
    select $partition.PF_Orders_Ctime(1654099200) 返回2 说明在第2个分区
    
六，查看每个非空分区存在的行数
  

 SELECT $partition.PF_Orders_Ctime(ctime) as partitionNum,count(*) as recordCount
    FROM dbo.robot_golds_log
    GROUP BY $partition.PF_Orders_Ctime(ctime)

 

    
七，查看各个分区的数据信息

    SELECT PARTITION = $PARTITION.PF_Orders_Ctime(ctime),
       ROWS      = COUNT(*),
       MinVal    = MIN(ctime),
       MaxVal    = MAX(ctime)
    FROM [dbo].[robot_golds_log]
    GROUP BY $PARTITION.PF_Orders_Ctime(ctime)
    ORDER BY PARTITION

 

    
八，拆分分区
    alter partition function PF_Orders_Ctime() split range(1688140800)    --2023-07-01
    如果分区函数已经指定了分区方案，则分区数需要和分区方案中指定的文件组个数保持对应一致

九，合并分区
    alter partition function PF_Orders_Ctime() merge range(1688140800)
    
十，分区移动
    切换分区为3的数据从分区表到归档表

    ALTER TABLE dbo.robot_golds_log SWITCH PARTITION 3
    TO dbo.robot_golds_log_2022
    
    注意：归档表    robot_golds_log_2022 要和 原表在一个文件组上 创建表的时候指定文件组  on [文件组]
    
    把归档表的数据切到分区表
    
    ALTER TABLE dbo.robot_golds_log_2022 SWITCH TO
    dbo.robot_golds_log PARTITION 3
    
    归档表要添加约束
    alter table robot_golds_log_2022 ADD CONSTRAINT CK_robot_golds_log_ctime CHECK(ctime>=1656604800 and ctime<1659283200)  第3个分区的起始时间