Olá pessoal!
Como vocês estão ?

Nesse artigo eu gostaria de demonstrar a vocês Como criar consultas recursivas com a CTE (Common Table Expressions ou expressões de tabela comuns) no SQL Server. O recurso de CTE está disponível desde a versão 2005, e até hoje, muitas pessoas não conhecem esse recurso tão interessante e útil no dia a dia.

Introdução

Uma CTE tem o uso bem similar ao de uma subquery ou tabela derivada, com a vantagem do conjunto de dados poder ser utilizado mais de uma vez na consulta, ganhando performance (nessa situação) e também, melhorando a legibilidade do código. Por estes motivos, o uso da CTE tem sido bastante difundido como substituição à outras soluções citadas.

Exemplo de consulta com CTE:

;WITH CTE1
AS (
    SELECT
        Id_Bairro,
        Nm_Cliente,
        COUNT(Id_Pedido) AS Qt_Pedidos
    FROM
        Clientes
        LEFT JOIN Pedidos ON Clientes.Id_Cliente = Pedidos.Id_Pedido
    GROUP BY
        Id_Bairro,
        Nm_Cliente 
),
CTE2 AS ( 
    SELECT
        Nm_Bairro,
        COUNT(Nm_Cliente) AS Qt_Clientes,
        SUM(Qt_Pedidos) AS Qt_Pedidos
    FROM
        Bairro
        LEFT JOIN CTE1 ON Bairro.Id_Bairro = CTE1.Id_Bairro
    GROUP BY
        Nm_Bairro 
)
SELECT
    Nm_Bairro,
    Qt_Clientes,
    Qt_Pedidos,
    CAST(Qt_Pedidos AS NUMERIC(15, 2)) / Qt_Clientes AS Media
FROM
    CTE2;

Exemplo de consulta com Subquery:

SELECT
    Nm_Bairro,
    Qt_Clientes,
    Qt_Pedidos,
    CAST(Qt_Pedidos AS NUMERIC(15, 2)) / Qt_Clientes AS Media
FROM
(
    SELECT
        Nm_Bairro,
        COUNT(Nm_Cliente) AS Qt_Clientes,
        SUM(Qt_Pedidos) AS Qt_Pedidos
    FROM
        Bairro
        LEFT JOIN
        (
            SELECT
                Id_Bairro,
                Nm_Cliente,
                COUNT(Id_Pedido) AS Qt_Pedidos
            FROM
                Clientes
                LEFT JOIN Pedidos ON Clientes.Id_Cliente = Pedidos.Id_Pedido
            GROUP BY
                Id_Bairro,
                Nm_Cliente
        ) AS Q1 ON Bairro.Id_Bairro = Q1.Id_Bairro
    GROUP BY
        Nm_Bairro
) AS Q2

Além disso, uma CTE fornece a significativa vantagem de ser capaz de se autorreferenciar, criando, portanto uma CTE recursiva, que é executada repetidamente para retornar subconjuntos de dados até que o resultado completo seja obtido.

A CTE recursiva é muito utilizada para retornar dados hierárquicos, como por exemplo, exibir funcionários em um organograma, no qual podem ter vários níveis hierárquicos e vou demonstrar aqui, um exemplo de como a CTE recursiva funciona.

Exemplos de CTE Recursiva

Exemplo de CTE recursiva utilizando uma sequência de números:

WITH CTE_Numerico (Nivel, Numero) 
AS
(
    -- Âncora (nível 1)
    SELECT 1 AS Nivel, 1 AS Numero
    
    UNION ALL

    -- Níveis recursivos (Níveis N)
    SELECT Nivel + 1, Numero + Numero 
    FROM CTE_Numerico
    WHERE Numero < 2048
 )
SELECT *
FROM CTE_Numerico

Resultado:

Exemplo de CTE recursiva utilizando hierarquia:

Criação da massa de testes – Funcionários da empresa CLR Corporation

IF (OBJECT_ID('tempdb..#Funcionarios') IS NOT NULL) DROP TABLE #Funcionarios
CREATE TABLE #Funcionarios (
    Id_Empregado INT IDENTITY(1, 1) NOT NULL,
    Nm_Empregado VARCHAR(60) NOT NULL,
    Id_Superior INT NULL
)

