Hace unos días, durante la Segunda Jornada del Pre-Summit de Solid Quality Mentors,  durante la charla de Greg Low sobre Microsoft StreamInsight mencionó la utilidad que brinda la cláusula CROSS APPLY agregada a T-SQL en la versión 2005. Por coincidencia, yo tenía sobre la mesa un ejemplo, porque necesitaba reproducirlo para solucionar una problemática en la preparación del DW en el que me encuentro trabajando. Quiero dar las gracias a Fernando Puyuelo de Informática Borsan , por autorizar la publicación del ejemplo.

Problemática

En aquel ejemplo, que data de un par de años, se trataba de un par de tablas con relación 1:M donde se registraban los datos de personas (1) y los números de teléfonos (M).  El objetivo era obtener el listado de cada persona con uno de sus teléfonos, cualquiera de ellos. Esta era la problemática.

Preparación del ejemplo

USE tempdb

DROP TABLE dbo.Persona

DROP TABLE dbo.Telefonos

BEGIN TRANSACTION

SET QUOTED_IDENTIFIER ON

SET ARITHABORT ON

SET NUMERIC_ROUNDABORT OFF

SET CONCAT_NULL_YIELDS_NULL ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

COMMIT

BEGIN TRANSACTION

GO

CREATE TABLE dbo.Persona

(PErsonaId int NOT NULL,

nombre char(20) NULL)  ON [PRIMARY]

GO

ALTER TABLE dbo.PErsona ADD CONSTRAINT

PK_PersonaId PRIMARY KEY CLUSTERED

(

PersonaId

) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

insert into dbo.persona (PersonaId, Nombre) values (1,’Fernando’)

insert into dbo.persona (PersonaId, Nombre) values (2,’Ana’)

insert into dbo.persona (PersonaId, Nombre) values (3,’JC’)

insert into dbo.persona (PersonaId, Nombre) values (4,’Susana’)

COMMIT

BEGIN TRANSACTION

GO

CREATE TABLE dbo.Telefonos

(TelefonoId int NOT NULL,

PersonaId Int NULL,

Telefono char(20) NULL)  ON [PRIMARY]

GO

ALTER TABLE dbo.Telefonos ADD CONSTRAINT

PK_TelefonoId PRIMARY KEY CLUSTERED

(

TelefonoId

) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

insert into dbo.Telefonos (TelefonoId, PersonaId, Telefono) values (1,1,’915550001′)

insert into dbo.Telefonos (TelefonoId, PersonaId, Telefono) values (2,1,’915550002′)

insert into dbo.Telefonos (TelefonoId, PersonaId, Telefono) values (3,1,’915550003′)

insert into dbo.Telefonos (TelefonoId, PersonaId, Telefono) values (4,2,’915550004′)

insert into dbo.Telefonos (TelefonoId, PersonaId, Telefono) values (5,2,’915550005′)

insert into dbo.Telefonos (TelefonoId, PersonaId, Telefono) values (6,2,’915550006′)

insert into dbo.Telefonos (TelefonoId, PersonaId, Telefono) values (7,3,’915550007′)

insert into dbo.Telefonos (TelefonoId, PersonaId, Telefono) values (8,3,’915550008′)

insert into dbo.Telefonos (TelefonoId, PersonaId, Telefono) values (9,4,’915550009′)

insert into dbo.Telefonos (TelefonoId, PersonaId, Telefono) values (10,4,’915550010′)

COMMIT

Veamos lo que tenemos hasta ahora:

select PersonaId, Nombre from Persona

select TelefonoId, Telefono from Telefonos

Esto es lo que contiene una consulta de las dos tablas. Todas las personas con todos sus teléfonos.

select dbo.Persona.PersonaId, dbo.Persona.Nombre, Telefonos.TelefonoId, Telefonos.Telefono

from dbo.persona JOIN dbo.Telefonos on (Telefonos.PersonaId = Persona.Personaid)

Posible solución utilizando sub-consuntas

Una posible solución, propuesta por el cliente, que es válida; pero algo compleja de escribir, especialmente si son muchas las columnas que hay que recuperar del lado (M) y no del todo óptima.

select dbo.Persona.PersonaId, dbo.Persona.Nombre,

(select TOP 1 Telefonos.TelefonoId from dbo.Telefonos WHERE Telefonos.PErsonaId = Persona.PersonaId),

(select TOP 1 Telefonos.Telefono from dbo.Telefonos WHERE Telefonos.PErsonaId = Persona.PersonaId)  from dbo.persona

Manos a la obra

Esta solución estaba muy bien encaminada. Me dieron todo lo que necesitaba, solamente faltaba crear una función en T-SQL que devolviera un tipo table. Esta función recibe como parámetro PersonaId y se dedica a ejecutar la subconsulta, la misma que habían propuesto; pero que devuelve de una vez todas las columnas que se necesitan.

create function dbo.DamePrimerDato (@PersonaId int)

returns table

as

return

select top 1 Telefonos.TelefonoId, Telefonos.Telefono

from dbo.Telefonos WHERE Telefonos.PErsonaId = @PersonaId

go

Posible solución empleando CROSS APPLY

Ahora viene lo más interesante. Con la cláusula CROSS APPLY en una única sentencia SELECT llamamos a la función que acabamos de mostrar y obtenemos el resultado esperado.

select dbo.Persona.PersonaId, dbo.Persona.Nombre, fdame.TelefonoId, fdame.Telefono

from dbo.persona CROSS APPLY dbo.DamePrimerDato (Persona.Personaid) as fdame

go

Ya está, limpio, legible cómodo de trabajar y obtenemos lo que necesitamos.

¿Más ventajas?

1.- La de cursores creados con T-SQL que veo en los clientes que se pueden eliminar empleando CROSS APPLY con una función que devuelva un dato tipo table. Los cursores en T-SQL, en el 99,99% son innecesarios y antinatura.

2.- Está optimizado, el motor entiende y aprovecha aquello que le es nativo. ¿Y si comparamos las dos posibles soluciones con el Plan de Ejecución Real?

Recuerdo:

Caso1:

select dbo.Persona.PersonaId, dbo.Persona.Nombre,

(select TOP 1 Telefonos.TelefonoId from dbo.Telefonos WHERE Telefonos.PErsonaId = Persona.PersonaId),

(select TOP 1 Telefonos.Telefono from dbo.Telefonos WHERE Telefonos.PErsonaId = Persona.PersonaId)  from dbo.persona

Caso2:

select dbo.Persona.PersonaId, dbo.Persona.Nombre, fdame.TelefonoId, fdame.Telefono

from dbo.persona CROSS APPLY dbo.DamePrimerDato (Persona.Personaid) as fdame

go

¿A que está bien? Por cierto, que ya solucioné con CROSS APPLY lo que necesitaba en mi DW !!!

Es todo por ahora, en la próxima seguiremos hablando de SSIS, específicamente de la tarea Ejecutar proceso.

Saludos,

Ana