表分区常用语句

--常用分区语句：


--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 dbo.PayRequisitions switch partition 3 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,997')


--查看分区列数据类型
  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') 
  
--分区边界及分区行数
SELECT $partition.TimeformonthPFN(o.modifyTime) AS [Partition Number]
, min(o.modifyTime) AS [Min SendDate] 
, max(o.modifyTime) AS [Max SendDate] 
, count(*) AS [Rows In Partition] 
FROM interface.Log_PhoneCharge AS o 
GROUP BY $partition.TimeformonthPFN(o.modifyTime) 
ORDER BY [Partition Number]




--需要注意的几点：


--8在分区方案设计为all to primary的情况下可以直接使用 alter partition fun split range进行增加分区；
--9在分区方案为to (文件组,文件组)时需要先修改分区方案再修改分区函数增加分区；


   alter partition scheme scheme_name next used [file4]
   alter partition function month_fun() split range('2011-02-28T23:59:59.997')
   
--10多个表共用一个分区方案和分区函数时修改一个表的分区方案和分区函数增加或删除分区时会同时影响所有使用分区函数的表；
--11分区列为null值的表也可以分区，已存在分区列null值的表进行分区会把null值的数据分到第一分区，分区后所有插入的分区列为null的数据都默认放到第一分区；












--12关于索引导致分区表无法切入切出的情况：
  --1唯一非聚集或聚集索引和不唯一非聚集索引：重建索引分区后即可
  create index IX_name on table_name (createtime asc) with (DROP_EXISTING = on) on scheme_name(createtime)
  --2主键索引：如果主键不是分区列，则需要建立联合主键包含分区列进行索引分区即可
  
  --对主键聚集索引的表分区的时候，分区后索引变为唯一非聚集索引


  --聚集索引导致分区表不能切除到普通标的错误：目标表的聚集索引和源表不一致。解决办法：在目标表（普通表）的聚集索引索引件列上添加分区列。源表不需要改动；








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
















--建立分区语句（也可以使用msms图形界面直接创建）：




--已每个月份为单位创建分区函数
CREATE PARTITION FUNCTION [month_partition_new](datetime)
 AS RANGE LEFT FOR VALUES 
(N'2010-01-31T23:59:59.997', N'2010-02-28T23:59:59.997', N'2010-03-31T23:59:59.997',
 N'2010-04-30T23:59:59.997', N'2010-05-31T23:59:59.997', N'2010-06-30T23:59:59.997',
  N'2010-07-31T23:59:59.997', N'2010-08-31T23:59:59.997', N'2010-09-30T23:59:59.997', 
  N'2010-10-31T23:59:59.997', N'2010-11-30T23:59:59.997', N'2010-12-31T23:59:59.997',
  N'2011-01-31T23:59:59.997', N'2011-02-28T23:59:59.997', N'2011-03-31T23:59:59.997',
 N'2011-04-30T23:59:59.997', N'2011-05-31T23:59:59.997', N'2011-06-30T23:59:59.997',
  N'2011-07-31T23:59:59.997', N'2011-08-31T23:59:59.997', N'2011-09-30T23:59:59.997', 
  N'2011-10-31T23:59:59.997', N'2011-11-30T23:59:59.997', N'2011-12-31T23:59:59.997',
  N'2012-01-31T23:59:59.997', N'2012-02-28T23:59:59.997', N'2012-03-31T23:59:59.997',
 N'2012-04-30T23:59:59.997', N'2012-05-31T23:59:59.997', N'2012-06-30T23:59:59.997',
  N'2012-07-31T23:59:59.997', N'2012-08-31T23:59:59.997', N'2012-09-30T23:59:59.997', 
  N'2012-10-31T23:59:59.997', N'2012-11-30T23:59:59.997', N'2012-12-31T23:59:59.997'
  )
GO


--all to primary分区方案
CREATE PARTITION SCHEME [moth_scheme_new]
 AS PARTITION [month_partition_new] all TO 
 ([PRIMARY])
GO








----------------------------------分区表分区切换的三种形式：


--1. 切换分区表的一个分区到普通数据表中：Partition to Table


 alter table dbo.Orders switch partition 3 to dbo.Orders_1998
 
/* 进行分区到普通表的切换，最好满足以下的前提条件： 


1. 普通表必须建立在分区表切换分区所在的文件组上。 
2. 普通表的表结构跟分区表的一致； 
3. 普通表上的索引要跟分区表一致。 
4. 普通表必须是空表，不能有任何数据。 */


--2. 切换普通表数据到分区表的一个分区中：Table to Partition


 alter table dbo.Orders_1998 add constraint CK_Orders1998_OrderDate
       check (OrderDate>='1998-01-01' and OrderDate<'1999-01-01')


/* 
切换普通表数据到分区，除了满足上面的 4 个条件外，还要加上一条：
普通表必须加上和分区数据范围一致的 check 约束条件。*/


例：
alter table dbo.PayRequisitions_20100831 add constraint CK_PayRequisitions_201000831
check ( createtime >'2010-07-31T23:59:59.997' and createtime <='2010-08-31T23:59:59.997' )
       
alter table dbo.PayRequisitions_20100831 switch to dbo.PayRequisitions partition 8




--3. 切换分区表的分区到另一分区表：Partition to Partition


 alter table dbo.Orders switch partition 1  to dbo.OrdersArchive partition 1


/*
实际上，分区表分区切换并没有真正去移动数据，而是 SQL Server 在系统底层改变了表的元数据
因此分区表分区切换是高效、快速、灵活的。利用分区表的分区切换功能，我们可以快速加载数据到分区表
卸载分区数据到普通表，然后 truncate 普通表，以实现快速删除分区表数据。快速归档不活跃数据到历史表 */