SqlServer按字母分区表

蔚落 2022-07-15 10:44 208阅读 0赞

SqlServer按字母分区
    
    --创建普通表
    CREATE TABLE [dbo].[emailTable](
        [id] [int] IDENTITY(1,1) NOT NULL,
        [email] [nvarchar](50) NULL,
        [createTime] [datetime] NULL
        );
    
    ALTER TABLE [dbo].[emailTable] ADD  CONSTRAINT [PK_emailTable] PRIMARY KEY NONCLUSTERED 
    (
        [id] ASC
    )
    GO
    CREATE CLUSTERED INDEX [CT_emailTable] ON [dbo].[emailTable]
    (
        [email] ASC
    )
    GO
    --插入测试数据
    insert into emailTable(email,createTime) values ('bile@163.com','2010-01-01');
    insert into emailTable(email,createTime) values ('b','2010-01-01');
    insert into emailTable(email,createTime) values ('aliph@163.com','2011-01-01');
    insert into emailTable(email,createTime) values ('aq233s@163.com','2012-01-01');
    insert into emailTable(email,createTime) values ('cillli@163.com','2013-01-01');
    insert into emailTable(email,createTime) values ('afdsf@116.com','2013-10-01');
    insert into emailTable(email,createTime) values ('dd@163.com','2014-01-01');
    insert into emailTable(email,createTime) values ('Aaaaa@163.com','2015-01-01');
    insert into emailTable(email,createTime) values ('01b@163.com','2015-10-10');
    insert into emailTable(email,createTime) values ('中国@163.com','2015-10-10');
    
    
    二、准备文件组、文件、分区函数、分区方案
    --添加文件组
    alter database Baike add filegroup group0;
    alter database Baike add filegroup groupa;
    alter database Baike add filegroup groupb;
    alter database Baike add filegroup groupc;
    --添加文件
    
    alter database Baike 
    add file(name='email0',filename='D:\data\email0.ndf',size=5mb,filegrowth=5mb)
    to filegroup group0;
    
    alter database Baike 
    add file(name='emaila',filename='D:\data\emaila.ndf',size=5mb,filegrowth=5mb)
    to filegroup groupa;
    
    alter database Baike 
    add file(name='emailb',filename='D:\data\emailb.ndf',size=5mb,filegrowth=5mb)
    to filegroup groupb;
    
    alter database Baike 
    add file(name='emailc',filename='D:\data\emailc.ndf',size=5mb,filegrowth=5mb)
    to filegroup groupc;
    
    ---分区函数
    create partition function fenqu(nvarchar(50)) --分区函数名
    as range right  --right分区方式 边界值去左表还是右表
    for values ('a','b','c') --按这些值来分区 
    --groupa : a 之前的
    --groupb : a 到 b的
    --groupc : b 之后的
    
    
    --创建分区方案
    create partition scheme SchemeFenqu --分区方案名
    as partition fenqu    --之前创建的分区函数
    to(group0,groupa,groupb,groupc); --之前创建的文件组
    
    
    三、将普通表转换为分区表
    --删除主键，自动同时删除索引
    alter table emailTable drop constraint PK_emailTable 
    
    --创建主键，但不创建聚集索引
    alter table emailTable add constraint PK_emailTable
    primary key nonclustered --非聚集
    (id asc) on [primary];
    
    --然后在createTime字段上创建一个聚集索引
    create clustered index CT_emailTable on emailTable(email)
    with(drop_existing=on) --如果存在则删除
    on schemeFenqu(email); --并调用分区方案
    
    --然后再查询分区，发现数据保留情况下，已经将数据按规则进行分区了
    select $partition.fenqu(email) as 分区,count(id) as 数量
    from emailTable group by $partition.fenqu(email);
    
    --查看分区表明细
    select * from emailTable where $partition.fenqu(email)=1;
    select * from emailTable where $partition.fenqu(email)=2;
    select * from emailTable where $partition.fenqu(email)=3;
    select * from emailTable where $partition.fenqu(email)=4;
    
    
    四、分区表转普通表
    --修改分区函数 将边界值都删除
    --这样虽然只有一个分区了，但是查看数据表存储位置，是否进行分区：True，分区数1.
    alter partition function fenqu()merge range('a');
    alter partition function fenqu()merge range('b');
    alter partition function fenqu()merge range('c');
    
    --查询
    select $partition.fenqu(email) as 分区,count(id) as 数量
    from emailTable group by $partition.fenqu(email);
    
    DBCC showfilestats  
    GO
    
    
    --清空文件
    --DBCC SHRINKFILE ('email0', EMPTYFILE);--重新建立聚集索引后再删
    DBCC SHRINKFILE ('emaila', EMPTYFILE);
    DBCC SHRINKFILE ('emailb', EMPTYFILE);
    DBCC SHRINKFILE ('emailc', EMPTYFILE);
    
    
    --删除文件
    --ALTER DATABASE [Baike] REMOVE FILE [email0];--重新建立聚集索引后再删
    ALTER DATABASE [Baike] REMOVE FILE [emaila];
    ALTER DATABASE [Baike] REMOVE FILE [emailb];
    ALTER DATABASE [Baike] REMOVE FILE [emailc];
    
    --删除文件组
    --ALTER DATABASE [Baike] REMOVE FILEGROUP [group0];--重新建立聚集索引后再删
    ALTER DATABASE [Baike] REMOVE FILEGROUP [groupa];
    ALTER DATABASE [Baike] REMOVE FILEGROUP [groupb];
    ALTER DATABASE [Baike] REMOVE FILEGROUP [groupc];
    
    --重新建立聚集索引
    --删除分区索引后，重新建立聚集索引，这时再此查看数据表的存储位置，是否分区：Flase。
    create clustered index CT_emailTable on emailTable(email)
        with(drop_existing=on) --如果存在则删除
    on [primary];
    
    
    --删除最后一个分区文件\文件组
    DBCC SHRINKFILE ('email0', EMPTYFILE);--最后再删
    ALTER DATABASE [Baike] REMOVE FILE [email0];--最后再删
    
    --删除分区方案
    DROP PARTITION SCHEME [schemeFenqu]
    
    --删除分区函数
    DROP PARTITION FUNCTION [fenqu]
    GO
    
    ---删除分区方案后,才能删除成功?
    ALTER DATABASE [Baike] REMOVE FILEGROUP [group0];--最最后再删