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.
