Percorrer hierarquias com CTE recursiva no SQL Server

Uma estrutura organizacional costuma começar com identificador e identificador do pai. Ler um nível é simples; encontrar todos os descendentes exige repetir o percurso. Uma CTE recursiva descreve esse processo sem comprovar que as relações armazenadas formam uma árvore válida.

Defina primeiro o modelo. Cada nó possui um único pai? Podem existir várias raízes ou componentes separados? Uma chave estrangeira verifica a existência do pai, mas não impede ciclos maiores. Essas escolhas orientam consultas e validação das alterações.

Separar ponto inicial e expansão

O exemplo começa no nó 1 e segue filhos usando caminho visitado e limite de recursão.

DECLARE @Nodes table (NodeId int PRIMARY KEY, ParentId int NULL, Name nvarchar(50));
INSERT @Nodes VALUES (1,NULL,N'Company'),(2,1,N'Operations'),
(3,1,N'Engineering'),(4,2,N'Database team');

;WITH Tree AS (
    SELECT NodeId, ParentId, Name, 0 AS Depth,
           CAST('/' + CONVERT(varchar(11),NodeId) + '/' AS varchar(max)) AS Visited
    FROM @Nodes WHERE NodeId = 1
    UNION ALL
    SELECT n.NodeId, n.ParentId, n.Name, t.Depth + 1,
           CAST(t.Visited + CONVERT(varchar(11),n.NodeId) + '/' AS varchar(max))
    FROM @Nodes AS n
    JOIN Tree AS t ON n.ParentId = t.NodeId
    WHERE CHARINDEX('/' + CONVERT(varchar(11),n.NodeId) + '/', t.Visited) = 0
)
SELECT NodeId, ParentId, Name, Depth, Visited
FROM Tree
ORDER BY Visited
OPTION (MAXRECURSION 100);

A âncora produz Company na profundidade zero. A parte recursiva encontra Operations e Engineering e depois Database team. A junção por ParentId determina a direção; invertê-la procuraria ancestrais.

O caminho contém identificadores delimitados. Sem separadores, buscar 1 encontraria também 11 ou 21. Quando um identificador já está no caminho daquela ramificação, a expansão é excluída. Isso impede a repetição contínua de um ciclo, mas não corrige a relação inválida.

Os tipos da âncora e da parte recursiva precisam ser compatíveis. Ambos os caminhos são convertidos para varchar(max), evitando que uma string inicial curta imponha um tipo incompatível com o crescimento. O caminho guarda números, enquanto os nomes continuam Unicode.

ORDER BY Visited fornece uma ordenação ilustrativa, não uma ordem universal entre irmãos. Textos ordenam lexicalmente e 10 pode vir antes de 2. Modele uma ordem de negócio explicitamente quando necessário. A ordem em que a recursão produz linhas não garante a apresentação final.

Interpretar limites e exclusões

MAXRECURSION 100 é uma proteção, não uma regra universal sobre profundidade organizacional. Se o modelo permite mais níveis, escolha um limite justificado e teste-o. Atingi-lo deve falhar de forma visível; o cliente não deve tratar um resultado parcial como árvore completa.

MAXRECURSION 0 remove essa proteção sem demonstrar ausência de ciclos. Preserve validação de dados mesmo quando uma profundidade legítima exige outro limite.

O predicado do caminho encerra silenciosamente uma ramificação repetida. Relatórios administrativos devem detectar e informar ciclos separadamente. Ao mover um nó, verifique se o novo pai faria o próprio nó se tornar seu ancestral.

Partir de uma raiz também não descreve componentes desconectados. Compare identificadores alcançados com a população esperada ao auditar toda a estrutura. Um ciclo sem ligação com uma raiz pode desaparecer completamente do relatório. Nós ausentes podem indicar problema de dados, não de desempenho.

Apoiar a expansão repetida

Em uma tabela permanente, um índice iniciado por ParentId pode facilitar a localização dos filhos. Colunas adicionais devem seguir as consultas reais. A chave primária em NodeId atende outra direção e não acelera automaticamente a busca de descendentes.

Meça amplitude e quantidade total de descendentes além da profundidade. Uma árvore rasa com milhões de filhos pode custar mais que uma cadeia profunda e estreita. O caminho visitado cresce e é copiado em cada etapa, portanto essa proteção não é gratuita.

Para consultas enormes de subárvores muito frequentes, considere hierarchyid, tabela de fechamento ou outra representação mantida. Essas alternativas deslocam complexidade para atualizações e verificações de consistência. Compare o conjunto de leituras e gravações antes de escolher.

Teste nó único, irmãos, cadeia profunda, múltiplas raízes, componentes separados e um ciclo proposital em dados descartáveis. Compare os identificadores e a profundidade esperada. A consulta confiável explica o resultado e também por que determinados nós armazenados não aparecem nele.

Referências técnicas: Microsoft Learn: Recursive queries · Microsoft Learn: MAXRECURSION.