Gestión de jerarquías en SQL Server(II) : CTE’s

Otra forma de gestionar las jerarquías que proporciona SQL Server son los Common Table Expression (en adelante CTE). Los CTE son expresiones similares a las vistas o a las consultas derivadas (subconsultas) que permiten crear una consulta temporal que puede ser referenciada desde una instrucción DML. Básicamente este tipo de expresiones es muy similar a los miembros calculados que se definen en MDX, claro está, teniendo en cuenta que se trata de un lenguaje relacional. Esto anterior lo digo porque se usa de la siguiente forma

WITH nombre_expresion [ ( nombre_columna [ ,...n ] ) ]
AS ( definicion_contenido_cte )
Instruccion DML

Como entiendo que se ve mucho más claro con un ejemplo, os presento el siguiente:

Lo que se quiere hacer es obtener el máximo en la lista de precios consolidada de productos en stock. Esto se haría en SQL Server 2005 como sigue

SELECT p.IDSubcategoria, s.Nombre, SUM(Precio) AS SumaPrecios
INTO #Temp1
FROM Producto p
JOIN SubcategoriaProducto s ON s.IDSubcategoria =
p.IDSubcategoria
WHERE p.IDSubcategoria IS NOT NULL
GROUP BY p.IDSubcategoria, s.Nombre

