Módulo 15 — CTEs & Recursividade

1) Conceitos & Sintaxe

Uma CTE é um resultado nomeado, válido apenas para a próxima instrução. Facilita legibilidade, reutilização dentro da consulta e recursão.

-- Forma geral
WITH cte_nome (col1, col2, ...) AS (
  <consulta_base>     -- CTE não-recursiva OU membro âncora em recursivas
  [UNION ALL
   <consulta_recursiva>]  -- apenas em CTEs recursivas; referencia cte_nome
)
SELECT ... FROM cte_nome;

2) CTE Não-Recursiva (fatoração de consultas)

Útil para organizar subconsultas e etapas. Pode também servir como alvo de UPDATE/DELETE.

-- Ex.: faturamento por cliente e filtro em camada externa
WITH Totais AS (
  SELECT v.IdCliente, SUM(v.Valor) AS Total, COUNT(*) AS Qtde
  FROM dbo.Vendas AS v
  GROUP BY v.IdCliente
)
SELECT c.IdCliente, c.Nome, t.Total, t.Qtde
FROM Totais AS t
JOIN dbo.Clientes AS c ON c.IdCliente = t.IdCliente
WHERE t.Total >= 1000.00;

-- Deduplicação com ROW_NUMBER (preparando para DELETE)
WITH Dups AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY CPF ORDER BY IdCliente) AS rn
  FROM dbo.Clientes
)
SELECT * FROM Dups WHERE rn > 1;  -- linhas duplicadas

3) CTE Recursiva (âncora + recursão)

Composta por um membro âncora e um membro recursivo unidos por UNION ALL. O recursivo referencia a própria CTE.

-- Estrutura
WITH cte_hierarquia AS (
  -- Âncora
  SELECT Id, ParentId, Nome, 0 AS Nivel, CAST(Nome AS nvarchar(4000)) AS Caminho
  FROM dbo.Categorias
  WHERE ParentId IS NULL  -- raiz, ou ponto de partida

  UNION ALL
  -- Recursivo
  SELECT c.Id, c.ParentId, c.Nome, h.Nivel + 1,
         CAST(h.Caminho + N' > ' + c.Nome AS nvarchar(4000)) AS Caminho
  FROM dbo.Categorias AS c
  JOIN cte_hierarquia AS h ON c.ParentId = h.Id
)
SELECT * FROM cte_hierarquia
ORDER BY Caminho
OPTION (MAXRECURSION 100);  -- padrão é 100; 0 = ilimitado

4) Hierarquias: Descendentes e Ancestrais

4.1) Todos os descendentes de uma categoria

DECLARE @Raiz int = 1;  -- Id da categoria de partida

WITH Descendentes AS (
  -- Âncora: a própria categoria
  SELECT Id, ParentId, Nome, 0 AS Profundidade
  FROM dbo.Categorias WHERE Id = @Raiz

  UNION ALL
  -- Recursivo: filhos
  SELECT c.Id, c.ParentId, c.Nome, d.Profundidade + 1
  FROM dbo.Categorias AS c
  JOIN Descendentes AS d ON c.ParentId = d.Id
)
SELECT * FROM Descendentes
ORDER BY Profundidade, Id
OPTION (MAXRECURSION 200);

4.2) Subindo a árvore (ancestrais)

DECLARE @Folha int = 42;  -- Id do nó folha

WITH Ancestrais AS (
  SELECT Id, ParentId, Nome, 0 AS Nivel
  FROM dbo.Categorias WHERE Id = @Folha

  UNION ALL
  SELECT p.Id, p.ParentId, p.Nome, a.Nivel + 1
  FROM dbo.Categorias AS p
  JOIN Ancestrais AS a ON p.Id = a.ParentId
)
SELECT * FROM Ancestrais
ORDER BY Nivel
OPTION (MAXRECURSION 50);

4.3) Evitando ciclos (detecção simples com caminho)

