Common Table Expression - SQL Server

Una CTE (Common Table Expression - Expresión de Tabla Común), se puede considerar un conjunto de resultados temporal que se define en el ámbito de la ejecución de una instrucción única SELECT, INSERT, UPDATE, DELETE o CREATE VIEW. Una CTE es similar a una tabla derivada en que no se almacena como un objeto y dura sólo el tiempo que dura la consulta. A diferencia de una tabla derivada, una CTE puede hacer referencia a sí misma y se puede hacer referencia a ella varias veces en la misma consulta.

Al igual que una TVF nos ayuda entre otras cosas a simplificar las consultas grandes, teniendo una mejor visión de la consulta entera.  Las CTE's, también cumplen este objetivo, pero su tiempo de vida es mucho más corto, ya que no se guarda como un objeto de la base de datos, sino que dura el ámbito de la ejecución de la instrucción, la cual puede estar contenida en un SP (Store Procedure) o una consulta general.

En mi caso, ¿dónde las he utilizado? Cuando debo hacer una consulta bastante extensa, que requiere que filtre información sobre datos ya filtrados, básicamente implementando recursividad o reutilizando la consulta anterior. Para los que han programado con Visual FoxPro, es equivalente al manejo de Cursores, con los cuales, la información se obtenía en una consulta (Cursor) y luego ese cursor se podía filtrar y/o utilizar para otra operación.

El uso de una CTE ofrece las ventajas de una legibilidad mejorada y la facilidad de mantenimiento de consultas complejas. La consulta se puede dividir en bloques de construcción lógicos simples e independientes. Estos bloques simples se pueden usar para crear CTE provisionales más complejas hasta que se genere el conjunto de resultados finales.

Las CTE se pueden definir en rutinas definidas por el usuario, como funciones, procedimientos almacenados, desencadenadores o vistas.

Sintaxis

WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )
 

Al importante que debo destacar es la utilización de un punto y coma (;) al inicio del CTE, ya que SQL Server enviará un error indicando que la última consulta anterior al CTE debe terminar con ;

También se pueden anidar CTE, con la siguiente sintaxis.

;With C1 AS
(
 Select ...
 From T1 
 Where ...
),
C2 AS
(
 Select ...
 From C1 
 Where ...
)
Select ...
  From C2
Where ...;

Ejemplos

En el siguiente ejemplo se muestra el número total de pedidos de venta por año para cada representante de ventas en Adventure Works Cycles:

;WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;

Más ejemplos por parte de Microsoft, pueden tener en este link.

Un ejemplo práctico, es este SP que realice a inicio del 2014, en el cual tenía que extraer las facturas pendientes de pago, pero por mes.

-- =============================================
-- Author:  Ing. DEBS
-- Create date: 13/02/2014
-- Description: Devuelve por mes (año actual)
--    las facturas pendientes de los proveedores
-- =============================================
ALTER PROCEDURE [Exo].[GetFCxPrvxYear]
 @cYear varchar(4)=''
