主页 / dba / 问题 / 101215
Accepted
Eric S
Asked: 2015-05-12 11:59:33 +0800 CST

是否可以在函数中更改 CTE 的递归级别？

  • 772

我创建了一个接受字符串的函数，它返回一个表格，其中包含字符串中每个字母的位置和字符。但是，由于 CTE 递归限制为 100 级，如果传入的字符串比这长，它就会失败。我知道我可以OPTION (MAXRECURSION [value])用来更改限制，但是在定义函数时这似乎不起作用。

我的功能：

CREATE FUNCTION [dbo].[StringSplit]
(
    @String NVARCHAR(MAX)    
)
RETURNS TABLE
AS
RETURN
(   
    WITH Split(Pos,Digit)
    AS(
        SELECT 1 AS Pos, LEFT(@String, 1) AS Digit
        UNION ALL
        SELECT Pos + 1, RIGHT(LEFT(@String, Pos + 1), 1)
            FROM Split
            WHERE Pos < LEN(@String)
    )
    SELECT Pos, Digit FROM Split    
    ---OPTION (MAXRECURSION 0) -- Unable to create function if this line is UnCommented)
)

因此，由于达到 100 个默认限制，以下 sql 代码将失败：

SELECT * FROM Impact_Work.dbo.StringSplit('How long of a sentence can I possibly think of for testing thingymadoodlers out. Yes I do love the word thingymadoodlers so don''t judge me!')

现在，如果我添加该选项，它将起作用

SELECT * FROM Impact_Work.dbo.StringSplit('How long of a sentence can I possibly think of for testing thingymadoodlers out. Yes I do love the word thingymadoodlers so don''t judge me!')
OPTION (MAXRECURSION 0)

然而，那些使用这个函数的人不太可能记得在他们的查询中添加这个额外的位，所以我希望在它自己的函数中添加这个。这可能吗？（是的，当我需要拿出一些字符串进行测试时，我确实会胡思乱想）

sql-server sql-server-2014

3 个回答

  1. Best Answer

    我相信这就是 Mikael 所暗示的。

    如果没有 MAX 参数，你会发现事情工作得更快，你真的需要那么大的字符串吗？

    CREATE FUNCTION [dbo].[StringSplit]
(
    @String NVARCHAR(MAX)    
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(   
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (SELECT TOP (ISNULL(DATALENGTH(@String),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4)
        SELECT N AS Pos, substring(@String, N, 1) AS Digit
        from cteTally
);
    • 2

  2. 由于看起来无法更改特定函数的递归级别，因此出于几个原因，我决定将其保留原样。它解决了我设计它的问题，并且我在函数中添加了一条注释，说明如果人们决定将它用于其他事情，他们可以如何将其用于查询。

    • 1

  3. 实际上，100 次递归的限制是默认值。

    从这个页面：https ://msdn.microsoft.com/en-us/library/ms181714.aspx

    它部分说：

    MAXRECURSION 号码（我看到你已经找到了）说：

    指定此查询允许的最大递归数。number 是介于 0 和 32767 之间的非负整数。指定 0 时，不应用限制。如果未指定此选项，则服务器的默认限制为 100。

    在查询执行期间达到指定的或默认的 MAXRECURSION 限制数时，查询将结束并返回错误。

    但是，递归可能比其他方法更昂贵。

    注意：这不需要是服务器范围的设置，因为它可以是查询提示。如果您有自己的函数用于此字符串解析，那么该函数可以使用 MAXRECURSION (0) 而不会影响可能使用递归的其他代码。

    • 0

相关问题