sqlserver表分区的相关总结

sqlserver的分区表是一个很好的工具，大大提高运维人员管理数据的效率。根据自己的经验总结了一下：

?

?--常用分区语句：

--1 ? ?查看数据库中的已分区表

--对sqlserver来说所有的表都是已分区表，所以我们只需要选择分区大于1的表即可

select distinct o.name from sys.tables o join sys.partitions p
on o.object_id = p.object_id and p.partition_number>1

?

--2 ? 各分区数据行数统计

select distinct o.name,p.partition_number,p.rows from sys.tables o join sys.partitions p
on (o.object_id = p.object_id)
where o.name = 'table_name'

--3 ?分区包含的详细数据统计

select top 1000 $partition.fun_name(createtime) as partition,createtime as?date?from table_name order by partition where partition = 14

--4 ?单个分区的详细数据统计

select top 1000 $partition.fun_name(createtime) as "partition #",*from table_name
where $partition.fun_name(createtime)=1

--5 ?切出数据
alter table testa1 switch partition 1 to tseta1bak;

--6 ?合并分区(删除分区)

alter partition function month_fun() merge range('2010-12-31 T23:59:59,997')

--7 ?添加新分区

alter partition function month_fun() split range('2011-02-28T23:59:59,sans-serif; ">--8 ?查看分区列数据类型
select name from sys.columns where
object_id = (select object_id from sys.tables where name= 'table_name')
and
system_type_id =
(select system_type_id from sys.partition_parameters p
join sys.partition_functions f
on p.function_id= f.function_id and f.name = 'fun_name')

--需要注意的相关问题：

--在分区方案设计为all to primary的情况下可以直接使用 alter partition fun split range进行增加分区；

--在分区方案为to (文件组,文件组)时需要先修改分区方案再修改分区函数增加分区。修改分区方案是为了增加下指定一个增加的分区使用的文件组。

alter partition scheme scheme_name next used?file4

--多个表共用一个分区方案和分区函数时修改一个表的分区方案和分区函数增加或删除分区时会同时影响所有使用分区函数的表；这个是一个非常消耗资源的操作。同时由于这个操作是原子性的。这样会造成相关的表在此期间被锁住，极大地影响数据库的使用。因此，现在在分区的时候尽量一个表使用一个分区函数和分区方案。?

--分区列为null值的表也可以分区，已存在分区列null值的表进行分区会把null值的数据分到第一分区，分区后所有插入的分区列为null的数据都默认放到第一分区；

--关于索引导致分区表无法切入切出的情况：
--（1）唯一非聚集或聚集索引和不唯一非聚集索引：重建索引并分区后即可。唯一索引需要索引包括分区列并使用分区方案建立。聚集索引在对表进行分区的时候就已经和分区列对齐了。不唯一非聚集索引只需要在上面应用分区方案即可，不需要添加分区列。
create index IX_name on table_name (createtime asc) with (DROP_EXISTING = on) on scheme_name(createtime)

--（2）主键索引：如果主键不是分区列，则需要建立联合主键包含分区列进行索引分区即可

--把分区表转换为普通表

这里澄清一个概念，普通表也是已分区表，只不过它只有一个分区，这个分区是根据聚集索引来的。这里指的普通表是说可以再利用别的字段进行分区的表。
有索引的分区表需要删除分区列的聚集或唯一索引，并且在分区列上重新建立一个索引，只有重新创建过索引之后，SQL Server才能将已分区表转换成普通表；
没有索引的分区表转换为普通表，可以直接在分区列上建立一个普通索引来实现；

/*切出分区
切出目标表可为分区表或非分区表，目标表必须满足以下条件：
1目标表存在且表结构相同
2目标表或目标分区为空
3目标为分区表的话，源表和目标表分区必须依据同一列
4源表和目标表必须在同一文件组下
*/

--建立分区语句（也可以使用msms图形界面直接创建）：
--创建分区函数 依据values为常量
create partition function fun_name(createtime)
as range left
for values ('2010-01-31T23:59:59,997','2010-02-28T23:59:59,'2010-03-31T23:59:59,sans-serif; ">--创建分区方案
--单个文件组方案
create partition SCHEME scheme_name as partition fun_name all to (primary)

--多文件组方案
create partition scheme scheme_name
as partition fun_name
to ('file1','file2','file3','file4')
go

--创建分区表 create table testa1 (id int,createtime datetime) on scheme_name(createtime)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!