Showing posts from February, 2009

SQL Server 2005 CROSS APPLY conversion

SQL Server 2005 provides a CROSS APPLY clause which in Oracle terms allows a correlated inline view or table function.

Heres some SQL Server CROSS APPLY background.

There are a couple of ways to convert this based on what exactly is trying to be done.
But here are a few methods.

There are 2 main uses of CROSS APPLY
1) CROSS APPLY with a table function
2) CROSS APPLY with a subquery (inline view in Oracleze)

1) CROSS APPLY with a table function
Jeff Smith put together this neat example of using a table function with CROSS APPLY

I expanded this example to try out a few different scenarios

drop table emails

create table emails (ID int primary key, EmailAddress varchar(100))
--added 2 rows which cant be processed by EmailParse
insert into emails
select 1,'' union all
select 2,'…