SQL Server 分区表的一些操作

use fenqutest

--------------------
-- 准备分区用的文件组、文件、分区函数、分区方案

--添加文件分组
ALTER DATABASE fenqutest ADD FILEGROUP [test2010]
ALTER DATABASE fenqutest ADD FILEGROUP [test2011]
ALTER DATABASE fenqutest ADD FILEGROUP [test2012]
ALTER DATABASE fenqutest ADD FILEGROUP [test2013]

--ALTER DATABASE fenqutest ADD FILEGROUP [test]

--添加物理文件
ALTER DATABASE fenqutest ADD FILE
(NAME = N'test2010',FILENAME = N'D:\sqlserver test\test2010.ndf',SIZE = 1MB,MAXSIZE = 10MB,FILEGROWTH = 1MB)
TO FILEGROUP [test2010]
--
ALTER DATABASE fenqutest ADD FILE
(NAME = N'test2011',FILENAME = N'D:\sqlserver test\test2011.ndf',SIZE = 1MB,MAXSIZE = 10MB,FILEGROWTH = 1MB)
TO FILEGROUP [test2011]
--
ALTER DATABASE fenqutest ADD FILE
(NAME = N'test2012',FILENAME = N'D:\sqlserver test\test2012.ndf',SIZE = 1MB,MAXSIZE = 10MB,FILEGROWTH = 1MB)
TO FILEGROUP [test2012]
--
ALTER DATABASE fenqutest ADD FILE
(NAME = N'test2013',FILENAME = N'D:\sqlserver test\test2013.ndf',SIZE = 1MB,MAXSIZE = 10MB,FILEGROWTH = 1MB)
TO FILEGROUP [test2013]
go

-- 创建分区函数
create partition function fnFenQu(datetime)
as
range right for values (
'2011-01-01',
'2012-01-01',
'2013-01-01')
go
-- right，2010-01-01(不含)前为一组，2010-01-01(含)~2010-12-31一组，2011-01-01(含)~2011-12-31一组，2012-01-01之后一组

-- 创建分区方案，不能使用一个文件组(含多个文件)：分区函数生成的分区多于方案中提到的文件组。
create partition scheme FenQu_Orders
as
partition fnFenQu
to (test2010, test2011, test2012, test2013)
go

--------------------
-- 直接创建分区表

-- 创建分区表
create table dbo.OrdersTest(
   OrderID     int          not null,
   CustomerID  varchar(10)  not null,
   EmployeeID  int          not null,
   OrderDate   datetime     not null
)
on FenQu_Orders(OrderDate)
go

-- 创建聚集分区索引
create clustered index IXC_OrdersTest on dbo.OrdersTest(OrderDate)
go

--清空表数据
truncate table OrdersTest
--插入数据
INSERT INTO [fenqutest].[dbo].OrdersTest VALUES (1 ,'aaaa',11 ,'2010-01-15 12:20:23')
INSERT INTO [fenqutest].[dbo].OrdersTest VALUES (2 ,'bbbb',21 ,'2010-05-10 12:20:23')
INSERT INTO [fenqutest].[dbo].OrdersTest VALUES (3 ,'cccc',31 ,'2011-10-10 12:20:23')
INSERT INTO [fenqutest].[dbo].OrdersTest VALUES (4 ,'dddd',41 ,'2012-10-10 12:20:23')
INSERT INTO [fenqutest].[dbo].OrdersTest VALUES (5 ,'eeee',51 ,'2013-10-10 12:20:23')
INSERT INTO [fenqutest].[dbo].OrdersTest VALUES (6 ,'ffff',61 ,'2010-10-10 12:20:23')
INSERT INTO [fenqutest].[dbo].OrdersTest VALUES (7 ,'gggg',71 ,'2015-10-10 12:20:23')

--查询数据
select from OrdersTest

--查看每个分区的数据分布情况，分区编号、记录数、(分区字段)最小值、(分区字段)最大值
--这个查看方法只是关联查询(表和分区函数)，并不能保证是分区成功了
SELECT
partition = $partition.fnFenQu(OrderDate),
rows = count(),
minval = min(OrderDate),
maxval = max(OrderDate)
FROM dbo.OrdersTest
GROUP BY $partition.fnFenQu(OrderDate)
ORDER BY partition

--------------------

-- 将普通表转换成分区表

