sql server 分区表的一些操作-白红宇

sql server 分区表的一些操作

阅读量：7218 次

发布时间：2019-06-29

本文共 6577 字，大约阅读时间需要 21 分钟。

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]

-- 创建分区函数

create partition function fnFenQu(datetime)

range right for values (

'2011-01-01',

'2012-01-01',

'2013-01-01')

-- right，2010-01-01(不含)前为一组，2010-01-01(含)~2010-12-31一组，2011-01-01(含)~2011-12-31一组，2012-01-01之后一组

-- 创建分区方案，不能使用一个文件组(含多个文件)：分区函数生成的分区多于方案中提到的文件组。

create partition scheme FenQu_Orders

partition fnFenQu

to (test2010, test2011, test2012, test2013)

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

-- 直接创建分区表

-- 创建分区表

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)

-- 创建聚集分区索引

create clustered index IXC_OrdersTest on dbo.OrdersTest(OrderDate)

--清空表数据

truncate table OrdersTest

--插入数据

INSERT INTO [fenqutest].[dbo].[OrdersTest]([OrderID] ,[CustomerID] ,[EmployeeID] ,[OrderDate]) VALUES (1 ,'aaaa',11 ,'2010-01-15 12:20:23')

INSERT INTO [fenqutest].[dbo].[OrdersTest]([OrderID] ,[CustomerID] ,[EmployeeID] ,[OrderDate]) VALUES (2 ,'bbbb',21 ,'2010-05-10 12:20:23')

INSERT INTO [fenqutest].[dbo].[OrdersTest]([OrderID] ,[CustomerID] ,[EmployeeID] ,[OrderDate]) VALUES (3 ,'cccc',31 ,'2011-10-10 12:20:23')

INSERT INTO [fenqutest].[dbo].[OrdersTest]([OrderID] ,[CustomerID] ,[EmployeeID] ,[OrderDate]) VALUES (4 ,'dddd',41 ,'2012-10-10 12:20:23')

INSERT INTO [fenqutest].[dbo].[OrdersTest]([OrderID] ,[CustomerID] ,[EmployeeID] ,[OrderDate]) VALUES (5 ,'eeee',51 ,'2013-10-10 12:20:23')

INSERT INTO [fenqutest].[dbo].[OrdersTest]([OrderID] ,[CustomerID] ,[EmployeeID] ,[OrderDate]) VALUES (6 ,'ffff',61 ,'2010-10-10 12:20:23')

INSERT INTO [fenqutest].[dbo].[OrdersTest]([OrderID] ,[CustomerID] ,[EmployeeID] ,[OrderDate]) 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]([OrderID] ,[CustomerID] ,[EmployeeID] ,[OrderDate]) VALUES (1 ,'aaaa',11 ,'2010-01-15 12:20:23')

INSERT INTO [fenqutest].[dbo].[OrdersTest]([OrderID] ,[CustomerID] ,[EmployeeID] ,[OrderDate]) VALUES (2 ,'bbbb',21 ,'2010-05-10 12:20:23')

INSERT INTO [fenqutest].[dbo].[OrdersTest]([OrderID] ,[CustomerID] ,[EmployeeID] ,[OrderDate]) VALUES (3 ,'cccc',31 ,'2011-10-10 12:20:23')

INSERT INTO [fenqutest].[dbo].[OrdersTest]([OrderID] ,[CustomerID] ,[EmployeeID] ,[OrderDate]) VALUES (4 ,'dddd',41 ,'2012-10-10 12:20:23')

INSERT INTO [fenqutest].[dbo].[OrdersTest]([OrderID] ,[CustomerID] ,[EmployeeID] ,[OrderDate]) VALUES (5 ,'eeee',51 ,'2013-10-10 12:20:23')

INSERT INTO [fenqutest].[dbo].[OrdersTest]([OrderID] ,[CustomerID] ,[EmployeeID] ,[OrderDate]) VALUES (6 ,'ffff',61 ,'2010-10-10 12:20:23')

INSERT INTO [fenqutest].[dbo].[OrdersTest]([OrderID] ,[CustomerID] ,[EmployeeID] ,[OrderDate]) 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]([OrderID] ,[CustomerID] ,[EmployeeID] ,[OrderDate]) 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]

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

转载于:https://my.oschina.net/animalong/blog/193799

你可能感兴趣的文章

从输入 URL 到页面加载完成的过程中都发生了什么事情？

查看>>

实例讲解JQuery中this和$(this)区别

面试题编程题05-python 有一个无序数组，如何获取第K 大的数，说下思路，实现后的时间复杂度？...

2.Web中使用iReport 整合----------创建html格式的

查看>>

异常备忘：java.lang.UnsupportedClassVersionError: Bad version number in .class file

查看>>

最全三大框架整合（使用映射）——applicationContext.xml里面的配置

查看>>

初步理解Java的三大特性——封装、继承和多态

查看>>

知识点积累(一)

查看>>