sqlserver 分区知识
2019-11-08 18:36:41
供稿：网友
--分区属性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 recordCountfrom [dbo].pgtable_forana_log_testgroup 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 rowsfrom sys.indexes i join sys.partition_schemes ps on i.data_space_id = ps.data_space_id join sys.destination_data_spaces ddson 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_numberand 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_idand 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 intset @day=365 --DateName(day,getdate())%30+10 --((select datepart(weekday,getdate()))%7+1)alter table UpdateInfo_Hist10switch partition @dayto UpdateInfo_Histtemp--删除分区数据declare @day intset @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 @dayto custom_file_error_log_TMP --临时表
