Início / dba / Perguntas / 101215
Accepted
Eric S
Asked: 2015-05-12 11:59:33 +0800 CST

É possível alterar o nível de recursão de um CTE em uma função?

  • 772

Criei uma função que pega uma string e retorna uma tabela com a posição e caractere de cada letra da string. No entanto, como a recursão CTE é limitada a 100 níveis, ela falha se uma string passada for maior que isso. Sei que posso usar OPTION (MAXRECURSION [value])para alterar o limite, mas isso não parece funcionar na hora de definir a função.

minha função:

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)
)

Assim, o seguinte código sql falhará devido a atingir o limite padrão de 100:

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!')

Agora, se eu adicionar a opção, funcionará

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)

No entanto, é altamente improvável que aqueles que usam essa função se lembrem de adicionar esse bit extra às suas consultas, portanto, espero adicioná-lo à própria função. Isso é possível? (E sim, eu divago loucamente quando preciso criar alguma string para teste)

sql-server sql-server-2014

3 respostas

  1. Best Answer

    Isso é o que Mikael estava insinuando, eu acredito.

    Você vai descobrir que as coisas funcionam muito mais rápido sem esse parâmetro MAX, você realmente vai precisar de uma string tão grande?

    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. Como parece que não é possível alterar o nível de recursão para uma função específica, decidi deixar como está por alguns motivos. Ele resolve o problema para o qual o projetei e estou adicionando uma nota na função sobre como as pessoas podem usá-lo para suas consultas, caso decidam usá-lo para outras coisas.

    • 1

  3. Na verdade, o limite de 100 recursões é padrão.

    Nesta página: https://msdn.microsoft.com/en-us/library/ms181714.aspx

    Diz, em parte:

    Número MAXRECURSION (como vejo que você encontrou) diz:

    Especifica o número máximo de recursões permitidas para esta consulta. number é um inteiro não negativo entre 0 e 32767. Quando 0 é especificado, nenhum limite é aplicado. Se esta opção não for especificada, o limite padrão para o servidor é 100.

    Quando o número especificado ou padrão para o limite MAXRECURSION é atingido durante a execução da consulta, a consulta é finalizada e um erro é retornado.

    No entanto, a recursão pode ser mais cara do que outros métodos.

    Nota: Isso não precisa ser uma configuração ampla do servidor, pois pode ser uma dica de consulta. Se você tiver sua própria função para essa análise de string, essa função poderá usar MAXRECURSION (0) sem afetar outro código que possa usar recursão.

    • 0

relate perguntas