Recorrer jerarquías con CTE recursivas en SQL Server

Una organización o catálogo suele comenzar con una tabla de identificadores y padres. Leer un nivel es sencillo; encontrar todos los descendientes requiere repetir el recorrido. Una CTE recursiva expresa ese trabajo, pero no demuestra que las relaciones guardadas constituyan un árbol válido.

Primero define el modelo. ¿Cada nodo tiene un solo padre? ¿Se permiten varias raíces o componentes desconectados? Una clave externa verifica que el padre exista, pero no impide un ciclo largo. Esas decisiones afectan tanto a la consulta como a las modificaciones.

Separar inicio y expansión

El ejemplo comienza en el nodo 1 y sigue hijos con una ruta visitada y un límite de recursión.

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

El ancla devuelve Company en profundidad cero. La parte recursiva encuentra Operations y Engineering y después Database team. La unión por ParentId determina la dirección; invertirla buscaría ancestros.

La ruta incluye identificadores delimitados. Sin separadores, buscar 1 también encontraría 11 o 21. Si un identificador ya figura en la ruta de esa rama, la expansión se excluye. Eso evita repetir indefinidamente un ciclo, pero no repara los datos.

Los tipos del ancla y la parte recursiva deben ser compatibles. Ambas rutas se convierten explícitamente a varchar(max). Una cadena inicial corta podría fijar un tipo incompatible con la expresión que crece. La ruta contiene números y los nombres permanecen Unicode.

ORDER BY Visited ofrece una ordenación ilustrativa, no un orden universal entre hermanos. Los textos se ordenan léxicamente y 10 puede aparecer antes que 2. Si necesitas un orden del negocio, almacénalo y constrúyelo expresamente. La secuencia de producción recursiva no garantiza la presentación.

Tratar límites y exclusiones como evidencia

MAXRECURSION 100 es una protección, no una afirmación de que toda organización tenga menos niveles. Si el modelo permite más profundidad, elige un límite justificado y pruébalo. Alcanzarlo debe fallar visiblemente; el cliente no debe presentar una respuesta parcial como árbol completo.

MAXRECURSION 0 elimina esa barrera, pero no demuestra ausencia de ciclos. Conserva protección y validación aunque necesites una profundidad mayor.

El predicado de ruta detiene silenciosamente una rama repetida. En informes administrativos, detecta y comunica ciclos por separado. Al mover un nodo, comprueba si el padre propuesto convertiría al nodo en su propio ancestro.

Empezar desde una raíz tampoco describe componentes desconectados. Compara los identificadores alcanzados con la población esperada al auditar la jerarquía completa. Un ciclo sin conexión con ninguna raíz puede quedar totalmente invisible. Un nodo ausente puede ser un defecto de datos y no de rendimiento.

Facilitar las búsquedas repetidas de hijos

En una tabla permanente, un índice que empiece por ParentId puede ayudar a encontrar hijos. Las columnas adicionales dependen de las consultas reales. La clave primaria sobre NodeId sirve para otra dirección y no acelera automáticamente todos los recorridos.

Mide número total de descendientes y anchura además de profundidad. Una estructura poco profunda con millones de hijos puede costar más que una cadena larga y estrecha. La ruta visitada también crece y se copia durante el recorrido; su protección no es gratuita.

Si son frecuentes las lecturas enormes de subárboles, considera hierarchyid, una tabla de cierre u otra representación mantenida. Esas opciones trasladan complejidad a las escrituras y a la consistencia. Evalúa el equilibrio completo y no una lectura aislada.

Prueba un nodo, hermanos, cadenas profundas, varias raíces, componentes separados y ciclos intencionados en datos desechables. Compara identificadores y profundidades esperadas. Una consulta correcta debe explicar lo que devuelve y por qué ciertos nodos almacenados quedan fuera.

Referencias técnicas: Microsoft Learn: Recursive queries · Microsoft Learn: MAXRECURSION.