AS
BEGIN
 /*Declare @cYear as varchar(4);
 Set @cYear = Convert(varchar(4),Year(GetDate()));
 */
 IF @cYear = ''
 BEGIN
  Set @cYear = Convert(varchar(4),Year(GetDate()))
 END

 ;With CTE1  --Llama la CTE y sus valores y convierte a C$ todo
  as
 (
 Select cDescPrv,cCodPrv,
  case when cCodMon='C$' THEN nMonto ELSE Round(nMonto * nTC,2) end as nMonto,
  case when cCodMon='C$' THEN nExonerado ELSE Round(nExonerado * nTC,2) end as nExonerado,
  case when cCodMon='C$' THEN nSaldo ELSE Round(nSaldo * nTC,2) end as nSaldo,
  left(YearMes,4) as cYear,Right(YearMes,2) as cMes
  from Exo.GetFCSaldos() 
  where nSaldo<>0
  and nMonto>=0 and nExonerado>=0 and nSaldo>=0
 ),-- CTE2 Ubica cada valor en el mes correspondiente
 CTE2 (cDescPrv,cCodPrv,Saldo_Ant,Ene,Feb,Mar,Abr,May,Jun,Jul,Ago,Sep,Oct,Nov,Dic) 
  as
 (
  Select cDescPrv,cCodPrv,
   case when cYear < @cYear then Sum(nSaldo) ELSE 0.0 END,
   Case when cMes = '01' and cYear = @cYear then Sum(nSaldo) else 0.00 end,
   Case when cMes = '02' and cYear = @cYear  then Sum(nSaldo) else 0.00 end,
   Case when cMes = '03' and cYear = @cYear  then Sum(nSaldo) else 0.00 end,
   Case when cMes = '04' and cYear = @cYear  then Sum(nSaldo) else 0.00 end,
   Case when cMes = '05' and cYear = @cYear  then Sum(nSaldo) else 0.00 end,
   Case when cMes = '06' and cYear = @cYear  then Sum(nSaldo) else 0.00 end,
   Case when cMes = '07' and cYear = @cYear  then Sum(nSaldo) else 0.00 end,
   Case when cMes = '08' and cYear = @cYear  then Sum(nSaldo) else 0.00 end,
   Case when cMes = '09' and cYear = @cYear  then Sum(nSaldo) else 0.00 end,
   Case when cMes = '10' and cYear = @cYear  then Sum(nSaldo) else 0.00 end,
   Case when cMes = '11' and cYear = @cYear  then Sum(nSaldo) else 0.00 end,
   Case when cMes = '12' and cYear = @cYear  then Sum(nSaldo) else 0.00 end
  from CTE1
  group by cDescPrv,cCodPrv,cYear,cMes
 ) --Se procede a sumar todo y listo
 Select cDescPrv,cCodPrv,Sum(Saldo_Ant) as Anterior,
  Sum(Ene) as Enero,
  Sum(Feb) as Febrero,
  Sum(Mar) as Marzo,
  Sum(Abr) as Abril,
  Sum(May) as Mayo,
  Sum(Jun) as Junio,
  Sum(Jul) as Julio,
  Sum(Ago) as Agosto,
  Sum(Sep) as Septiembre,
  Sum(Oct) as Octubre,
  Sum(Nov) as Noviembre,
  Sum(Dic) as Diciembre,
  Sum(Saldo_Ant + Ene + Feb + Mar + Abr + May + Jun + Jul + Ago + Sep + Oct + Nov + Dic) as Total
 from CTE2
 where
  (Saldo_Ant + Ene + Feb + Mar + Abr + May + Jun + Jul + Ago + Sep + Oct + Nov + Dic)<>0
  group by cDescPrv,cCodPrv
  order by cDescPrv,cCodPrv
END

Una ejecución a este SP, es algo así:

Conclusión

Las CTE's son muy útiles para poder aplicar recursividad a la información que queremos filtrar, esto es, aplicar ciertas operaciones (o filtro) sobre un conjunto de registros que previamente extrajimos (o consultamos) de la BD.

Como verán en el ejemplo que puse ya al final, utilicé dos CTE, la primera (CTE1) se encarga de poner todos los montos en moneda nacional (de no estarlo), a partir de los datos de una TVF (Exo.GetFCSaldos()) así como filtrar únicamente los registros que aún tienen saldo, además de poner los dos primeros dígitos del mes, luego en la segunda CTE (CTE2) se realiza la sumatoria por mes de cada registro del año correspondiente, asignándoles el nombre del mes.  Al final, se muestran los datos, a partir de la CTE2 y se totaliza por registros.  Puede que la solución es bastante compleja y existen métodos más sencillos, pero traté de ponerles una aplicación práctica de CTE.

Para más información sobre CTE's, pueden visitar los siguientes sitios:

• TechNet
• CodeProject
• MSDN

Gracias por haber tomado el tiempo de leer este artículo, espero te sea de utilidad y de serlo, favor compartirlo.  Igual, si tenés alguna duda, comenta en esta publicación.

"Enseñar es la mejor forma de Aprender"