sqlserver 分区表总结

一些结论：

1、分区字段不一定需要建立索引
2、分区字段可建索引：clustered 、noclustered
3、不论分区字段的索引方式，若重建为clustered且没有关联分区方案时，分区表就变成了非分区表
4、普通表转换为分区表，只要在该表创建一个clustered索引，并在该clustered索引上使用分区方案即可
5、分区表转换为普通表，分区字段新建clustered索引且不关联分区方案
6、分区表创建唯一性约束，必须包含分区列
7、创建分区方案时，必须保证文件组数量匹配分区函数的分区范围段，文件组名称可重复，也可使用ALL(即一个文件组)，则所有分区均落在这  个文件组中
8、分区函数和分区方案是面对数据库里的，不是整个实例的
9、分区表占用磁盘大，删除字段后仍不会变化，进行分区合并或转换为普通表，则会降低磁盘消耗

创建分区表步骤：

建立文件组(类似oracle表空间)->创建分区函数(确定数据分区范围)->创建分区方案，关联分区函数/文件组->建立表，关联分区方案

1.1、建立文件组的示例

alter database test1 add filegroup part1;
alter database test1 add filegroup part1000;
alter database test1 add filegroup part2000;
alter database test1 add filegroup part3000;
alter database test1 add filegroup part4000;

1.2、建立文件的示例，关联文件组

