Common Table Expression (CTE): la sentencia WITH de SQL-Server

Las expresiones de tabla común (common table expression o CTE) fueron añadidas por Microsoft a SQL-Server a partir de la versión 2008. Cuando hablamos de CTE hablamos de un artefacto que nos mantiene en memoria el resultado de una consulta, que podremos llamar luego dentro de esa misma consulta. Esta cláusula también se puede utilizar en una instrucción CREATE VIEW como parte de la instrucción SELECT que la define.

Sintaxis de una CTE

La sintaxis básica de una CTE sería la siguiente:

WITH nombreDeNuestra_CTE (Columna1, Columna2, Columna3)  
AS  
-- Aquí definimos la consulta que la crea.  
(  
    SELECT Columna1, Columna2, Columna3
    FROM BaseDatos.dbo.Tabla
    Where Condicion=Condicion
)  

Por ejemplo, imagina que tenemos una tabla con vendedores y otra con sus ventas, donde el Id del vendedor actúa como clave externa en cada línea de ventas. Podemos usar una CTE para tener un resultado temporal con su total de ventas de 2018. Algo así:

WITH Ventas_CTE (IdVendedor, NombreVendedor, TotalVendido)  
AS  
(  
    SELECT 
      v.Id as IdVendedor, 
      v.Nombre as NombreVendedor, 
      SUM(vt.Importe) AS TotalVentas 
    FROM Vendedores v inner join ventas vt on v.Id = vt.IdVendedor
    WHERE YEAR(vt.Fecha)=2018
)  

Ahora podríamos usar la CTE del ejemplo de arriba en una consulta como si de una tabla normal se tratase. Por ejemplo, podríamos sacar todos los departamentos donde haya vededores que hayan superado los 100.000 euros en ventas haciendo un join contra una tabla de departamentos:

SELECT Distinct
  d.Nombre
FROM
  Departamentos d
inner join
  Departamentos_Vendedores vd
on
  d.Id = vd.Idep
inner join
  Ventas_CTE v
on
  v.IdVendedor = vd.IdVend
where
  vd.TotalVendido > 100000

Hay que recordar que las cláusulas INTO, ORDER BY, FOR BROWSE y OPTION no están permitidas en una consulta de definición de una CTE. Sí permite el uso de operadores de conjuntos como UNION ALL, UNION, INTERSECT o EXCEPT y también permite referencias a tablas externas, e incluso a tablas situadas en servidores remotos.

En el ejemplo hemos usado un SELECT después de crear nuestra CTE, pero podríamos haber usado una instrucción INSERT, DELETE o UPDATE también. Incluso la podemos utilizar para definir un cursor.

¿Por qué usar una CTE?

Aparte del potencial que tienen las CTE por permitir crear una CTE recursiva, cosa de la que ya hablaremos más adelante en otro artículo, las CTE principalmente nos permiten sustituir a subconsultas y a variables de tabla.

En el caso de las subconsultas realmente la CTE no nos da ninguna ventaja en el rendimiento, pero sí nos permitirá tener un código más ordenado y más manejable, lo que facilita la legibilidad del mismo y las tareas de mantenimiento.

En el caso de la variables de tipo tabla ahí sí que las CTE nos dan un rendimiento mejor en la consulta, por lo que siempre serán una opción más recomendable.

También uno podría pensar en cambiar una Vista por una CTE. Si se trata de conjuntos de datos muy grandes, con muchas líneas, la Vista tendrá un rendimiento mayor por tratarse de un objeto creado en la base de datos que no permite definir índices (ya hablamos aquí sobre cuándo esto es recomendable y cuándo no).

Usando librerías .dll de .NET en SQL-Server2008

