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.

 

0 Shares:
2 comments
Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

You May Also Like
Leer más

Backups y restores “al vuelo” sin almacenamiento intermedio

Seguramente los más “senior” recordarán la posibilidad que existía en versiones SQL Server antiguas de realizar backups utilizando named pipes. Cuando hablo de versiones antiguas, me refiero a “antiguas de verdad”, ya que esta funcionalidad fue marcada como obsoleta en SQL Server 7, se mantuvo en SQL 2000 pero ya se eliminó de SQL Server 2005 y posteriores.
Leer más

Data Masking de datos sensibles… piénsalo dos veces

Dynamic data masking (enmascaramiento) es una técnica que busca limitar/ocultar información sensible sin requerir cambios en las aplicaciones. Los datos en la base de datos realmente no se modifican, se alteran “al vuelo” de forma que cuando las consultas devuelven resultados se aplican las máscaras apropiadas. Esto hace que esta funcionalidad sea sencilla de implementar ya que no requiere cambios sustanciales y sea bastante transparente para las aplicaciones que utilizan los datos enmascarados.