ALTER DATABASE test1 ADD FILE(NAME = test1part1,FILENAME = 'G:	est1part1.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP part1;
ALTER DATABASE test1 ADD FILE(NAME = test1part1000,FILENAME = 'G:	est1part1000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP part1000;
ALTER DATABASE test1 ADD FILE(NAME = test1part2000,FILENAME = 'G:	est1part2000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP part2000;
ALTER DATABASE test1 ADD FILE(NAME = test1part3000,FILENAME = 'G:	est1part3000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP part3000;
ALTER DATABASE test1 ADD FILE(NAME = test1part4000,FILENAME = 'G:	est1part4000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP part4000;

2、建立分区函数的示例，分区函数名为partfun1

-- VALUES ('1000','2000','3000','4000')表明，将把表分为5个区了，是从根据表字段的值的大小来分区，五个区分别是最小--1000,1000-2000,2000-3000,3000-4000,4000-最大

CREATE PARTITION FUNCTION partfun1 (int) AS RANGE LEFT FOR VALUES ('1000','2000','3000','4000')

3、建立方案的例子,关联分区函数partfun1，关联文件组

-- 建立在part1,part1000,part2000,part3000,part4000几个文件组上

CREATE PARTITION SCHEME partschema1 AS PARTITION partfun1 TO (part1,part1000,part2000,part3000,part4000);

-- 建立在part1、[PRIMARY]文件组上，把part1换成[PRIMARY]也没问题，这样就类似都建立在[PRIMARY]文件组上

CREATE PARTITION SCHEME partschema2 AS PARTITION partfun1 TO (part1,[PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY]);

-- 都建立在part1文件组上

CREATE PARTITION SCHEME partschema3 AS PARTITION partfun1 ALL TO (part1);

-- 都建立在[PRIMARY]文件组上

CREATE PARTITION SCHEME partschema4 AS PARTITION partfun1 ALL TO ([PRIMARY]);

4、建立分区表的示例

CREATE TABLE parttable1(
[ID] [int] NOT NULL,
[IDText] [nvarchar](max) NULL,
[Date] [datetime] NULL)
ON [partschema1](ID);

insert into parttable1 values (1,'1',getdate()-4);
insert into parttable1 values (1001,'1001',getdate()-3);
insert into parttable1 values (2001,'2001',getdate()-2);
insert into parttable1 values (3001,'3001',getdate()-1);
insert into parttable1 values (4001,'4001',getdate());

5、验证分区表的数据

-- 返回分区表所有行

SELECT * FROM parttable1;

-- 返回ID字段值为4的行属于哪个分区

SELECT distinct $PARTITION.[partfun1](4) FROM parttable1;

-- 返回第2个分区的所有行,ID就是分区字段ID

SELECT * FROM parttable1 where $PARTITION.[partfun1](ID)=2;

注意：不能因为SELECT * FROM parttable1 where $PARTITION.partfun1=2有结果就说明它是分区表，本文最后试验7该表是非分区表了，但是执行SELECT * FROM parttable1 where $PARTITION.partfun1=2还是有结果的

新增分区

1、为分区方案指定一个可以使用的文件组(新增分区方案的文件组)。

2、修改分区函数(新增分区函数的数据范围)

ALTER PARTITION SCHEME partschema1 NEXT USED [PRIMARY]

ALTER PARTITION FUNCTION partfun1() SPLIT RANGE ('4500')

select p.partition_number,p.rows from sys.indexes i inner join sys.partitions p on p.object_id = i.object_id and i.object_id = object_id('parttable2') order by 1

--第一条语句，如果分区方案使用的ALL TO ([PRIMARY])，则这条语句不用执行
--第二条语句新增一个分区，范围是4000-4500
--第三条语句验证新增分区是否存在，是否存在行数

删除合并分区

-- 就把1000-2000这个分区，删除了，合并成了1000-3000

ALTER PARTITION FUNCTION partfun1() MERGE RANGE ('2000')

-- 无法像oracle一样执行ALTER TABLE TABLENAME DROP PARTITION PARTITIONNAME;

删除分区表及对应的文件组

删除顺序为：删除分区表、删除分区方案、删除分区函数，最后删除文件组，删除完文件组后对应的文件也就删除了

分区表转换为普通表，普通表转换为分区表的示例

DROP TABLE parttable1;

CREATE TABLE parttable1(
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](16) NOT NULL,
	[Id2][int] NOT NULL
) ON partschema1(Id2);

insert into parttable1 values ('1',1);
insert into parttable1 values ('1001',1001);
insert into parttable1 values ('2001',2001);
insert into parttable1 values ('3001',3001);
insert into parttable1 values ('4001',4001);

1、在分区表上创建的唯一约束，必须包含分区列。

ALTER TABLE parttable1 ADD CONSTRAINT PK_prattable1_id PRIMARY KEY CLUSTERED ([ID] ASC)

报错Column 'Id2' is partitioning column of the index 'PK_prattable1_id'. Partition columns for a unique index must be a subset of the index key.

2、分区列id2新建clustered索引，parttable1还是分区表

create clustered index CI_prattable1_id2 on parttable1(id2);

3、分区列id2创建nonclustered索引，parttable1还是分区表

drop index CI_prattable1_id2 on parttable1;
create nonclustered index NCI_prattable1_id2 on parttable1(id2);

4、非分区列id列创建clustered索引，parttable1还是分区表，说明非分区列可以是cluster索引列

create clustered index CI_prattable1_id on parttable1(id);

5、分区列id2重建为nonclustered索引并且不使用分区方案，parttable1还是分区表

create nonclustered index NCI_prattable1_id2 on parttable1(id2) WITH (DROP_EXISTING = ON) ON [PRIMARY];

6、分区列id2重建为clustered索引不加ON条件，parttable1还是分区表

drop index CI_prattable1_id on parttable1;
drop index NCI_prattable1_id2 on parttable1;
create clustered index CI_prattable1_id2 on parttable1(id2);
create clustered index CI_prattable1_id2 on parttable1(id2) WITH (DROP_EXISTING = ON);

7、分区列id2重建为clustered索引加上ON条件但不使用分区方案，parttable1变成了非分区表

create clustered index CI_prattable1_id2 on parttable1(id2) WITH (DROP_EXISTING = ON) ON [PRIMARY];

8、分区列id2重建为clustered索引并且使用分区方案，parttable1变成了分区表

create clustered index CI_prattable1_id2 on parttable1(id2) WITH (DROP_EXISTING = ON) on partschema1(Id2);

9、删掉上面8的clustered索引后，parttable1还是分区表

drop index CI_prattable1_id2 on parttable1;

10、分区列id2新建为clustered索引并且不使用分区方案，parttable1变成了非分区表

create clustered index CI_prattable1_id2 on parttable1(id2) ON [PRIMARY];

11、删掉上面10的clustered索引后，parttable1还是非分区表

drop index CI_prattable1_id2 on parttable1;

12、分区列id2新建为nonclustered索引，虽然使用了分区方案，还是非分区表

create nonclustered index NCI_prattable1_id2 on parttable1(id2) on partschema1(Id2);

分区表转换为普通表，遇到分区字段是主键的情况下，则删除主键约束，再对原来主键的字段重建cluster索引或重建为主键，但是都不关联分区方案

ALTER TABLE Table_name DROP CONSTRAINT PK_NAME WITH (>
CREATE CLUSTERED INDEX PK_NAME ON Table_name(column)  WITH (ON [PRIMARY];

或

ALTER TABLE Table_name ADD CONSTRAINT PK_NAME PRIMARY KEY CLUSTERED(column) WITH (ON [PRIMARY];

普通表转换为分区表，要保留原来的主键的情况下，则删除主键约束，再创建主键但不设为聚集索引，再创建新的聚集索引，在该聚集索引中使用分区方案

ALTER TABLE Table_name DROP CONSTRAINT PK_NAME WITH (>
ALTER TABLE Table_name ADD CONSTRAINT PK_NAME PRIMARY KEY NONCLUSTERED(column) WITH (ON [PRIMARY];

--创建主键，但不设为聚集索引

CREATE CLUSTERED INDEX index_name ON Table_name(column) ON 分区方案(分区字段)

--创建一个新的聚集索引，在该聚集索引中使用分区方案

查询某张分区表的总行数和大小，比如表为crm.EmailLog

exec sp_spaceused 'crm.EmailLog';

查询某张分区表的信息，每个分区有多少行，比如表为crm.EmailLog

select convert(varchar(50), ps.name
) as partition_scheme,
p.partition_number,
convert(varchar(10), ds2.name
) as filegroup,
convert(varchar(19), isnull(v.value, ''), 120) as range_boundary,
str(p.rows, 9) as rows
from sys.indexes i
join sys.partition_schemes ps on i.data_space_id = ps.data_space_id
join sys.destination_data_spaces dds
on ps.data_space_id = dds.partition_scheme_id
join sys.data_spaces ds2 on dds.data_space_id = ds2.data_space_id
join sys.partitions p on dds.destination_id = p.partition_number
and p.object_id = i.object_id and p.index_id = i.index_id
join sys.partition_functions pf on ps.function_id = pf.function_id
LEFT JOIN sys.Partition_Range_values v on pf.function_id = v.function_id
and v.boundary_id = p.partition_number - pf.boundary_value_on_right
WHERE i.object_id = object_id('crm.EmailLog')
and i.index_id in (0, 1)
order by p.partition_number

查询分区函数

select * from sys.partition_functions

查看分区架构

select * from sys.partition_schemes