INSERT INTO #Funcionarios(Nm_Empregado, Id_Superior)
VALUES ('Edvaldo Neves', NULL), ('Fabricio Amante', 1), ('Caio Lima', 1), ('Tiago Castro', 2)

INSERT INTO #Funcionarios(Nm_Empregado, Id_Superior)
VALUES ('Reginaldo Oliveira', 3), ('Fábio Merazzi', 3), ('Dirceu Resende', 2), ('Luiz Vitor Neves', 2)

INSERT INTO #Funcionarios(Nm_Empregado, Id_Superior)
VALUES ('Vithor Lima', 1), ('Edimar Lellis', 9), ('Lucas Fardim', 9), ('Aquila Loureiro', 9)

INSERT INTO #Funcionarios(Nm_Empregado, Id_Superior)
VALUES ('Rodrigo Almeida', 9), ('Flávio Castro', NULL), ('Raul Farias', 14), ('Logan Castro', 1)

INSERT INTO #Funcionarios(Nm_Empregado, Id_Superior)
VALUES('Fábio Amante', 3), ('Ariel Neves', 3), ('Leandro Galon', 7), ('Lucas Keller', 7)

INSERT INTO #Funcionarios(Nm_Empregado, Id_Superior)
VALUES('Richardson Folha', 19), ('Rafaela Giugliet', 20)

SELECT * FROM #Funcionarios

Estrutura da empresa fictícia

Dada a estrutura acima, quero criar os níveis de hierarquia desses funcionários. Como faríamos isso sem usar o CTE ?

Exemplo com hierarquia manual

Uma forma muito comum de se criar uma hierarquia de dados no SQL Server para poucos níveis, é através de inserções manuais.

Embora seja bem simples (basicamente, Ctrl+C e Ctrl+V), quando precisamos automatizar esse trabalho ou temos muitos níveis na nossa hierarquia (podendo até ser variáveis), essa solução acaba não atendendo muito bem, nos levando a buscar uma solução mais robusta.

Exemplo de criação de hirarquia manual:

