为何SQL Server分区函数无法返回更多分区编号？

背景信息 #

partition function（分区函数）用于在SQL Server中对表或索引进行水平分区，它定义了分区边界，并指定范围的行为（即边界的LEFT或RIGHT规则）。

以下是一个简单的分区函数示例：

IF EXISTS(SELECT * FROM sys.partition_functions WHERE name = 'year_partition_function')
  DROP PARTITION FUNCTION year_partition_function;

CREATE PARTITION FUNCTION year_partition_function (INT) AS 
  RANGE LEFT FOR VALUES (2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009 
                        ,2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019 
                        ,2020, 2021, 2022, 2023, 2024, 2025, 2026, 2027, 2028, 2029, 2030);

测试设置 #

在TSQL中使用$PARTITION，可以传入示例值来查询分区函数计算出的对应分区，这是调试分区函数行为的便捷方法。以下示例中，调用上述创建的分区函数，传入所有与边界完全匹配的值，以查看其返回的分区范围。

测试用TSQL代码：

WITH years AS
(
  SELECT s.value AS Value
  FROM STRING_SPLIT('2000,2001,2002,2003,2004,2005,2006,2007,2008,2009' +
                   ',2010,2011,2012,2013,2014,2015,2016,2017,2018,2019' +
                   ',2020,2021,2022,2023,2024,2025,2026,2027,2028,2029,2030',',') AS s
)
, parts as 
(
  SELECT Value
    , $PARTITION.customer_partition_function(Value) AS [PartitionNumber]
  FROM years
)
SELECT [PartitionNumber]
  , STRING_AGG(Value, ',') AS Boundaries
FROM parts
GROUP BY [PartitionNumber]

问题描述 #

原本预期该查询的结果分区数量与传入的边界数量一致，所有值都是唯一且已在分区函数中列为边界，但实际只得到了少量分区。

问题：为何测试该分区函数时无法得到更多分区？

实际结果：

问题原因与解决方法 #

核心问题在于测试代码中调用的分区函数名称错误：你创建的分区函数名为year_partition_function，但测试SQL里写的是$PARTITION.customer_partition_function(Value)，调用了一个不存在（或与预期不符）的分区函数。

当调用未正确定义的分区函数时，SQL Server会使用默认分区规则（或该customer_partition_function本身只有少量分区边界），导致所有测试值被分配到少数几个分区中，和预期结果不符。

修正方法很简单，把测试代码中的customer_partition_function替换为year_partition_function即可：

WITH years AS
(
  SELECT s.value AS Value
  FROM STRING_SPLIT('2000,2001,2002,2003,2004,2005,2006,2007,2008,2009' +
                   ',2010,2011,2012,2013,2014,2015,2016,2017,2018,2019' +
                   ',2020,2021,2022,2023,2024,2025,2026,2027,2028,2029,2030',',') AS s
)
, parts as 
(
  SELECT Value
    , $PARTITION.year_partition_function(Value) AS [PartitionNumber]
  FROM years
)
SELECT [PartitionNumber]
  , STRING_AGG(Value, ',') AS Boundaries
FROM parts
GROUP BY [PartitionNumber]

修正后，每个边界值会被分配到对应的分区中，你就能得到与边界数量匹配的分区结果了。

内容的提问来源于stack exchange，提问作者Jerry Nixon