官方文档
创建分区表的大致步骤 1、建立文件组(类似oracle的表空间),当然不建立也行,把所有分区都放一个文件组内也可以 2、建立分区函数,数据按什么范围分配 3、建立分区方案,关联分区函数,也会关联文件组,分区函数把数据分了几个范围,就需要关联几个文件组,当然也可以把这几个分区范围都放入到同一个文件组 4、建立表,关联分区方案
遇到的一个Bug 直接右键表导出表结构时导不出分区信息,只能右键数据库--任务--生成脚本才能导出表的分区信息
分区表的一些结论: 1、分区字段不一定需要建立索引 2、分区字段可以创建为clustered索引或noclustered索引 3、分区字段不管是clustered索引还是noclustered索引,重建为clustered索引且没有关联分区方案时,分区表就变成了非分区表 4、普通表转换为分区表,只要在该表创建一个clustered索引,并在clustered索引上使用分区方案即可。比如非分区表的字段1创建clustered索引且关联分区方案时,该表转换为分区表,且分区字段为字段1。 5、分区表转换为普通表,如果有分区字段有索引则重建分区字段为clustered索引且不关联分区方案即可,分区字段没有索引的话则分区字段新建clustered索引且不关联分区方案即可 6、普通表改成分区表或把分区表改成普通表,只能使用clustered索引来实现,因为有了clustered索引就是索引组织表,通过clustered索引的重建来实现表的重新分布。普通表变成分区表,把分区字段重建为clustered索引并关联分区方案即可,分区表变成普通表,把分区字段重建为clustered索引不要关联分区方案即可。 7、分区表创建唯一性约束,必须包含分区列 8、创建分区方案时,必须保证文件组数量匹配分区函数的分区范围段,文件组名称重复没有关系,当然也可以使用ALL,指定一个文件组名称,这样所有的分区函数的分区范围段数据都落到这一个文件组。 9、分区函数和分区方案是在一个个数据库里面的,而不是面对整个实例的 10、分区表太大占用很多磁盘空间,delete了一些字段后大小还是没变,这个时候进行分区合并或把分区表转换为普通表,则大小会降下来
创建分区表的步骤 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:\test1part1.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP part1; ALTER DATABASE test1 ADD FILE(NAME = test1part1000,FILENAME = 'G:\test1part1000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP part1000; ALTER DATABASE test1 ADD FILE(NAME = test1part2000,FILENAME = 'G:\test1part2000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP part2000; ALTER DATABASE test1 ADD FILE(NAME = test1part3000,FILENAME = 'G:\test1part3000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP part3000; ALTER DATABASE test1 ADD FILE(NAME = test1part4000,FILENAME = 'G:\test1part4000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP part4000; 2、建立分区函数的示例,分区函数名为partfun1 CREATE PARTITION FUNCTION partfun1 (int) AS RANGE LEFT FOR VALUES ('1000','2000','3000','4000') --VALUES ('1000','2000','3000','4000')表明,将把表分为5个区了,是从根据表字段的值的大小来分区,五个区分别是最小--1000,1000-2000,2000-3000,3000-4000,4000-最大 3、建立方案的例子,关联分区函数partfun1,关联文件组 CREATE PARTITION SCHEME partschema1 AS PARTITION partfun1 TO (part1,part1000,part2000,part3000,part4000); --建立在part1,part1000,part2000,part3000,part4000几个文件组上 CREATE PARTITION SCHEME partschema2 AS PARTITION partfun1 TO (part1,[PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY]); --建立在part1、[PRIMARY]文件组上,把part1换成[PRIMARY]也没问题,这样就类似都建立在[PRIMARY]文件组上 CREATE PARTITION SCHEME partschema3 AS PARTITION partfun1 ALL TO (part1); --都建立在part1文件组上 CREATE PARTITION SCHEME partschema4 AS PARTITION partfun1 ALL TO ([PRIMARY]); --都建立在[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; --返回分区表所有行 SELECT distinct $PARTITION.[partfun1](4) FROM parttable1; --返回ID字段值为4的行属于哪个分区 SELECT * FROM parttable1 where $PARTITION.[partfun1](ID)=2 --返回第2个分区的所有行,ID就是分区字段ID 注意:不能因为SELECT * FROM parttable1 where $PARTITION.[partfun1](ID)=2有结果就说明它是分区表,本文最后试验7该表是非分区表了,但是执行SELECT * FROM parttable1 where $PARTITION.[partfun1](ID)=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 --第三条语句验证新增分区是否存在,是否存在行数
删除\合并分区 ALTER PARTITION FUNCTION partfun1() MERGE RANGE ('2000') 就把1000-2000这个分区,删除了,合并成了1000-3000 --无法像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
原文地址:http://blog.itpub.net/30126024/viewspace-2655191/
|