测试分区表，以及在现有表上新建分区方案，以及把分区表变为普通表测试代码！！
测试分区表，以及在现有表上新建分区方案，以及把分区表变为普通表测试代码！！
--1.新建表直接运用分区方案，没有聚焦索引，没有主键，也看不到分区索引的关联， -- 这时直接新建一个聚集索引加上 ON [PRIMARY] 此表不用合并分区就可以直接变成普通表 -- 不加 ON [PRIMARY] 的话还是聚集索引，并且对分区表也没有影响 --2.在现在有的表上添加分区方案，想变普通表，直接重建聚集索引加上 ON [PRIMARY] 此表不用合并分区就可以直接变成普通表 --3.变成普通表之后，想删除文件和文件组 -- 先删除分区方案再删除分区函数 -- 先删除文件再删除文件组 --创建测试数据库 CREATE DATABASE [test] --添加文件组 USE [master] GO ALTER DATABASE [test] ADD FILEGROUP [test01] GO ALTER DATABASE [test] ADD FILEGROUP [test02] GO ALTER DATABASE [test] ADD FILEGROUP [test03] GO ALTER DATABASE [test] ADD FILEGROUP [test04] GO ALTER DATABASE [test] ADD FILEGROUP [test05] GO --添加文件 USE [master] GO ALTER DATABASE [test] ADD FILE ( NAME = N'test01', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\test01.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [test01] GO ALTER DATABASE [test] ADD FILE ( NAME = N'test02', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\test02.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [test02] GO ALTER DATABASE [test] ADD FILE ( NAME = N'test03', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\test03.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [test03] GO ALTER DATABASE [test] ADD FILE ( NAME = N'test04', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\test04.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [test04] GO ALTER DATABASE [test] ADD FILE ( NAME = N'test05', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\test05.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [test05] GO --分区函数 USE [test] CREATE PARTITION FUNCTION partfunSale (datetime) AS RANGE RIGHT FOR VALUES ('20100101','20110101','20120101','20130101') --分区方案 USE [test] CREATE PARTITION SCHEME partschSale AS PARTITION partfunSale TO ( test01, test02, test03, test04, test05) --新建表，直接应用分区方案，也可表建成后，再添加分区方案 --此种方式创建的表没有任何索引，创建聚集索引之后，加 ON [PRIMARY] 就可以直接变成普通表 CREATE TABLE Sale( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](16) NOT NULL, [SaleTime][datetime] NOT NULL ) ON partschSale([SaleTime]) --想改为普通表时用 --新建聚集索引，，不用合并分区，直接变成普通表-----必须加 ON [PRIMARY] 就可以直接变成普通表！！！ CREATE CLUSTERED INDEX PK_Sale ON Sale([id]) --WITH ( DROP_EXISTING = ON) ON [PRIMARY] INSERT Sale ([Name],[SaleTime]) values ('张三','2009-1-1') insert Sale ([Name],[SaleTime]) values ('李四','2009-2-1') insert Sale ([Name],[SaleTime]) values ('王五','2009-3-1') insert Sale ([Name],[SaleTime]) values ('钱六','2010-4-1') insert Sale ([Name],[SaleTime]) values ('赵七','2010-5-1') insert Sale ([Name],[SaleTime]) values ('张三','2011-6-1') insert Sale ([Name],[SaleTime]) values ('李四','2011-7-1') insert Sale ([Name],[SaleTime]) values ('王五','2011-8-1') insert Sale ([Name],[SaleTime]) values ('钱六','2012-9-1') insert Sale ([Name],[SaleTime]) values ('赵七','2012-10-1') insert Sale ([Name],[SaleTime]) values ('张三','2012-11-1') insert Sale ([Name],[SaleTime]) values ('李四','2013-12-1') insert Sale ([Name],[SaleTime]) values ('王五','2014-12-1') select * from Sale select $PARTITION.partfunSale ('2010-10-1') select * from Sale where $PARTITION.partfunSale(SaleTime)=1 select * from Sale where $PARTITION.partfunSale(SaleTime)=2 select * from Sale where $PARTITION.partfunSale(SaleTime)=3 select * from Sale where $PARTITION.partfunSale(SaleTime)=4 select * from Sale where $PARTITION.partfunSale(SaleTime)=5 select $PARTITION.partfunSale(SaleTime) as 分区编号,count(id) as 记录数 from Sale group by $PARTITION.partfunSale(SaleTime) --统计所有分区表中的记录总数 select $PARTITION.partfunSale(SaleTime) as 分区编号,count(id) as 记录数 from Sale group by $PARTITION.partfunSale(SaleTime) --修改编号为1的记录，将时间改为2019年1月1日 update Sale set SaleTime='2019-1-1' where id=1 --重新统计所有分区表中的记录总数 select $PARTITION.partfunSale(SaleTime) as 分区编号,count(id) as 记录数 from Sale group by $PARTITION.partfunSale(SaleTime) --索引重建，不用合并分区，直接变成普通表-----必须加 ON [PRIMARY] 就可以直接变成普通表！！！ CREATE CLUSTERED INDEX PK_Sale ON Sale([id]) --WITH ( DROP_EXISTING = ON) ON [PRIMARY] --删除原来的数据表 drop table Sale --新建一个普通的数据表 CREATE TABLE Sale( [Id] [int] IDENTITY(1,1) NOT NULL, --自动增长 [Name] [varchar](16) NOT NULL, [SaleTime] [datetime] NOT NULL, CONSTRAINT [PK_Sale] PRIMARY KEY CLUSTERED --创建主键 ( [Id] ASC ) ) --插入一些记录 insert Sale ([Name],[SaleTime]) values ('张三','2009-1-1') insert Sale ([Name],[SaleTime]) values ('李四','2009-2-1') insert Sale ([Name],[SaleTime]) values ('王五','2009-3-1') insert Sale ([Name],[SaleTime]) values ('钱六','2010-4-1') insert Sale ([Name],[SaleTime]) values ('赵七','2010-5-1') insert Sale ([Name],[SaleTime]) values ('张三','2011-6-1') insert Sale ([Name],[SaleTime]) values ('李四','2011-7-1') insert Sale ([Name],[SaleTime]) values ('王五','2011-8-1') insert Sale ([Name],[SaleTime]) values ('钱六','2012-9-1') insert Sale ([Name],[SaleTime]) values ('赵七','2012-10-1') insert Sale ([Name],[SaleTime]) values ('张三','2012-11-1') insert Sale ([Name],[SaleTime]) values ('李四','2013-12-1') insert Sale ([Name],[SaleTime]) values ('王五','2014-12-1') --删掉主键 ALTER TABLE Sale DROP constraint PK_Sale --创建主键，但不设为聚集索引 ALTER TABLE Sale ADD CONSTRAINT PK_Sale PRIMARY KEY NONCLUSTERED ( [ID] ASC ) ON [PRIMARY] --创建一个新的聚集索引，在该聚集索引中使用分区方案 CREATE CLUSTERED INDEX CT_Sale ON Sale([SaleTime]) ON partschSale([SaleTime]) --统计所有分区表中的记录总数 select $PARTITION.partfunSale(SaleTime) as 分区编号,count(id) as 记录数 from Sale group by $PARTITION.partfunSale(SaleTime) ALTER PARTITION FUNCTION partfunSale() MERGE RANGE ('20100101') --统计所有分区表中的记录总数 select $PARTITION.partfunSale(SaleTime) as 分区编号,count(id) as 记录数 from Sale group by $PARTITION.partfunSale(SaleTime) --原来的分区函数是将2010-1-1之前的数据放在第1个分区表中，将2010-1-1至2011-1-1之间的数据放在第2个分区表中 --现在需要将2011-1-1之前的数据都放在第1个分区表中，也就是将第1个分区表和第2个分区表中的数据合并 --修改分区函数 ALTER PARTITION FUNCTION partfunSale() MERGE RANGE ('20100101') --统计所有分区表中的记录总数 select $PARTITION.partfunSale(SaleTime) as 分区编号,count(id) as 记录数 from Sale group by $PARTITION.partfunSale(SaleTime) --合并分区表 ALTER PARTITION FUNCTION partfunSale() MERGE RANGE ('20100101') ALTER PARTITION FUNCTION partfunSale() MERGE RANGE ('20110101') ALTER PARTITION FUNCTION partfunSale() MERGE RANGE ('20120101') ALTER PARTITION FUNCTION partfunSale() MERGE RANGE ('20130101') --重建聚集索引，加 ON [PRIMARY] 就可以直接变成普通表 CREATE CLUSTERED INDEX CT_Sale ON Sale([SaleTime]) WITH ( DROP_EXISTING = ON) ON [PRIMARY] --也可合并分区后，重建聚集索引，变成普通表！！ --删除文件组之前必须先删除分区方案！！ --删除分区方案 USE [test] DROP PARTITION SCHEME partschSale GO --删除分区函数,删除分区函数前必须先删除分区方案，要不会提示 USE [test] DROP PARTITION FUNCTION [partfunSale] GO --删除文件 USE [test] GO ALTER DATABASE [test] REMOVE FILE [test01] GO ALTER DATABASE [test] REMOVE FILE [test02] GO ALTER DATABASE [test] REMOVE FILE [test03] GO ALTER DATABASE [test] REMOVE FILE [test04] GO ALTER DATABASE [test] REMOVE FILE [test05] GO --删除文件组 USE [test] GO ALTER DATABASE [test] REMOVE FILEGROUP [test05] GO ALTER DATABASE [test] REMOVE FILEGROUP [test04] GO ALTER DATABASE [test] REMOVE FILEGROUP [test03] GO ALTER DATABASE [test] REMOVE FILEGROUP [test02] GO ALTER DATABASE [test] REMOVE FILEGROUP [test01] GO
