sqlserver分区

SQL Server 大数据管理——表分区

--db important
use MyTest

一.    创建分区文件组/文件

--filegroup
alter database MyTest add filegroup fg2015
alter database MyTest add filegroup fg2016
alter database MyTest add filegroup fg2017
alter database MyTest add filegroup fg2018

--file
alter database MyTest add file
(name='MyTest_2015file',filename='F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MyTest_2015.ndf',
size=1mb, filegrowth=1mb) to filegroup fg2015;
alter database MyTest add file
(name='MyTest_2016file',filename='F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MyTest_2016.ndf',
size=1mb, filegrowth=1mb) to filegroup fg2016;
alter database MyTest add file
(name='MyTest_2017file',filename='F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MyTest_2017.ndf',
size=1mb, filegrowth=1mb) to filegroup fg2017;
alter database MyTest add file
(name='MyTest_2018file',filename='F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MyTest_2018.ndf',
size=1mb, filegrowth=1mb) to filegroup fg2018;

二.    创建分区函数

--partition function
create partition function f_date(datetime)
as range right for values('2016-01-01','2017-01-01','2018-01-01');

注意：

1.        F_TestDate 为分区函数名，分区的字段是datetime类型

2.        Right 表示该分区包含右边界值，上面分区函数会把数据分为

• 小于2016.1.1
• 大于等于2016.1.1 且小于2017.1.1
• 大于等于2017.1.1 且小于2018.1.1
• 大于等于2018.1.1

四个分区，若把right换为left，则分区变为

• 小于等于2016.1.1
• 大于2016.1.1 且小于等于2017.1.1
• 大于2017.1.1 且小于等于2018.1.1
• 大于2018.1.1

三.    创建分区方案

--partition scheme
create partition scheme s_date
as partition f_date to (fg2015, fg2016, fg2017, fg2018);

注意：

1.        分区方案是建立在分区函数的基础上的，所以先建立分区函数，再建立分区方案

2.        分区个数比分区边界值多1

3.        本分区方案每个分区建在一个文件组上，当然也可以把所有分区建立在一个文件组上

--partition scheme
create partition scheme s_date
as partition f_date to ([PRIMARY]);

四.    创建分区表

--table
create table tradelog
(
	ID int,
	productID int,
	tradedate datetime
) on s_date(tradedate)

4.2  对已有表分区

若表上没有聚集索引，可以通过创建聚集索引，对表进行分区

CREATE CLUSTERED INDEX [CLI_tn_TestDate] ON [dbo].[tradelog_noClusterIndex]
(
	[tradedate]
) ON [s_TestDate]([tradedate])
 
--如果不需要聚集索引,删除聚集索引
DROP INDEX [CLI_tn_TestDate] ON [dbo].[tradelog_noClusterIndex]

若表上已有聚集索引，删除聚集索引，再通过上面脚本重建聚集索引。或者通过WITH（DROP_EXISTING=ON）重建聚集索引，脚本如下：

CREATE CLUSTERED INDEX [CLI_tn_TestDate] ON [dbo].[tradelog_noClusterIndex]
(
	[tradedate]
)WITH (DROP_EXISTING = ON) ON [s_TestDate]([tradedate])

五.    增加分区

增加分区的方法是将某个现有的分区“拆分”为两个分区并重新定义新分区的边界。

;with cte as
	(select 
		object_id
		,OBJECT_NAME(i.object_id) tableName
		,i.index_id
		,dds.partition_scheme_id
		,dds.destination_id as partition_number
		,fg.groupid
		,fg.groupname
		,f.fileid
		,f.name
		,f.filename
		--,p.partition_id
		--,p.rows
	 from sys.destination_data_spaces dds,sys.indexes i,sys.sysfilegroups fg,sys.sysfiles f
	 where dds.partition_scheme_id=i.data_space_id
		and dds.data_space_id=fg.groupid
		and fg.groupid=f.groupid
		)
,cte1 as(
	select
		ps.data_space_id as partition_scheme_id
		,ps.name partiton_schemes_name
		,pf.name partition_function_name
		,pf.function_id
		--,prv.value AS BoundaryValue
	from sys.partition_schemes ps ,sys.partition_functions pf
	where ps.function_id=pf.function_id
		--and pf.function_id=prv.function_id
)
select cte.tableName,cte.groupname,cte.name,cte.filename
	,cte.partition_number,cte1.partiton_schemes_name,cte1.partition_function_name,p.rows
	,prv.boundary_id,prv.value BoundaryValue
from cte
inner join cte1	on cte.partition_scheme_id=cte1 .partition_scheme_id
left join sys.partition_range_values prv on cte1.function_id=prv.function_id and cte.partition_number=prv.boundary_id
left join sys.partitions  p on cte.object_id=p.object_id and cte.index_id=p.index_id and cte.partition_number=p.partition_number
 
where 
	 cte.object_id=OBJECT_ID('dbo.tradelog','U')

现在增加一个分区，将2019以后的数据分开，或者说将原4分区以2019年1月1日为分区边界拆分为两个分区，具体脚本如下：

--split
alter database MyTest add filegroup fg2019
alter database MyTest add file
(name='MyTest_2019file',filename='F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MyTest_2019.ndf',
size=1mb, filegrowth=1mb) to filegroup fg2019;
alter partition scheme s_date next used fg2019
alter partition function f_date() split range('2019-01-01')

六.    合并分区

alter partition function f_date() merge range('2019-01-01')

分区合并的数据移动方向刚好和增加分区的方向相反，分区合并后，将合并分界点的后一个分区数据移动到前一个分区的文件中。这个结论在数据自动归档中将极为有用，因为数据归档最后一步是将合并后的空文件、文件组回收，这样就可以确定回收的文件名

七.    分区数据移到普通表

create table tradelog_partition1
(
	ID int,
	productID int,
	tradedate datetime
) on test2015
alter table tradelog switch partition 1 to tradelog_partition1

把分区表的某个分区数据转移到普通表，要求

1.      普通表必须和对应的分区在同一个文件组下

2.      普通表和分区表结构相同，包括字段、数据类型、数据长度、索引等

分区表上在tradedate上有聚集索引，但普通表tradelog_partition1上没有建聚集索引，执行上述脚本就会报如下错误：
 

八.    普通表数据移到某一分区

alter table tradelog_partition1 switch to tradelog partition 1

在tradelog_partition1的tradedate上创建聚集索引，重新执行上面的脚本，又报了如下错误

这是因为分区1上有CHECK日期要在2014到2016之间，而tradelog_partition1上没有这个检查，所以，在表上加上如下检查：

ALTER TABLE dbo.tradelog_partition1
		ADD CONSTRAINT TradeDate_Switch_CHECK CHECK 
			(TradeDate >= CONVERT(DATE,'2014-01-01') AND TradeDate < CONVERT(DATE,'2016-01-01')
            AND TradeDate IS NOT NULL);
GO

再执行移动数据，数据又重新移回到分区1中。

九.    删除文件/文件组

--delete from scatterintensity   inoperative
--1 drop table 
drop table scatterintensity 
select * from select * from sys.database_files;
alter database test remove file test_file;
--2 drop partition scheme
select * from sys.partition_schemes;
drop partition scheme s_model;
--3 drop partition fun 
select * from sys.partition_functions;
drop partition function f_model;
--4 drop filegroup
select * from sys.filegroups;
alter database test remove filegroup fgmodel;

注:在合并分区后,可以直接remove file/filegroup

转载:SQL Server 大数据管理——表分区_三空道人的博客-CSDN博客