IF (OBJECT_ID('tempdb..#Hiearquia') IS NOT NULL) DROP TABLE #Hiearquia
CREATE TABLE [#Hiearquia]
(
    [Id_Empregado] INT NOT NULL,
    [Nm_Empregado] VARCHAR(60) NOT NULL,
    [Id_Superior] INT,
    [Nivel] INT
)

-- Nível 1
INSERT INTO #Hiearquia
SELECT Id_Empregado, Nm_Empregado, Id_Superior, 1 AS Nivel
FROM #Funcionarios
WHERE Id_Superior IS NULL

-- Nível 2
INSERT INTO #Hiearquia
SELECT A.Id_Empregado, A.Nm_Empregado, A.Id_Superior, 2 AS Nivel
FROM #Funcionarios A
JOIN #Hiearquia B ON A.Id_Superior = B.Id_Empregado
WHERE NOT EXISTS(SELECT NULL FROM #Hiearquia WHERE Id_Empregado = A.Id_Empregado)

-- Nível 3
INSERT INTO #Hiearquia
SELECT A.Id_Empregado, A.Nm_Empregado, A.Id_Superior, 3 AS Nivel
FROM #Funcionarios A
JOIN #Hiearquia B ON A.Id_Superior = B.Id_Empregado
WHERE NOT EXISTS(SELECT NULL FROM #Hiearquia WHERE Id_Empregado = A.Id_Empregado)

-- Nível 4
INSERT INTO #Hiearquia
SELECT A.Id_Empregado, A.Nm_Empregado, A.Id_Superior, 4 AS Nivel
FROM #Funcionarios A
JOIN #Hiearquia B ON A.Id_Superior = B.Id_Empregado
WHERE NOT EXISTS(SELECT NULL FROM #Hiearquia WHERE Id_Empregado = A.Id_Empregado)

-- Nível 5
INSERT INTO #Hiearquia
SELECT A.Id_Empregado, A.Nm_Empregado, A.Id_Superior, 5 AS Nivel
FROM #Funcionarios A
JOIN #Hiearquia B ON A.Id_Superior = B.Id_Empregado
WHERE NOT EXISTS(SELECT NULL FROM #Hiearquia WHERE Id_Empregado = A.Id_Empregado)


SELECT * FROM #Hiearquia

Resultado:

Exemplo com LOOP WHILE

Visando criar uma mais robusta que a anterior e que funcione com N níveis de hierarquia, podemos utilizar um LOOP WHILE para isso, que vai criar uma iteração em cada nível a partir da âncora (primeiro nível) até o insert não encontrar mais registros a serem inseridos.

Exemplo de inserção utilizando LOOP:

IF (OBJECT_ID('tempdb..#Hiearquia') IS NOT NULL) DROP TABLE #Hiearquia
CREATE TABLE [#Hiearquia]
(
    [Id_Empregado] INT NOT NULL,
    [Nm_Empregado] VARCHAR(60) NOT NULL,
    [Id_Superior] INT,
    [Nivel] INT
)

-- Nível 1
INSERT INTO #Hiearquia
SELECT Id_Empregado, Nm_Empregado, Id_Superior, 1 AS Nivel
FROM #Funcionarios
WHERE Id_Superior IS NULL


DECLARE @Nivel INT = 2

WHILE(1=1)
BEGIN

    INSERT INTO #Hiearquia
    SELECT A.Id_Empregado, A.Nm_Empregado, A.Id_Superior, @Nivel AS Nivel
    FROM #Funcionarios A
    JOIN #Hiearquia B ON A.Id_Superior = B.Id_Empregado
    WHERE NOT EXISTS(SELECT NULL FROM #Hiearquia WHERE Id_Empregado = A.Id_Empregado)

    IF (@@ROWCOUNT <= 0) BREAK

    SET @Nivel += 1

END

SELECT * FROM #Hiearquia

Resultado:

Como vocês puderam observar, utilizei um loop while para conseguir consultar a própria tabela e assim, encadear os dados e montar a hierarquia.

Mas e a CTE recursiva? Ela poderia retornar esse mesmo resultado com apenas 1 SELECT?
R: É claro! Utilizando esse recurso, podemos criar uma consulta que vai retornar o resultado esperado com apenas uma consulta.

Exemplo com CTE Recursivo

;WITH CTE_Recursivo AS (
    -- Nível 1 (Âncora)
    SELECT Id_Empregado, Nm_Empregado, Id_Superior, 1 AS Nivel
    FROM #Funcionarios
    WHERE Id_Superior IS NULL

    UNION ALL

    -- Níveis 2-N
    SELECT A.Id_Empregado, A.Nm_Empregado, A.Id_Superior, B.Nivel + 1 AS Nivel
    FROM #Funcionarios A
    JOIN CTE_Recursivo B ON A.Id_Superior = B.Id_Empregado
)
SELECT *
FROM CTE_Recursivo

Resultado:

Controlando o grau de recursão com MAXRECURSION

Agora que você já viu como utilizar a CTE recursiva e como ela pode ser prática, chegou a hora de entender o risco de se construir uma CTE recursiva em “loop infinito”. Isso acontece quando você não programa bem a recursão da sua query e ela pode acabar atingindo N níveis e até travar a sua instância e tanto consumir recursos tentando resolver a consulta.

Para evitar esse tipo de situação, existe a query hint MAXRECURSION, que permite especificar o nível máximo de recursão. Ao atingir esse nível, o SQL irá abortar o restante da execução e mostrar uma mensagem de erro com severidade 16, conforme demonstrado abaixo:

;WITH CTE_Recursivo AS (
    -- Nível 1 (Âncora)
    SELECT Id_Empregado, Nm_Empregado, Id_Superior, 1 AS Nivel
    FROM Dacasa.dbo.Funcionarios_Teste
    WHERE Id_Superior IS NULL

    UNION ALL

    -- Níveis 2-N
    SELECT A.Id_Empregado, A.Nm_Empregado, A.Id_Superior, B.Nivel + 1 AS Nivel
    FROM Dacasa.dbo.Funcionarios_Teste A
    JOIN CTE_Recursivo B ON A.Id_Superior = B.Id_Empregado
)
SELECT *
FROM CTE_Recursivo
OPTION(MAXRECURSION 1)

Mensagem de erro:

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 1 has been exhausted before statement completion.

Resultado:

Vejam que utilizando o hint OPTION(MAXRECURSION 1), apenas o nível primário e mais 1 nível de hierarquia foram retornados (níveis 1 e 2). Caso utilizássemos o valor MAXRECURSION 2, o nível primário e mais 2 níveis de hierarquia seriam retornados (nível 1 ao nível 3).

Como a exceção gerada pelo hint MAXRECURSION é de severidade 16, ela causa uma interrupção no restante da rotina e caso seja um job, esse job irá retornar uma falha. Caso você não queira esse comportamento e deseja apenas limitar o nível de recursão, mas sem provocar erro na rotina, basta encapsular a sua CTE em um bloco de TRY..CATCH:

BEGIN TRY

    ;WITH CTE_Recursivo AS (
        -- Nível 1 (Âncora)
        SELECT Id_Empregado, Nm_Empregado, Id_Superior, 1 AS Nivel
        FROM Dacasa.dbo.Funcionarios_Teste
        WHERE Id_Superior IS NULL

        UNION ALL

        -- Níveis 2-N
        SELECT A.Id_Empregado, A.Nm_Empregado, A.Id_Superior, B.Nivel + 1 AS Nivel
        FROM Dacasa.dbo.Funcionarios_Teste A
        JOIN CTE_Recursivo B ON A.Id_Superior = B.Id_Empregado
    )
    SELECT *
    FROM CTE_Recursivo
    OPTION(MAXRECURSION 1)

END TRY
BEGIN CATCH
END CATCH

Observação: O parâmetro do hint MAXRECURSION suporta valores de 1 a 32.767. Caso você queira utilizar um nível de recursão acima desse valor, basta não utilizar esse hint ou especificar o valor 0, que significa sem limite de recursividade.

Uma outra forma de demonstrar um uso de CTE recursiva bem interessante é para gerar sequências de números:

Exemplo de CTE recursiva para gerar uma sequência de 200 números:

;WITH CTE_Numerico AS
(
    SELECT 1 AS Numero
    UNION ALL
    SELECT Numero + 1 FROM CTE_Numerico
 )
SELECT * FROM CTE_Numerico
OPTION (MAXRECURSION 199)

Resultado:

Utilizando esse código acima, foi possível limitar os 200 registros utilizando o hint MAXRECURSION(199), limitando a 199 níveis de recursividade. Entretanto, é possível garantir essa limitação de uma outra forma (e sem gerar uma exceção):

WITH CTE_Numerico AS
(
    SELECT 1 AS Numero
    UNION ALL
    SELECT Numero + 1 FROM CTE_Numerico
    WHERE Numero < 200
 )
SELECT * FROM CTE_Numerico
OPTION (MAXRECURSION 249)

Resultado:

Então reparem que, mesmo especificando que o nível de recursão é 249, ou seja, o nível máximo de recursão são 250 registros (e de fato, deveriam ter sido gerados 250 registros), como eu limitei a quantidade em 200 registros dentro da query da CTE, foi possível retornar apenas os 200 registros, fazendo com que o hint MAXRECURSION seja dispensável neste caso.

Com isso, foi possível demonstrar que é possível limitar os registros de um CTE recursivo (em determinadas situações) mesmo sem utilizar o hint MAXRECURSION.

Conclusão

Nesse artigo, pude demonstrar o que é uma CTE, em que situações podemos utilizá-la, como ela é construída e algumas das vantagens que ela possui sobre outras soluções, como tabelas derivadas e subquery.

Além disso, foi possível explicar como uma CTE recursiva funciona, como ela pode ser montada e alguns exemplos onde ela pode ser especialmente útil.

Espero que tenham gostado desse artigo e que ele tenha sido útil para vocês!
Um abraço e até a próxima!