SqlServer表分区

一、创建普通表并插入数据准备测试环境--创建普通表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)GOCREATE 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_fenquTableprimary 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 SchemeFenqunext 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 SchemeFenqunext 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 cwhere i.groupid=s.groupid and i.groupid=c.groupid--and s.groupname='group2013'and object_Name(i.ID)='fengquTable'