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

Anuncios

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión /  Cambiar )

Google photo

Estás comentando usando tu cuenta de Google. Cerrar sesión /  Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión /  Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión /  Cambiar )

Conectando a %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.