Understanding APPLY Clause in SQL Server 2005
page 2 of 6
by Sachin Dedhia
Feedback
Average Rating: 
Views (Total / Last 10 Days): 30030/ 97

The Limitation

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.


View Entire Article

User Comments

Title: The Apply Clause   
Name: Shailesh
Date: 2010-09-16 9:51:08 AM
Comment:
The presentation is better as compared to the MSDN article.

Small and Sweet!

Thanks
Title: APPLY clause   
Name: Vivek
Date: 2009-07-08 11:24:18 AM
Comment:
Excellent Explanation..
Title: The APPLY clause   
Name: Sachin Dedhia
Date: 2009-05-26 4:14:26 PM
Comment:
Still cannot see why is apply clause any better than join for SQL 2005
Thanks
Title: user   
Name: user
Date: 2009-04-24 1:22:09 PM
Comment:
Beautifully explained
Title: "Using APPLY" MSDN article   
Name: Michael Freidgeim
Date: 2009-03-02 4:19:54 PM
Comment:
You should add reference to "Using APPLY" MSDN article http://msdn.microsoft.com/en-us/library/ms175156.aspx






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-07-22 5:12:49 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search