CTEs recursivas

Con la versión 2005 de SQL Server se ha añadido la funcionalidad de crear CTE (Common Table Expression)  así como CTE recursivas.

Esta funcionalidad, existente en la ISO-ANSI SQL3 (pero no implementada en SQL Server hasta ahora), nos permitirá crear estas tablas a partir de uniones recursivas.

Con la recursividad se nos abren nuevas puertas para solucionar problemas sin necesidad de recurrir a cursores o estructuras iterativas en T-SQL (usando while por ejemplo)

Por ejemplo con una función recursiva podemos crear fácilmente la tabla de números del 1 al 100:

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:

 

 

i	texto	contador1	contador2	contador3
1	prueba1 prueba2 prueba3 prueba4 prueba5 prueba6 prueba7	1	1	1
2	prueba2 prueba3 prueba4 prueba5 prueba6 prueba7	1	1	2
3	prueba3 prueba4 prueba5 prueba6 prueba7	1	1	3
4	prueba4 prueba5 prueba6 prueba7	1	1	4
5	prueba5 prueba6 prueba7	1	1	5
6	prueba6 prueba7	1	1	6
7	prueba7	1	1	7

 

 

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.