Wednesday, January 17, 2007

Apply Command : New in SQL Server 2005

Apply command can be used with a table-valued user defied function (UDF) so that the UDF accepts a different parameter value for each corresponding row being processed by the main query. There are two ways to apply, apply command.
(a) Cross Apply :- It is bit confusing because it has a contradictory behavior from name. ie. it operates more like an inner join tan a cross join. The Cross Apply command will join data from the main query with any table-valued data sets from the UDF. If no data is returned from the UDF, then the row from the main query is also not returned. So, its just behave like inner join
(b) Outer Apply :- It will return all the rows from main query , if at all no data is returned from the UDF. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.

It is well explained in BOL with perfect example. But thing is that you must know something like this exists in SQL Server 2005.

No comments:

Locations of visitors to this page