测试分区表，以及在现有表上新建分区方案，以及把分区表变为普通表测试代码！！

测试分区表，以及在现有表上新建分区方案，以及把分区表变为普通表测试代码！！

--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 FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATA	est01.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [test01]
GO
ALTER DATABASE [test] ADD FILE ( NAME = N'test02', FILENAME = N'D:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATA	est02.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [test02]
GO
ALTER DATABASE [test] ADD FILE ( NAME = N'test03', FILENAME = N'D:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATA	est03.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [test03]
GO
ALTER DATABASE [test] ADD FILE ( NAME = N'test04', FILENAME = N'D:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATA	est04.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [test04]
GO
ALTER DATABASE [test] ADD FILE ( NAME = N'test05', FILENAME = N'D:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATA	est05.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