MSSQL Server 创建分区表

分区表的主要目的:
是为了改善大型表以及具有各种访问模式的表的可伸缩性和可管理性。


分区一方面可以将数据分为更小、更易管理的部分，为提高性能起到一定的作用；
另一方面，对于如果具有多个CPU的系统，分区可以是对表的操作通过并行的方式进行。


2. 创建分区表或分区索引的步骤
可以分为以下步骤：
1. 确定分区列和分区数


2. 确定是否使用多个文件组


3. 创建分区函数


4. 创建分区架构


5. 创建分区表


6. 创建分区索引




第一、创建分区表的第一步，先创建数据库文件组，但这一步可以省略，因为你可以直接使用PRIMARY文件。
为了方便管理，还是可以先创建几个文件组，这样可以将不同的小表放在不同的文件组里，既便于理解又可以提高运行速度。
创建文件组的方法很简单，打开SQL Server Management Studio，找到分区表所在数据库，
右键单击，在弹出的菜单里选择“属性”。然后选择“文件组”选项，再单击下面的“添加”按钮


第二，创建了文件组之后，还要再创建几个数据库文件。
因为分区的小表必须要放在硬盘上的文件里. 
再说,文件组中没有文件，文件组就是多余的了.
选择“文件”选项，然后添加几个文件。在添加文件的时候要注意：
1、不要忘记将不同的文件放在文件组中。当然一个文件组中也可以包含多个不同的文件。
2、如果可以的话，将不同的文件放在不同的硬盘分区里，最好是放在不同的独立硬盘里。
要知道IQ的速度往往是影响SQL Server运行速度的重要条件之一。
将不同的文件放在不同的硬盘上，可以加快SQL Server的运行速度。




-- 第三、创建分区函数, 
-- 创建分区函数的目的是告诉SQL Server以什么方式对分区表进行分区。
-- 若分区函数存在则先drop掉
-- 创建分区函数给后面的分区方案使用，分区函数就是指定一个范围确定在某个值为什么的时候放在那个分区上
IF  EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'partf_hc')
DROP PARTITION FUNCTION [partf_hc]


CREATE PARTITION FUNCTION partf_hc (datetime)
AS RANGE RIGHT FOR VALUES ('2015-01-01', '2016-01-01')
--1、CREATE PARTITION FUNCTION意思是创建一个分区函数。
--2、partfunSale为分区函数名称。
--3、AS RANGE RIGHT为设置分区范围的方式为Right，也就是右置方式。
--4、FOR VALUES ('2015-01-01', '2016-01-01')为按这几个值来分区。




-- 第四、创建分区架构.
-- 分区方案的作用是将分区函数生成的分区映射到文件组中去。告诉SQL Server将已分区的数据放在哪个文件组中。
-- 看分区方案是否存在，若存在先drop掉
IF  EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'parts_hc')
DROP PARTITION SCHEME [parts_hc]
GO
--创建分区方案，分区方案需要指定一个分区函数，并指定在分区函数中分的区需要放在哪一个文件组上
create partition scheme parts_hc 
AS 
PARTITION [partf_hc] TO (HC2014,HC2015,HC2016)
GO
-- 1、CREATE PARTITION SCHEME意思是创建一个分区方案。
-- 2、parts_hc 为分区方案名称。
-- 3、AS PARTITION partf_hc 说明该分区方案所使用的分区函数为partf_hc。
-- 4、TO后面的内容是指partf_hc分区函数划分出来的数据对应存放的文件组。




--创建分区表
if object_id('hitcount','U') is not null
drop table hitcount;
go
create table hitcount
(
[hitdate] [datetime] NOT NULL,
[songid] [nvarchar](20) NOT NULL,
[hitcount] [int] NULL
) on parts_hc(hitdate) --在此处指定该表要使用的分区方案，并将指定分区依据列
go
-- 1、跟创建普通数据表差不多,需要注意的是不能再创建聚集索引, 
-- 因为聚集索引可以将记录在物理上顺序存储的，
-- 而分区表是将数据分别存储在不同的表中，这两个概念是冲突的
-- 2、on parts_hc说明使用名为 parts_hc 的分区方案。
-- 3、(hityear)表示用于分区条件的字段是hityear。




-- 插入测试数据
insert into hitcount values ('2013-01-01 00:00:00', '001102', 1); -- 这条数据在1分区上
insert into hitcount values ('2014-01-01 00:00:00', '001102', 1); -- 这条数据在1分区上
insert into hitcount values ('2015-01-01 00:00:00', '001102', 1); -- 这条数据在2分区上
insert into hitcount values ('2016-01-01 00:00:00', '001102', 1); -- 这条数据在3分区上
insert into hitcount values ('2017-01-01 00:00:00', '001102', 1); -- 这条数据在3分区上


-- 查看全部纪录
select * from hitcount
-- 查看纪录分部情况
select *, $partition.partf_hc(hitdate) as 分区 from hitcount