WITH Safe AS (
  SELECT Id, ParentId, Nome, CAST(','+CAST(Id AS varchar(20))+',' AS varchar(4000)) AS Visitados
  FROM dbo.Categorias WHERE ParentId IS NULL

  UNION ALL
  SELECT c.Id, c.ParentId, c.Nome, CAST(s.Visitados + CAST(c.Id AS varchar(20)) + ',' AS varchar(4000))
  FROM dbo.Categorias AS c
  JOIN Safe AS s ON c.ParentId = s.Id
  WHERE CHARINDEX(','+CAST(c.Id AS varchar(20))+',', s.Visitados) = 0  -- evita repetir
)
SELECT * FROM Safe
OPTION (MAXRECURSION 0);  -- ilimitado, com segurança por ciclo

5) Uso com DML (UPDATE/DELETE) e deduplicação

Uma CTE pode ser alvo de UPDATE e DELETE quando corresponde a uma única tabela base.

-- Atualizar somente registros “folha” (sem filhos)
WITH Folhas AS (
  SELECT c.*
  FROM dbo.Categorias AS c
  WHERE NOT EXISTS (SELECT 1 FROM dbo.Categorias AS f WHERE f.ParentId = c.Id)
)
UPDATE Folhas SET Nome = Nome + N' (Folha)';

-- Deletar duplicatas mantendo a menor Id por CPF
WITH Dups AS (
  SELECT IdCliente, CPF,
         ROW_NUMBER() OVER (PARTITION BY CPF ORDER BY IdCliente) AS rn
  FROM dbo.Clientes
)
DELETE FROM Dups WHERE rn > 1;

6) Paginação e Séries

6.1) Paginação com ROW_NUMBER()

DECLARE @Page int = 2, @PageSize int = 10;

WITH Paged AS (
  SELECT v.*,
         ROW_NUMBER() OVER (ORDER BY v.DataVenda DESC, v.IdVenda DESC) AS rn
  FROM dbo.Vendas AS v
)
SELECT *
FROM Paged
WHERE rn BETWEEN (@Page-1)*@PageSize + 1 AND @Page*@PageSize;

6.2) Série de datas (exemplo didático)

DECLARE @Inicio date = '2025-08-01', @Fim date = '2025-08-31';

WITH Dias AS (
  SELECT @Inicio AS Dia
  UNION ALL
  SELECT DATEADD(DAY, 1, Dia) FROM Dias WHERE Dia < @Fim
)
SELECT Dia FROM Dias
OPTION (MAXRECURSION 1000);  -- 31 é suficiente; mantido amplo por segurança

7) Desempenho, Ciclos & MAXRECURSION

• Limite a profundidade com OPTION (MAXRECURSION N). Padrão: 100. 0 = ilimitado.
• Evite ciclos: rastreie visitados ou defina restrições de integridade no modelo.
• Indexe chaves de junção (ex.: ParentId).
• Considere materializar caminhos/níveis em colunas calculadas ou tabelas auxiliares quando consultas forem muito frequentes.
• Para relatórios, combine com views e indexed views (quando aplicável).

8) Laboratório Guiado

8.1) Setup & dados

-- Tabela de categorias hierárquicas
IF OBJECT_ID('dbo.Categorias','U') IS NOT NULL DROP TABLE dbo.Categorias;
GO
CREATE TABLE dbo.Categorias(
  Id       int IDENTITY PRIMARY KEY,
  ParentId int NULL REFERENCES dbo.Categorias(Id),
  Nome     nvarchar(100) NOT NULL
);
GO
-- Amostra
INSERT INTO dbo.Categorias (ParentId, Nome) VALUES
(NULL, N'Veículos'),
(1,    N'Carros'),
(1,    N'Caminhões'),
(2,    N'Sedãs'),
(2,    N'SUVs'),
(3,    N'Leves'),
(3,    N'Pesados');
GO

8.2) Árvore completa com caminho