-- 创建普通表
create table dbo.OrdersTest(
  OrderID     int          not null,
  CustomerID  varchar(10)  not null,
  EmployeeID  int          not null,
  OrderDate   datetime     not null,
  CONSTRAINT [PK_OrdersTest_OrderID] PRIMARY KEY CLUSTERED ( --创建主键  
    [OrderID] ASC  
  )  
)
--插入数据
INSERT INTO [fenqutest].[dbo].OrdersTest VALUES (1 ,'aaaa',11 ,'2010-01-15 12:20:23')
INSERT INTO [fenqutest].[dbo].OrdersTest VALUES (2 ,'bbbb',21 ,'2010-05-10 12:20:23')
INSERT INTO [fenqutest].[dbo].OrdersTest VALUES (3 ,'cccc',31 ,'2011-10-10 12:20:23')
INSERT INTO [fenqutest].[dbo].OrdersTest VALUES (4 ,'dddd',41 ,'2012-10-10 12:20:23')
INSERT INTO [fenqutest].[dbo].OrdersTest VALUES (5 ,'eeee',51 ,'2013-10-10 12:20:23')
INSERT INTO [fenqutest].[dbo].OrdersTest VALUES (6 ,'ffff',61 ,'2010-10-10 12:20:23')
INSERT INTO [fenqutest].[dbo].OrdersTest VALUES (7 ,'gggg',71 ,'2015-10-10 12:20:23')
--查询数据
select from [fenqutest].[dbo].[OrdersTest]

--开始转换

--删掉主键
ALTER TABLE OrdersTest DROP CONSTRAINT PK_OrdersTest_OrderID
--重新创建主键，但不设为聚集索引
ALTER TABLE OrdersTest ADD CONSTRAINT PK_OrdersTest_OrderID PRIMARY KEY NONCLUSTERED (  
    [OrderID] ASC  
) ON [PRIMARY]
--创建一个新的聚集索引，在该聚集索引中使用分区方案(分区方案的文件组有文件后才能引用分区方案)
CREATE CLUSTERED INDEX IXC_OrdersTest ON dbo.OrdersTest([OrderDate])
ON FenQu_Orders([OrderDate])

--查看每个分区的数据分布情况，分区编号、记录数、(分区字段)最小值、(分区字段)最大值
SELECT
partition = $partition.fnFenQu(OrderDate),
rows = count(),
minval = min(OrderDate),
maxval = max(OrderDate)
FROM dbo.OrdersTest
GROUP BY $partition.fnFenQu(OrderDate)
ORDER BY partition

--------------------

-----合并表空间-----
-- INSERT INTO [fenqutest].[dbo].OrdersTest VALUES (1111 ,'asdgsfd',113241 ,'2009-01-15 12:20:23')
-- 合并(删除)分区函数中的临界值，以此合并分区，数据会放在前一个空间里，如1、2，2的合进1里。
ALTER PARTITION FUNCTION fnFenQu()
  MERGE RANGE ('2011-01-01')
 
--------------------

-- 对分区表的其它操作

-----添加表空间-----
-- ALTER DATABASE fenqutest ADD FILEGROUP [test2014]
-- ALTER DATABASE fenqutest ADD FILE
-- (NAME = N'test2014',FILENAME = N'D:\sqlserver test\test2014.ndf',SIZE = 1MB,MAXSIZE = 10MB,FILEGROWTH = 1MB)
-- TO FILEGROUP [test2014]
-- 分区方案添加下一个文件组，要先准备好文件组(及其文件)
ALTER PARTITION SCHEME FenQu_Orders
  NEXT USED [test2014]
-- 分区函数分割(添加)临界值
ALTER PARTITION FUNCTION fnFenQu()
  SPLIT RANGE ('2014-01-01')

-- 改变分区方案和分区函数后，通过查看它们的源码，发现它们的源码并没有改变。
-- 但通过上面的查询我们发现，合并和添加分区的效果已经产生了了
-- 以上操作使用 SQL Server 2012(数据库) - Toad for SQL Server(查询分析器)


--------------------
--------------------

-- 删除表
DROP TABLE [fenqutest].[dbo].[OrdersTest];
-- 删除分区方案，删除分区表(引用关系？)后
DROP PARTITION SCHEME [FenQu_Orders];
-- 删除分区函数，删除分区方案后
DROP PARTITION FUNCTION [fnFenQu];

-- 删除文件，删除表数据后
ALTER DATABASE fenqutest REMOVE FILE [test2010]
ALTER DATABASE fenqutest REMOVE FILE [test2011]
ALTER DATABASE fenqutest REMOVE FILE [test2012]
ALTER DATABASE fenqutest REMOVE FILE [test2013]

-- 删除文件组，删除分区方案及(物理)文件后
ALTER DATABASE fenqutest REMOVE FILEGROUP [test2010]
ALTER DATABASE fenqutest REMOVE FILEGROUP [test2011]
ALTER DATABASE fenqutest REMOVE FILEGROUP [test2012]
ALTER DATABASE fenqutest REMOVE FILEGROUP [test2013]

--ALTER DATABASE fenqutest REMOVE FILEGROUP [test]