Aunque normalmente no se recomiende tener la lógica de negocio en el lado del servidor de la base de datos, en algunos casos puede resultar interesante el hacerlo. Microsoft, en su empeño (por otra parte loable) de lograr una integración total entre sus servicios, nos permite utilizar nuestras .dll creadas en .NET (sea en VB.NET o en C#.NET o en C++) dentro de nuestra base de datos SQL-Server como “código gestionado”.

Iré construyendo poco a poco un ejemplo para que veáis, paso a paso como se realiza esto. Lo primero es activar clr para que nos permita la integración con .NET tal que así:

USE [basedatosdeejemplo]
go
sp_configure 'clr enabled', 1
go
reconfigure
go

Seleccionamos la base de datos a utilizar, configuramos clr como ‘enabled’ (armado, activado) con el parámetro 1 y ejecutamos reconfigure para que el cambio tenga efecto. Acuérdate de poner el parámetro (en este caso 1) y de ejecutar reconfigure.

El primer paso está realizado. Lo siguiente es crear el ensamblado para la librería, definiendo el esquema y el tipo de permisos.


CREATE ASSEMBLY [Utilidades]
AUTHORIZATION [dbo]
FROM 'C:\LibreriaEjemplo.dll'
WITH PERMISSION_SET = SAFE
GO

Creamos el ensamblado con el nombre “Utilidades” (podemos darle el que queramos mientras no sea una palabra reservada), con el esquema dbo (podríamos haber utilizado cualquiera de los disponibles), en FROM le especificamos la dirección del archivo en disco mediante una cadena de texto con la ruta y finalmente los permisos, en este caso SAFE.

SAFE es el permiso más restrictivo que hay, el más “seguro” para nuestro equipo pues limita mucho lo que pueda hacer la librería. En caso de que uses liberías de terceros es el permiso que te reportará más seguridad. Existe también el permiso EXTERNAL_ACCESS, que permite que el código acceda a ciertos recursos externos (registro, archivos, red) y el permiso UNSAFE, que da control sin restricciones a la librería sobre los recursos de la máquina. Si usas una librería propia puedes usar UNSAFE, pero si usas una de un tercero piensa que pueden entrañar riesgos de seguridad.

Con lo puesto ya tenemos la librería disponible en nuestra base datos. ¿Y ahora qué? Ahora simplemente puedes usar las clases y métodos de dicha librería en tu base. Puedes crear tus tipos de datos propios usando sus objetos, incluir sus métodos en procedimientos o triggers. En este ejemplo vamos a suponer que la librería importada tiene una clase Point que guarda las coordenadas de un eje X y un eje Y junto a un valor booleano, y tiene también una función GetCoordsAsText que muestra un mensaje largo con las coordendas. Vamos a crear un tipo de datos Point, usarlo en una tabla, acceder a los valores y usar el método GetCoordsAsText en una función. Pondré comentarios para ir explicando el proceso

--Creamos el tipo de datos Point como un objeto de la clase Point.
--Para acceder a la clase point tenemos que usar el método external name de SQL-Server
--accediendo a la clase mediante el nombre del assembly que creamos antes y el namespace de la clase (en 
--este caso point)
create type Point
external name Utilidades.Point 
go

--Creamos la tabla

create table dbo.Points
(
	id int identity primary key,
	valor Point
);
go

--Para acceder a los datos de la tabla debemos usar un método que nos devuelva
--los valores almacenados dentro del objeto en forma de texto (en este caso .X para el valor X, .Y para 
--el valor Y o .ToString() para sacar ambos en una columna como texto).

select id,
			valor.X as X,
			valor.Y as Y,
			valor.ToString() as Completo
from dbo.Points

--Si intentáramos acceder al objeto a pelo, como voy a poner debajo, nos devolvería el valor del objeto
--sin convertir (un churrazo con la dirección de memoria del puntero)

select valor from dbo.Points --así nos saldría un churo tipo 0x0000000100020000303000000

--También podríamos usar los métodos de la librería en una función, trigger, función de agregado,
--cursor... En este caso haremos una función que acceda a GetCoordsAsText (que está en la clase 
--UserFunctions)

create function dbo.CoordenadasComoTexto
returns nvarchar(50)
as
    external name Utilidades.UserFunctions.GetCoordsAsText

Y con esto tendríais la función que simplemente ejecuta el método definido en la dll y un tipo de datos igual al objeto. Las posibilidades de esto son muy grandes, así que podéis ir profundizando.