SELECT IDSubcategoria, Nombre, MAX(SumaPrecios)
FROM #Temp1
GROUP BY IDSubcategoria, Nombre
HAVING MAX(SumaPrecios) = (SELECT MAX(SumaPrecios) FROM #Temp1)

DROP TABLE #Temp1

Como vemos lo que hace es sumar los precios de cada lista agrupados por lista y después obtener el máximo. Con los nuevos CTE esta query quedaría de la siguiente forma:

WITH ListaProductos (IDSubcategoria, Nombre, SumaPrecios) AS
(
SELECT p.IDSubcategoria, s.Nombre, SUM(Precio) AS SumaPrecios
FROM Producto p
JOIN SubcategoriaProducto s ON s.IDSubcategoria =
p.IDSubcategoria
WHERE p.IDSubcategoriaIS NOT NULL
GROUP BY p.IDSubcategoria, s.Nombre
)
SELECT IDSubcategoria, Nombre, MAX(SumaPrecios)
FROM ListaProductos
GROUP BY IDSubcategoria, Nombre
HAVING MAX(SumaPrecios) = (SELECT MAX(SumaPrecios) FROM ListaProductos)

Este es un ejemplo muy simple de cómo funcionan, aunque a continuación se verá cómo aprovechar más esta nueva característica.

Existen dos tipos de CTE en SQL Server: los recursivos y lo no recursivos. Con los primeros es posible manejar jerarquías, aunque no con la misma potencia que se propone en el post Gestión de jerarquías en SQL Server(I).

Mediante los CTE recursivos es posible crear consultas que devuelvan los datos estructurados jerárquicamente, es decir, podemos construir una jerarquía de elementos idpadre-idelemento-nivel a partir de tablas que no contienen esta información directamente. Como más claro se va a ver es en siguiente ejemplo:

Supongamos que tenemos una tabla con productos que contienen las columnas de IdProducto y otra con la relación con su elemento padre. El nivel es relativamente costoso calcularlo si se realiza de forma tradicional. Por ello, si empleamos una CTE tal como esta

WITH JerarquiaProductos(IdProductoPadre, IdProducto, Nombre, Nivel) AS
(
    SELECT IdProductoPadre, IdProducto, NombreProducto, 0 AS Nivel
    FROM Productos
    WHERE IdProductoPadre IS NULL
    UNION ALL
    SELECT p.IdProductoPadre, p.IdProducto, p.NombreProducto, Nivel + 1
    FROM Productos p
        INNER JOIN JerarquiaProductos j
        ON p.IdProductoPadre = j.IdProducto
)
SELECT IdProductoPadre, IdProducto, Nombre, Nivel
FROM JerarquiaProductos

vamos a obtener los siguientes resultados:

IdProductoPadre	IdProducto	Nombre	Nivel
Null	1	Coche	0
1	2	Motor	1
1	3	Chasis	1
2	4	Filtro aceite	2
…

En el caso de que nuestra jerarquía disponga de multitud de niveles debemos especificar el parámetro MAXRECURSION con el fin de que no colapsemos el sistema. Su valor puede estar entre 1y 32757.

Como vemos, esta característica nos permite representar una jerarquía a partir de orígenes que no contienen esta estructura directamente, junto a operaciones simples sobre los elementos de ella. Pero si queremos un poco más, esta técnica se queda corta.

Gestión de jerarquías en SQL Server(I) : hierarchyid

En Microsoft SQL Server 2008 se han introducido mejoras respecto a la versión 2005. Una de ellas es el nuevo tipo de dato llamado hierarchyId. Con este nuevo tipo de dato es posible organizar las entidades jerárquicas de una manera mucho más sencilla.

HierarchyId no es un tipo propio de SQL Server (como pudiera ser int o varchar), ya que se trata de un tipo del CLR propio del sistema. Por ello, dispone de una serie de funciones (http://technet.microsoft.com/en-us/library/bb677193.aspx) con las que se puede leer de diferentes niveles, obtener un nivel, etc. Tareas estas que eran mucho más complicadas en versiones anteriores de SQL Server.

Sí, sí. Todo esto está muy bien pero, ¿cómo funciona de verdad?

En el siguiente ejemplo se crea una tabla y se manejan datos con ella.

CREATE TABLE Geografia
(elemento hierarchyid PRIMARY KEY CLUSTERED,
nivel AS elemento.GetLevel() PERSISTED,
id_elemento INT UNIQUE,
nombre_elemento VARCHAR(30) NOT NULL)

Como vemos el campo “elemento”, primary key, es de tipo hierarchyid, lo que simplifica el cálculo del campo “nivel”, puede calcularse de manera directa (además para este campo se introduce la palabra clave “PERSISTED” que lo almacena permanentemente en el campo de forma que no se recalcula cada vez que se realiza una consulta sobre la tabla).

A la hora de insertar datos se realizaría de la siguiente forma

INSERT INTO Municipios VALUES (hierarchyid::GetRoot(),1,'España')

y para otras filas que no sean la raíz

DECLARE @NodoEspana hierarchyid

Para recuperar el nodo principal podemos usar

SELECT @NodoEspana =elemento FROM Municipios WHERE id_municipio=1

O también

SELECT @NodoEspana = CAST('/' AS hierarchyid)

Y, ¿Cómo insertamos las filas restantes de la tabla?

INSERT INTO Employees VALUES(@NodoEspana.GetDescendant(NULL, NULL),2, 'Madrid')

INSERT INTO Employees VALUES(@NodoEspana.GetDescendant(NULL, NULL),3, 'Asturias')

Para el caso de nodos de nivel 2

DECLARE @NodoMadrid hierarchyid

SELECT @NodoMadrid = CAST('/1/' AS hierarchyid)

INSERT INTO Employees VALUES(@NodoMadrid.GetDescendant(NULL, NULL),4, 'Madrid ciudad')

O también

INSERT INTO Employees VALUES(@NodoMadrid.GetDescendant((SELECT elemento FROM Geografia WHERE id_elemento=2), NULL),4, 'Madrid ciudad')

Como vemos, mediante la función GetDescendant podemos insertar filas, siendo los dos parámetros que acepta los niveles donde queremos que inserte el nuevo elemento.

Y, ¿cómo se muestran y manejan los datos de una consulta?

SELECT elemento.ToString() AS Jerarquia,
elemento AS NodoBinario,
nivel,
id_elemento,
nombre_elemento
FROM Geografia

Esto nos devuelve los siguientes registros

Jerarquia	NodoBinario	nivel	Id_elemento	Nombre_elemento
/	0x	0	1	España
/1/	0x58	1	2	Madrid
/2/	0x68	1	3	Asturias
/1/1/	0x5AC0	2	4	Madrid ciudad

Así, hemos creado un árbol jerárquico con ramas asimétricas, es decir, que no tienen la misma profundidad.