WITH Arvore AS (
  SELECT Id, ParentId, Nome, 0 AS Nivel, CAST(Nome AS nvarchar(4000)) AS Caminho
  FROM dbo.Categorias WHERE ParentId IS NULL
  UNION ALL
  SELECT c.Id, c.ParentId, c.Nome, a.Nivel+1, CAST(a.Caminho + N' > ' + c.Nome AS nvarchar(4000))
  FROM dbo.Categorias AS c
  JOIN Arvore AS a ON c.ParentId = a.Id
)
SELECT * FROM Arvore ORDER BY Caminho OPTION (MAXRECURSION 50);

8.3) Apenas folhas e UPDATE via CTE

WITH Folhas AS (
  SELECT c.*
  FROM dbo.Categorias AS c
  WHERE NOT EXISTS (SELECT 1 FROM dbo.Categorias AS f WHERE f.ParentId = c.Id)
)
UPDATE Folhas SET Nome = Nome + N' ✔';

8.4) Série de dias do mês

DECLARE @Y int = 2025, @M int = 8;
DECLARE @Inicio date = DATEFROMPARTS(@Y,@M,1);
DECLARE @Fim    date = EOMONTH(@Inicio);

WITH Dias AS (
  SELECT @Inicio AS Dia
  UNION ALL
  SELECT DATEADD(DAY,1,Dia) FROM Dias WHERE Dia < @Fim
)
SELECT Dia FROM Dias OPTION (MAXRECURSION 1000);

9) Exercícios

1. Descendentes: Dada uma categoria raiz, traga todos os descendentes com níveis.
2. Ancestrais: Dado um nó, traga toda a cadeia de ancestrais até a raiz.
3. Folhas: Marque com sufixo ' (Folha)' todas as categorias folha via UPDATE usando CTE.
4. Deduplicação: Remova clientes duplicados por CPF, mantendo o menor IdCliente.
5. Série de Inteiros: Gere uma série de 1..N com CTE recursiva e mostre apenas múltiplos de 7.

10) Gabarito (sugestões)

-- 1) Descendentes
DECLARE @Raiz int = 1;
WITH D AS (
  SELECT Id, ParentId, Nome, 0 AS Nivel FROM dbo.Categorias WHERE Id = @Raiz
  UNION ALL
  SELECT c.Id, c.ParentId, c.Nome, d.Nivel+1
  FROM dbo.Categorias AS c
  JOIN D AS d ON c.ParentId = d.Id
)
SELECT * FROM D ORDER BY Nivel, Id OPTION (MAXRECURSION 200);

-- 2) Ancestrais
DECLARE @Folha int = 5;
WITH A AS (
  SELECT Id, ParentId, Nome, 0 AS Nivel FROM dbo.Categorias WHERE Id = @Folha
  UNION ALL
  SELECT p.Id, p.ParentId, p.Nome, a.Nivel+1
  FROM dbo.Categorias AS p
  JOIN A AS a ON p.Id = a.ParentId
)
SELECT * FROM A ORDER BY Nivel OPTION (MAXRECURSION 50);

-- 3) UPDATE de folhas
WITH F AS (
  SELECT c.* FROM dbo.Categorias AS c
  WHERE NOT EXISTS (SELECT 1 FROM dbo.Categorias AS f WHERE f.ParentId = c.Id)
)
UPDATE F SET Nome = Nome + N' (Folha)';

-- 4) DELETE duplicados por CPF
WITH Dups AS (
  SELECT IdCliente, CPF,
         ROW_NUMBER() OVER (PARTITION BY CPF ORDER BY IdCliente) AS rn
  FROM dbo.Clientes
)
DELETE FROM Dups WHERE rn > 1;

-- 5) Série 1..N (múltiplos de 7)
DECLARE @N int = 100;
WITH S AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n+1 FROM S WHERE n < @N
)
SELECT n FROM S WHERE n % 7 = 0
OPTION (MAXRECURSION 32767);

11) Erros Comuns & Checklist

12) Próximos Passos

• Modelar hierarquias grandes usando estratégias híbridas (ex.: colunas de caminho, pré-cálculos).
• Comparar CTE recursiva com hierarchyid (tipo especializado) — tópico avançado.
• Preparar o Módulo 16: PIVOT & UNPIVOT (transformação de linhas/colunas).