Você já se deparou com uma CTE recursiva no SQL Server e se sentiu intimidado com sua sintaxe e uso? Você não está sozinho. CTEs recursivas podem ser confusas à primeira vista, mas uma vez que você as entende, elas se tornam muito mais gerenciáveis. Neste artigo, exploraremos o que é uma CTE recursiva, quando usá-la e como superar desafios comuns.

O que é uma CTE Recursiva?

Uma Expressão de Tabela Comum (CTE) é um conjunto de resultados temporário que você pode usar em uma única instrução SQL. Existem dois tipos de CTEs: não recursivas e recursivas. CTEs não recursivas são comumente usadas e fáceis de entender. No entanto, CTEs recursivas podem ser mais complexas.

Uma CTE recursiva é usada para percorrer estruturas recursivas, como uma hierarquia de empresa, lista de materiais ou árvore genealógica. Ela faz referência ao seu próprio conjunto de resultados várias vezes até que uma condição especificada seja atendida. Isso a torna uma ferramenta poderosa para consultar dados hierárquicos.

As Duas Partes de uma CTE Recursiva

Uma CTE recursiva consiste em duas partes principais: a âncora e a recursão. A âncora é o ponto de partida da recursão, frequentemente referido como CEO, pai ou chefe. É a consulta inicial que recupera o primeiro conjunto de registros.

A parte de recursão é onde a mágica acontece. Ela junta a tabela original com a própria CTE recursiva, usando uma coluna comum para estabelecer a relação entre registros pai e filhos. Essa junção recursiva continua até que a condição especificada seja atendida.

Vamos dar uma olhada em um exemplo para ilustrar essas duas partes:

DROP TABLE IF EXISTS #Employees;
CREATE TABLE #Employees
(
    Id INT NOT NULL,
    Name VARCHAR(50) NOT NULL,
    ManagerId INT NULL
);

INSERT INTO #Employees
(
    Id,
    Name,
    ManagerId
)
VALUES
(1, 'John', NULL),
(2, 'Jane', 1),
(3, 'Mark', 1),
(4, 'Sarah', 2),
(5, 'Mike', 3);

WITH RecursiveCTE
AS (
    SELECT Id,
           Name,
           ManagerId
    FROM #Employees
    WHERE ManagerId IS NULL -- Consulta âncora

    UNION ALL

    SELECT e.Id,
           e.Name,
           e.ManagerId
    FROM #Employees e
        INNER JOIN RecursiveCTE r
            ON e.ManagerId = r.Id -- Junção recursiva
)
SELECT r.Id,
       r.Name,
       r.ManagerId
FROM RecursiveCTE r;

No exemplo acima, temos uma tabela chamada #Employees que representa uma hierarquia organizacional. A consulta âncora recupera o CEO (John) que não tem gerente. A parte recursiva então junta a tabela #Employees com a própria RecursiveCTE, relacionando funcionários com seus respectivos gerentes.

Ao executar essa consulta, podemos obter uma visualização hierárquica da organização, incluindo todos os funcionários e seus gerentes.

Lidando com a Recursão Máxima

O SQL Server define um limite padrão de 100 níveis para CTEs recursivas para evitar loops infinitos. Se sua CTE recursiva exceder esse limite, você encontrará um erro de “recursão máxima esgotada”.

Para contornar essa limitação, você pode usar a cláusula OPTION (MAXRECURSION 0) em sua consulta. No entanto, é essencial usar essa opção com cautela, pois ela pode causar problemas de desempenho se a recursão for muito profunda.

Conclusão

CTEs recursivas são uma ferramenta poderosa para consultar dados hierárquicos no SQL Server. Ao entender as duas partes principais de uma CTE recursiva e como lidar com a recursão máxima, você pode usar com confiança esse recurso para percorrer estruturas recursivas em seu banco de dados.

Da próxima vez que você se deparar com um problema recursivo, considere usar uma CTE recursiva em vez de escrever loops ou cursores complexos. Isso pode simplificar seu código e torná-lo mais legível.

Lembre-se, CTEs recursivas são apenas uma das muitas ferramentas em seu arsenal do SQL Server. Experimente-as, explore diferentes casos de uso e veja como elas podem aprimorar suas capacidades de consulta de dados.

Codificação feliz!