Those who have extensively worked on database development with SQL Server 2000 would have, at some point in time, come across a shortcoming of user-defined functions.  Delve further into this and you would agree too.

In SQL Server 2000 table-valued functions can be joined to a result set of another table expression.  However, table-valued functions cannot be invoked for each row returned by the joined table expression.  To understand this better let us discuss an example.  The example may not be a real world example, but sufficient enough to bring out the limitation we are discussing.

Say we have a table-valued function "fn_DepartmentEmployees (int DeptId)" that accepts department ID and returns the list of employees' names and salaries along with the department ID.

To get a list of employees' names and salaries for department #1 we simple pass a scalar input to the function call.

Listing 1

SELECT DeptId, EmpName, EmpSalary 
FROM fn_DepartmentEmployees (1)

To get the name of the department, we can extend the Listing 1 query by joining the function with the "Departments" table.

Listing 2

 
SELECT DeptName, EmpName, EmpSalary
FROM fn_DepartmentEmployees (1) de
JOIN Departments d ON d.DeptId = de.DeptId

We can even interchange the order of user-defined function and table in the Listing 2 query without any problems.

Now say we want a list of employees' names and salaries for all the departments at one time.  We simply think of extending the Listing 2 query further.

Listing 3

 
SELECT DeptName, EmpName, EmpSalary
FROM Departments d 
JOIN fn_DepartmentEmployees (d.DeptId) de ON d.DeptId = de.DeptId

When we execute the Listing 3 query we get an error because the table-valued function cannot be invoked for each record returned by the outer table in SQL Server 2000.

However, in SQL Server 2005 the APPLY clause comes to our rescue.

The APPLY clause in SQL Server 2005 can be used on a table-valued function to be invoked for each row returned by the outer table expression.  Unlike SQL Server 2000, the order of the operation does matter with the APPLY clause in SQL Server 2005.

The simplified syntax of APPLY clause (T-SQL) is as follows.

SELECT column1, column2…
FROM outer_table_expression ote
CROSS APPLY|OUTER APPLY table_valued_function (ote.join_column)

From the above syntax we can see there are two types of APPLY clause available in SQL Server 2005: CROSS APPLY and OUTER APPLY.

CROSS APPLY is similar to INNER JOIN.  It returns only rows from the outer table that produce a result set from the table-valued function.  The table valued function in the CROSS APPLY clause also acts the correlated sub-query where the sub-query is executed repeatedly for each row of the outer query.

Listing 4

SELECT DeptName, EmpName, EmpSalary
FROM Departments d 
CROSS APPLY fn_DepartmentEmployees (d.DeptId)

This query will list the employees' names and salaries in all the departments that have one or more employees.  If a department does not have any employees allocated then that department will not appear in the result set.  To include such departments requires use of OUTER APPLY clause.

OUTER APPLY is similar to LEFT OUTER JOIN.  It returns all rows from the outer result set with NULL values in the columns of the table-valued function that do not produce a result set for the outer row.

Listing 5

SELECT DeptName, EmpName, EmpSalary
FROM Departments d 
OUTER APPLY fn_DepartmentEmployees (d.DeptId)

Although this is a good feature, there is a performance issue associated with this enhancement.  If the outer table expression of the APPLY clause returns a huge result set, performance of the query may degrade due to the number of calls to the table-valued function.

[Download Sample]

In SQL Server 2005 we can apply the APPLY operator if a table-valued function needs to be invoked for each row retuned by the outer table expression.