SQL Server中带递归CTE的表值函数如何设置maxrecursion选项

云原生阿岚

2026-9-25

问题原因

这是SQL Server的官方设计限制，不属于功能缺陷。内联表值函数的定义逻辑中不允许包含OPTION查询提示子句，该类查询提示属于外层调用查询的配置项，不能封装在函数内部。

可行解决方案

方案1：调用函数时在外层添加查询提示

保留现有函数的定义（去掉内部的OPTION子句），在实际调用函数的语句末尾添加OPTION (MAXRECURSION 0)即可，该提示会对函数内部的递归CTE生效，示例调用代码：

SELECT * FROM dbo.dates('2021-01-01', 365) OPTION (MAXRECURSION 0);

这种方式改造成本最低，适合临时使用的场景。

方案2：改用非递归的数字序列生成日期（推荐）

递归CTE生成日期的性能本身低于静态数字序列方案，且受递归次数限制，推荐改用交叉连接生成数字序列的方式实现，完全不需要递归，最多可生成超过6万天的日期（覆盖近180年的时间范围），完全满足绝大多数业务场景需求，函数实现代码如下：

CREATE FUNCTION dates(@start date, @end date) 
RETURNS TABLE AS
RETURN
WITH 
  L0 AS (SELECT 1 AS c UNION ALL SELECT 1),
  L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B),
  L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B),
  L3 AS (SELECT 1 AS c FROM L2 A CROSS JOIN L2 B),
  L4 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L3 B),
  Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS n FROM L4)
SELECT DATEADD(DAY, n, @start) AS date
FROM Nums
WHERE n <= DATEDIFF(DAY, @start, @end);

该方案不需要额外加任何递归限制提示，执行效率也远高于递归CTE实现，适合高频使用的场景。