SqlServer表分区

末蓝、 2022-07-15 07:52 256阅读 0赞

一、创建普通表并插入数据准备测试环境
    
    --创建普通表
    CREATE TABLE [dbo].[fenquTable](
        [id] [int] IDENTITY(1,1) NOT NULL,
        [name] [nvarchar](50) NULL,
        [createTime] [datetime] NULL
        );
    
    ALTER TABLE [dbo].[fenquTable] ADD  CONSTRAINT [PK_fenquTable] PRIMARY KEY NONCLUSTERED 
    (
        [id] ASC
    )
    GO
    CREATE CLUSTERED INDEX [CT_fenquTable] ON [dbo].[fenquTable]
    (
        [createTime] ASC
    )
    GO
    --插入测试数据
    insert into fenquTable(name,createTime) values ('隔壁老王','2010-01-01');
    insert into fenquTable(name,createTime) values ('隔壁老张','2011-01-01');
    insert into fenquTable(name,createTime) values ('隔壁老赵','2012-01-01');
    insert into fenquTable(name,createTime) values ('隔壁老李','2013-01-01');
    insert into fenquTable(name,createTime) values ('老李儿子','2013-10-01');
    insert into fenquTable(name,createTime) values ('隔壁老田','2014-01-01');
    insert into fenquTable(name,createTime) values ('隔壁老梁','2015-01-01');
    insert into fenquTable(name,createTime) values ('老梁姑娘楠楠','2015-10-10');
    
    
    
    二、准备文件组、文件、分区函数、分区方案
    --添加文件组
    alter database Baike add filegroup group2013;
    alter database Baike add filegroup group2014;
    alter database Baike add filegroup group2015;
    --添加文件
    alter database Baike 
    add file(name='web2013',filename='D:\data\web2013.ndf',size=5mb,filegrowth=5mb)
    to filegroup group2013;
    
    alter database Baike 
    add file(name='web2014',filename='D:\data\web2014.ndf',size=5mb,filegrowth=5mb)
    to filegroup group2014;
    
    alter database Baike 
    add file(name='web2015',filename='D:\data\web2015.ndf',size=5mb,filegrowth=5mb)
    to filegroup group2015;
    
    ---分区函数
    create partition function fenqu(datetime) --分区函数名
    as range right  --right分区方式 边界值去左表还是右表
    for values ('2014-01-01','2015-01-01') --按这些值来分区 
    --group2013 : 2014-01-01 之前的
    --group2014 : 2014-01-01 到 2014-12-31的
    --group2015 : 2015-01-01 之后的
    
    
    --创建分区方案
    create partition scheme SchemeFenqu --分区方案名
    as partition fenqu    --之前创建的分区函数
    to(group2013,group2014,group2015); --之前创建的文件组
    
    
    三、将普通表转换为分区表
    --删除主键，自动同时删除索引
    alter table fenquTable drop constraint PK_fenquTable 
    
    --创建主键，但不创建聚集索引
    alter table fenquTable add constraint PK_fenquTable
    primary key nonclustered --非聚集
    (id asc) on [primary];
    
    --然后在createTime字段上创建一个聚集索引
    create clustered index CT_fenquTable on fenquTable(createTime)
    with(drop_existing=on) --如果存在则删除
    on schemeFenqu(createTime); --并调用分区方案
    
    --然后再查询分区，发现数据保留情况下，已经将数据按规则进行分区了
    select $partition.fenqu(createTime) as 分区,count(id) as 数量
    from fenquTable group by $partition.fenqu(createTime);
    
    --查看分区表明细
    select * from fenquTable where $partition.fenqu(createTime)=1;
    select * from fenquTable where $partition.fenqu(createTime)=2;
    select * from fenquTable where $partition.fenqu(createTime)=3;
    
    
    
    四、添加分区
    
    --添加2016年数据
    insert into fenquTable (name,createTime) values ('16年小明','2016-03-05');
    
    --创建文件组
    alter database Baike add filegroup group2016
    
    --添加数据库文件
    alter database Baike 
    add file(name='web2016',filename='D:\data\web2016.ndf',size=5mb,filegrowth=5mb)
    to filegroup group2016
    
    --修改分区方案
    alter partition scheme SchemeFenqu
    next used group2016;
    
    --修改分区函数
    alter partition function fenqu()
    split range('2016-01-01');
    
    
    --查看分区及统计
    select $partition.fenqu(createTime) as 分区,count(id) as 数量
    from fenquTable group by $partition.fenqu(createTime);
    
    五、删除分区
    --删掉该边界值
    alter partition function fenqu() merge range('2014-01-01');
    
    --将指定文件中的所有数据迁移到同一文件组中的其他文件  
    DBCC SHRINKFILE ([web2014], EMPTYFILE);  
    GO  
    
    --移除文件、文件组
    ALTER DATABASE [Baike] REMOVE FILE [web2014]  
    GO  
    ALTER DATABASE [Baike] REMOVE FILEGROUP [group2014]  
    GO  
    
    
    --再次查询
    select $partition.fenqu(createTime) as 分区,count(id) as 数量
    from fenquTable group by $partition.fenqu(createTime);
    
    DBCC showfilestats  
    GO
    
    六、拆分分区
    --创建文件组
    alter database Baike add filegroup group2014
    
    --添加数据库文件
    alter database Baike add file(name='web2014',filename='D:\data\web2014.ndf',size=5mb,filegrowth=5mb)
    to filegroup group2014
    
    --修改分区方案
    alter partition scheme SchemeFenqu
    next used group2014
    
    --修改分区函数
    alter partition function fenqu()
    split range('2014-01-01')
    
    --查看分区及统计
    select $partition.fenqu(createTime) as 分区,count(id) as 数量
    from fenquTable group by $partition.fenqu(createTime);
    
    DBCC showfilestats  
    GO
    
    七、分区表转普通表
    --修改分区函数 将边界值都删除
    --这样虽然只有一个分区了，但是查看数据表存储位置，是否进行分区：True，分区数1.
    alter partition function fenqu()merge range('2014-01-01');
    alter partition function fenqu()merge range('2015-01-01');
    alter partition function fenqu()merge range('2016-01-01');
    
    --查询
    select $partition.fenqu(createTime) as 分区,count(id) as 数量
    from fenquTable group by $partition.fenqu(createTime);
    
    DBCC showfilestats  
    GO
    
    
    --清空文件
    --DBCC SHRINKFILE ('web2013', EMPTYFILE);--重新建立聚集索引后再删
    DBCC SHRINKFILE ('web2014', EMPTYFILE);
    DBCC SHRINKFILE ('web2015', EMPTYFILE);
    DBCC SHRINKFILE ('web2016', EMPTYFILE);
    
    
    --删除文件
    --ALTER DATABASE [Baike] REMOVE FILE [web2013];--重新建立聚集索引后再删
    ALTER DATABASE [Baike] REMOVE FILE [web2014];
    ALTER DATABASE [Baike] REMOVE FILE [web2015];
    ALTER DATABASE [Baike] REMOVE FILE [web2016];
    
    --删除文件组
    --ALTER DATABASE [Baike] REMOVE FILEGROUP [group2013];--重新建立聚集索引后再删
    ALTER DATABASE [Baike] REMOVE FILEGROUP [group2014];
    ALTER DATABASE [Baike] REMOVE FILEGROUP [group2015];
    ALTER DATABASE [Baike] REMOVE FILEGROUP [group2016];
    
    --重新建立聚集索引
    --删除分区索引后，重新建立聚集索引，这时再此查看数据表的存储位置，是否分区：Flase。
    create clustered index CT_fenquTable on fenquTable(createTime)
        with(drop_existing=on) --如果存在则删除
    on [primary];
    
    
    --删除最后一个分区文件\文件组
    DBCC SHRINKFILE ('web2013', EMPTYFILE);--最后再删
    ALTER DATABASE [Baike] REMOVE FILE [web2013];--最后再删
    
    --删除分区方案
    DROP PARTITION SCHEME [schemeFenqu]
    
    --删除分区函数
    DROP PARTITION FUNCTION [fenqu]
    GO
    
    ---删除分区方案后,才能删除成功?
    ALTER DATABASE [Baike] REMOVE FILEGROUP [group2013];--最最后再删
    
    
    
    --查看
    SELECT COUNT(*) FROM fenquTable   
    GO   
    SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]    
    FROM sys.indexes i    
    INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id    
    INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id]    
    WHERE o.name in( 'fenquTable','TestTab2');
    GO    
    DBCC showfilestats  
    GO
    
    --在查询Baike的文件组.
    sp_helpdb Baike 
    
    -- 查询该文件组[group2013]下的对象.
    select filegroup=s.groupname,
    filename=c.name,
    tablename=object_Name(i.ID),
    indexname=i.name,
    indextype=case when i.indid=0 then 'Heap' 
    when i.indid=1 then 'Clustered index' 
    when i.indid>1 then 'Nonclustered index' end 
    from sysfilegroups s, sysindexes i,sysfiles c
    where i.groupid=s.groupid and i.groupid=c.groupid
    --and s.groupname='group2013'
    and object_Name(i.ID)='fengquTable'