创建归档表

归档表需要与源表结构保持一致，具有相同的索引
归档表与源表位于同一文件组
归档必须为空表

分区切出

ALTER TABLE tbl_source SWITCH PARTITION 1 TO staging_work 
GO

分区重用

归档表(staging_work)的数据完成归档后，对其进行清除，进行分区重用

TRUNCATE TABLE staging_work
GO
DROP TABLE staging_work
GO
ALTER PARTITION SCHEME PartitionScheme NEXT USED fg1 --重用fg1文件组对应的分区
GO

分区拆分

历史数据归档完成后，需要对最后的分区进行拆分，让单个分区保持合理的数据

ALTER PARTITION FUNCTION PF_XXX() SPLIT RANGE (10000) --10000为把最后分区拆的边界值，拆分后，10000为新的最后分区边界

分区合并

ALTER PARTITION FUNCTION PF_XXX() MERGE RANGE(1000) --1000为切出的第一个分区的边界

工具脚本

使用如下查询，可以查询分区表的分区列、分区方案、分区函数、分区存储文件组、分区的边界值以及分区的数据量

SELECT
OBJECT_SCHEMA_NAME(pstats.object_id) AS SchemaName
,OBJECT_NAME(pstats.object_id) AS TableName
,ps.name AS PartitionSchemeName
,ds.name AS PartitionFilegroupName
,pf.name AS PartitionFunctionName
,CASE pf.boundary_value_on_right WHEN 0 THEN 'Range Left' ELSE 'Range Right' END AS PartitionFunctionRange
,CASE pf.boundary_value_on_right WHEN 0 THEN 'Upper Boundary' ELSE 'Lower Boundary' END AS PartitionBoundary
,prv.value AS PartitionBoundaryValue
,c.name AS PartitionKey
,CASE WHEN ISNULL(prv.value, rv2.value) IS NULL THEN 'N/A' 
ELSE
    CASE WHEN pf.boundary_value_on_right = 0 AND rv2.value IS NULL THEN '>=' 
        WHEN pf.boundary_value_on_right = 0 THEN '>' 
        ELSE '>=' 
    END + ' ' + ISNULL(CONVERT(varchar(64), rv2.value), 'Min Value') + ' ' + 
        CASE pf.boundary_value_on_right WHEN 1 THEN 'and <' 
                ELSE 'and <=' END 
        + ' ' + ISNULL(CONVERT(varchar(64), prv.value), 'Max Value') 
END AS TextComparison
,pstats.partition_number AS PartitionNumber
,pstats.row_count AS PartitionRowCount
,p.data_compression_desc AS DataCompression
FROM sys.dm_db_partition_stats AS pstats
INNER JOIN sys.partitions AS p ON pstats.partition_id = p.partition_id
INNER JOIN sys.destination_data_spaces AS dds ON pstats.partition_number = dds.destination_id
INNER JOIN sys.data_spaces AS ds ON dds.data_space_id = ds.data_space_id
INNER JOIN sys.partition_schemes AS ps ON dds.partition_scheme_id = ps.data_space_id
INNER JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id
INNER JOIN sys.indexes AS i ON pstats.object_id = i.object_id AND pstats.index_id = i.index_id AND dds.partition_scheme_id = i.data_space_id AND i.type <= 1 /* Heap or Clustered Index */
INNER JOIN sys.index_columns AS ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id AND ic.partition_ordinal > 0
INNER JOIN sys.columns AS c ON pstats.object_id = c.object_id AND ic.column_id = c.column_id
LEFT JOIN sys.partition_range_values AS prv ON pf.function_id = prv.function_id AND pstats.partition_number = (CASE pf.boundary_value_on_right WHEN 0 THEN prv.boundary_id ELSE (prv.boundary_id+1) END)
LEFT JOIN sys.partition_range_values AS rv2
    ON pf.function_id = rv2.function_id
    AND p.partition_number - 1= rv2.boundary_id
ORDER BY TableName, PartitionNumber