CTEs recursivas

WITH Numeros(n)
AS
(
SELECT 1 AS n
UNION ALL
SELECT (n + 1) AS n
FROM Numeros
WHERE
n < 100
)

SELECT n from Numeros
order by n asc

Como medida de protección ante consultas cuyo nivel de recursividad puediera ser excesivo, SQL Server 2005 limita por defecto a 100 este número. Esto haría que la anterior consulta no funcionara si quisieramos obtener 200 números dándonos este error: “The statement terminated. The maximum recursion 100 has been exhausted before statement completion.” Para evitar este problema debemos indicarle el número máximo de llamadas recursivas en la consulta con MAXRECURSION:

WITH Numeros(n)
AS
(
SELECT 1 AS n
UNION ALL
SELECT (n + 1) AS n
FROM Numeros
WHERE
n < 200
)

SELECT n from Numeros
order by n asc
OPTION(MAXRECURSION 200)

Otro de los resultados que podemos obtener utilizando CTEs consiste en la concatenación recursiva de cadenas. Esto podría ser útil para recorrer jerarquías aplanadas en una tabla y crear una representación completa de la navegación. Veamos un ejemplo de concatenación y algunas peculiaridades a la hora de intentar hacer conteos de filas en las CTEs con @@rowcount y row_count():

create table prueba (i int, texto nvarchar(100))
insert into prueba values (1,'prueba1')
insert into prueba values (2,'prueba2')
insert into prueba values (3,'prueba3')
insert into prueba values (4,'prueba4')
insert into prueba values (5,'prueba5')
insert into prueba values (6,'prueba6')
insert into prueba values (7,'prueba7')

GO

WITH Concatena(i,texto,contador1,contador2) AS 
(
    SELECT i,texto,1, row_number() over(order by prueba.i)
    FROM prueba
    WHERE i = 7
    UNION ALL
    SELECT prueba.i,cast (prueba.texto+' ' + concatena.texto as nvarchar(100)), @@rowcount,row_number() over(order by prueba.i)
    FROM prueba INNER JOIN Concatena  
        ON prueba.i = concatena.i-1
)

SELECT i,texto,contador1,contador2,row_number() over (order by i) as contador3
FROM Concatena

Y el resultado de la consulta sería:

Como podemos ver obtenemos una lista de las cadenas concatenadas de forma recursiva. Si nos fijamos, vemos que las columnas contador1 y contador2 muestran siempre el valor 1. Con esto lo que se pretende mostrar es que el número de columnas afectadas (@@rowcount) muestra el valor de la última operación (el último insert) y que no se actualiza con el cálculo recursivo de la tabla. De la misma forma la evaluación de las función row_number() nos devuelve siempre 1 pues en cada “select” del caso general de la recursividad solo se genera una única fila en el resultado. Tan sólo la columna 3, que calcula el contador fuera de la CTE, muestra el valor esperado del contador.