CROSS APPY SQL 2005 Y 2008

El día de hoy comparto con ustedes una instrucción de SQL Server 2005 que me ha sido de mucha ayuda al momento de crear tablas compuestas: CROSS APPLY.

La finalidad de este articulo es mostrar algunas de sus aplicaciones, dando oportunidad al lector investigar a fondo la instrucción. Al final del articulo dejo algunas ligas de interés con más información al respecto.

Como habremos de imaginar por su nombre, la instrucción CROSS APPLY nos permite crear una tabla compuesta (un set de resultados combinando columnas de dos o más tablas ).

La principal caracteristica de esta instrucción a diferencia de las instrucciones JOIN (CROSS JOIN, INNER JOIN, OUTER JOIN) es que nos permite generar una tabla compuesta a partir de una tabla y una función de tipo tabla.

¿Y esto para que me sirve dirían muchos?, pues de bastante, pongo un ejemplo bastante sencillo que creo que se demostrará la capacidad de esta instrucción.

Ejemplo


Tabla: ModificacionesFactura

Factura	FechaCreacion	FechaCorte
1001	10/10/2012	10/10/2011
1001	11/10/2012	10/10/2013
1003	12/10/2012	10/10/2012

Supongamos que necesitamos obtener la fecha menor entre el campo FechaCreacion y el campo FechaCorte:

Para lograrlo podríamos hacer algo así

select Factura,
case when FechaCreacion < FechaCorte then FechaCreacion
else FechaCorte end FechaMenor
From ModificacionesFactura

Hasta aquí todo bien, sin problemas. Ahora a poner una pequeña traba, ¿qué pasa si queremos hacer algo más con la FechaMenor que acabamos de obtener?, como por ejemplo saber la diferencia de días entre el día de hoy y la fecha menor. En este caso tendríamos que repetir la instrucción para obtener de nuevo la fecha menor y obtendríamos algo como esto:

select Factura
case when FechaCreacion &lt FechaCorte then FechaCreacion
else FechaCorte end FechaMenor,
DATEDIFF(dd,case when FechaCreacion < FechaCorte then FechaCreacion
else FechaCorte end FechaMenor, GETDATE()) Diferencia
From ModificacionesFactura

Alternativa con CROSS APPLY

Con el uso de CROSS APPLY podríamos reducir la consulta anterior de esta manera:

SELECT Factura,
FechaMenor,
DATEDIFF(dd, A.FechaMenor, GETDATE()) Diferencia
FROM ModificacionesFactura
CROSS APPLY
(
     SELECT case when FechaCreacion < FechaCorte then FechaCreacion            
     else FechaCorte end FechaMenor
) A

De esta manera queda más limpio nuestro SELECT ya que pasamos al cálculo de la fecha menor al CROSS APPLY. Incluso podemos utilizar el campo A.FechaMenor en la clausula Group By directamente lo cual, de otra manera nos llevaría muchas más lineas de código.

La instrucción CROSS APPLY ejecuta para cada  registro de la tabla, la función de tipo tabla que especificamos. En este caso la función regresa un solo registro para cada registro de la tabla, pero podría darse el caso en el que regrese más de uno. Incluyo una liga donde se puede ver el ejemplo fácilmente:

http://www.sqlteam.com/article/using-cross-apply-in-sql-server-2005


Limpiando todavía más la consulta

Esta instrucción puede quedar todavía más limpia si pasamos la lógica para obtener la fecha menor a una función de tipo tabla.

La cual podría quedar de la siguiente manera:

CREATE FUNCTION dbo.ObtenerFechaMenor(@Fecha1 as datetime, @Fecha2 as datetime)
RETURNS Table
AS
RETURN
SELECT case when @Fecha1 < @Fecha2 then @Fecha1
             else @Fecha2 end FechaMenor
GO 

Nuestro query podría quedar de la siguiente manera:

SELECT Factura,
            FechaMenor,
            DATEDIFF(dd, A.FechaMenor, GETDATE()) Diferencia
FROM ModificacionesFactura
CROSS APPLY dbo.ObtenerFechaMenor(FechaCreacion, FechaCorte) A

Alguien podría decir, ¿para que uso funciones definidas por el usuario si no son muy óptimas al momento de consultar grandes cantidades de registros?, y esto es verdad, pero un detalle interesante de la instrucción CROSS APPLY es que, las instrucciones dentro de la función se toman al generar el plan de ejecución de nuestro query, lo cual aumenta la velocidad de nuestra consulta en la mayoria de los casos. Cabe aclarar que siempre debemos realizar pruebas de desempeño comparando diversas opciones para que tengamos argumentos de porque realizamos la consulta de tal o cual manera.

Espero que les haya gustado este breve artículo y les haya dado curiosidad para investigar más acerca de esta instrucción. Para mi ha sido muy útil al momento de reutilizar y reducir código, algo que a veces en SQL Server es un poco complicado de hacer. 

Si les gusta compartan este articulo y dejen sus puntos de vista.

Hasta luego.

Carlos E. Rodríguez

ligas de interés:

http://geeks.ms/blogs/lfranco/archive/2011/09/26/funciones-escalares-en-tsql-joins-cross-apply-y-la-madre-que-pari-243-al-topo.aspx

http://social.msdn.microsoft.com/Forums/en/transactsql/thread/796821b0-ba48-49b4-ba42-1783061d8c41 

http://amby.net/2010/03/29/ejemplo-de-empleo-de-clausula-cross-aply-en-t-sql-2005-y-superior/ 

http://msdn.microsoft.com/es-es/library/ms175156%28v=sql.105%29.aspx