建表
分区函数
分区结构
分区相关系统表
-- 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/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))。可将
# 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> ) ]
用下列方式之一切换数据块：
- 将表的所有数据作为分区重新分配给现有的已分区表。
- 将分区从一个已分区表切换到另一个已分区表。
- 将已分区表的一个分区中的所有数据重新分配给现有的未分区的表。
如果 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 表达式。
含有聚集列存储索引的已分区表的行为与已区分堆类似：
- 主键必须包含分区键。
- 唯一索引必须包含分区键。不过，在现有唯一索引中添加分区键可能会改变唯一性。
- 所有非聚集索引都必须包含分区键才能切换分区。
分区变量
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)
