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).
DonnierocK 