能否在T-SQL（SQL Server）函数内使用OPTION子句？

你遇到的问题是SQL Server的语法限制导致的：内联表值函数（即RETURNS TABLE AS RETURN这种形式）不允许在函数内部的查询中使用OPTION子句，所以直接添加OPTION (MAXRECURSION 0)会触发语法错误；但不加的话，递归CTE默认的最大递归深度是100，生成1000个数字时就会超出限制报错。

要实现让使用者无需额外添加MAXRECURSION选项的需求，有两种可行方案：

方案一：改用多语句表值函数 #

多语句表值函数支持在内部查询中使用OPTION子句，具体写法如下：

CREATE OR ALTER FUNCTION TestFunction()
RETURNS @Result TABLE (Number INT)
AS
BEGIN
    WITH NumberList AS (
        SELECT 1 AS Number
        UNION ALL
        SELECT Number + 1
        FROM NumberList
        WHERE Number < 1000
    )
    INSERT INTO @Result
    SELECT Number
    FROM NumberList
    OPTION (MAXRECURSION 0);

    RETURN;
END;

调用时直接执行SELECT * FROM TestFunction()即可，不会再触发递归深度超出的错误。

方案二：改用非递归方式生成数字列表 #

递归CTE生成连续数字的性能通常不如非递归方法，你可以利用系统表的笛卡尔积来生成序列，彻底避免递归深度问题：

CREATE OR ALTER FUNCTION TestFunction()
RETURNS TABLE
AS RETURN
SELECT TOP(1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Number
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2;

这种方式不需要递归，自然也不会受到递归深度限制，同时内联表值函数的性能优势也得以保留。

内容的提问来源于stack exchange，提问作者Daniel Jonsson