sqlserver 分区知识

--分区属性
SELECT * FROM sys.partitions AS p
  JOIN sys.tables AS t
      ON  p.object_id = t.object_id
  WHERE p.partition_id IS NOT NULL
      AND t.name = 'custom_file_error_log';








--查看分区方案段
select $partition.[PGTABLE_FORANA_FUNC](day) as [PGTABLE_FORANA_FUNC],count(*) as recordCount
from [dbo].pgtable_forana_log_test
group by  $partition.[PGTABLE_FORANA_FUNC](day)












--查看分区详情
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('custom_file_error_log')
and i.index_id in (0, 1) 
order by p.partition_number










--1.移除历史记录(一周前的) [每天23:30执行一次]
declare @day int
set @day=365                         --DateName(day,getdate())%30+10                             --((select datepart(weekday,getdate()))%7+1)
alter table UpdateInfo_Hist10
switch partition @day
to UpdateInfo_Histtemp








--删除分区数据
declare @day int
set @day=365   --DateName(day,getdate()-10)                         --DateName(day,getdate())%30+10                             --((select datepart(weekday,getdate()))%7+1)
alter table custom_file_error_log -- 分区表
switch partition @day
to custom_file_error_log_TMP    --临时表