【MSSQL】表分区

建表

https://docs.microsoft.com/zh-cn/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver15

分区函数

https://docs.microsoft.com/zh-cn/sql/t-sql/statements/create-partition-function-transact-sql?view=sql-server-ver15

分区结构

https://docs.microsoft.com/zh-cn/sql/t-sql/statements/create-partition-scheme-transact-sql?view=sql-server-ver15

分区相关系统表

-- sys.partitions 分区元数据，分区与表的关系
select * from sys.partitions where object_name(object_id) = 'TABLE_NAME'

-- sys.partition_functions 分区函数定义表
-- sys.partition_schemes 分区对象定义表
-- sys.destination_data_spaces 分区结构、分区和表空间多对多关系表

-- 查询表各个分区数据量
select s.name, i.name, p.partition_id, p.partition_number, v.value, ps.*
from sys.tables t 
	inner join sys.indexes i on t.object_id = i.object_id
	inner join sys.partitions p on i.index_id = p.index_id and t.object_id = p.object_id
	inner join sys.partition_schemes s on t.lob_data_space_id = s.data_space_id
	inner join sys.partition_functions f on s.function_id = f.function_id
	inner join sys.partition_range_values v on s.function_id = v.function_id
	inner join sys.dm_db_partition_stats ps on p.partition_id = ps.partition_id and v.boundary_id = ps.partition_number
where t.name = 'TableName' and i.name = 'IdxName' and f.name = 'FunName'

-- 查询有数据的分区
select * from sys.partitions p inner join sys.partition_range_values f 
on p.partition_number = f.boundary_id 
where object_name(object_id) = '' and rows> 0 and index_id = 1;

-- 分区表和文件组
select 
	distinct df.file_id, fg.name as filegroupname, fg.data_space_id, df.name as filename, df.physical_name, df.state_desc, dd.partition_scheme_id,
	ps.name as schemename, pf.name funcname
	from sys.filegroups fg 
	inner join 
		sys.database_files df on fg.data_space_id = df.data_space_id 
	inner join 
		sys.data_spaces ds on fg.data_space_id = ds.data_space_id
	inner join 
		sys.destination_data_spaces dd on fg.data_space_id = dd.data_space_id
	inner join 
		sys.partition_schemes ps on dd.partition_scheme_id = ps.data_space_id
	inner join 
		sys.partition_functions pf on ps.function_id = pf.function_id
where fg.data_space_id <> 1

分区表的查询逻辑

https://docs.microsoft.com/zh-cn/sql/relational-databases/query-processing-architecture-guide?view=sql-server-ver15#query-processing-enhancements-on-partitioned-tables-and-indexes

清理分区表数据

https://docs.microsoft.com/zh-cn/sql/t-sql/statements/truncate-table-transact-sql

# 适用范围：SQL Server（SQL Server 2016 (13.x) 到当前版本）
WITH ( PARTITIONS ( { <partition_number_expression> | <range> } [ , ...n ] ) )

指定要截断或删除其中所有行的分区。 如果未对表进行分区，则 WITH PARTITIONS 参数将生成错误。 如果未提供 WITH PARTITIONS 子句，则整个表都将被截断。

可通过下列方式指定 <partition_number_expression>：提供分区号，例如：WITH (PARTITIONS (2))。提供若干单独分区的分区号，并用逗号分隔，例如：WITH (PARTITIONS (1, 5))，同时提供范围和单独分区，例如：WITH (PARTITIONS (2, 4, 6 TO 8))。可将 指定为由单词 TO 隔开的分区号，例如：WITH (PARTITIONS (6 TO 8))。要截断一个已分区表，表和索引必须对齐（在同一个分区函数上进行分区）。

# Syntax for SQL Server and Azure SQL Database
TRUNCATE TABLE   
    { database_name.schema_name.table_name | schema_name.table_name | table_name }  
    [ WITH ( PARTITIONS ( { <partition_number_expression> | <range> }   
    [ , ...n ] ) ) ]  
[ ; ]  
  
<range> ::=  
<partition_number_expression> TO <partition_number_expression>  

# 下面的示例将截断已分区表的指定分区。 WITH (PARTITIONS (2, 4, 6 TO 8)) 语法导致分区号 2、4、6、7 和 8 被截断。
TRUNCATE TABLE PartitionTable1   
WITH (PARTITIONS (2, 4, 6 TO 8));  

迁移分区

https://docs.microsoft.com/zh-cn/sql/t-sql/statements/alter-table-transact-sql

# 适用于：SQL Server（SQL Server 2008 及更高版本）和 Azure SQL 数据库
SWITCH [ PARTITION source_partition_number_expression ] TO [ schema_name . ] target_table [ PARTITION target_partition_number_expression ]

ALTER TABLE table_name
SWITCH [ PARTITION source_partition_number_expression ]
        TO target_table
        [ PARTITION target_partition_number_expression ]
        [ WITH ( <low_priority_lock_wait> ) ]

用下列方式之一切换数据块：

1. 将表的所有数据作为分区重新分配给现有的已分区表。
2. 将分区从一个已分区表切换到另一个已分区表。
3. 将已分区表的一个分区中的所有数据重新分配给现有的未分区的表。

如果 table 是已分区表，必须指定 source_partition_number_expression。 如果 target_table 已分区，必须指定 target_partition_number_expression。 若要将表的数据作为分区重新分配给现有的已分区表，或要将分区由一个已分区表切换到另一个已分区表，那么目标分区必须存在且为空。

若要重新分配一个分区的数据来形成单个表，那么目标表必须已存在且为空。 源表或分区以及目标表或分区必须位于同一个文件组中。 相应的索引或索引分区也必须位于同一个文件组中。 切换分区还有许多其他限制。 table 和 target_table 不得相同。 target_table 可以是由多个部分组成的标识符。

source_partition_number_expression 和 target_partition_number_expression 是可以引用变量和函数的常量表达式 。 其中包括用户定义类型变量和用户定义函数。 它们无法引用 Transact-SQL 表达式。

含有聚集列存储索引的已分区表的行为与已区分堆类似：

1. 主键必须包含分区键。
2. 唯一索引必须包含分区键。不过，在现有唯一索引中添加分区键可能会改变唯一性。
3. 所有非聚集索引都必须包含分区键才能切换分区。

分区变量

https://docs.microsoft.com/zh-cn/sql/t-sql/functions/partition-transact-sql?view=sql-server-ver15

查询值所在分区

# Partition_Func是用户创建的分区函数
# Value是查询的数据值
SELECT $PARTITION.Partition_Func(Value)

查询表分区数据

-- ColumnName 是分区列名，不需要引号
SELECT * FROM TableName 
WHERE $PARTITION.Partition_Func(ColumnName) = $PARTITION.Partition_Func(Value)