SQL SERVER 2005 – APPLY clause

The APPLY clause
 
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.
 
Conclusion :
 
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.

———————
SAMPLES
———————

— Start environemt setup : To be executed in SQL Server 2000 and 2005
— Create a test database
CREATE DATABASE MyTestDatabase
GO

USE MyTestDatabase
GO

— Create Departments table
CREATE TABLE Departments
(
 DeptId int IDENTITY(1,1) NOT NULL PRIMARY KEY ,
 DeptName varchar(50)
)
GO

— Create Employees table
CREATE TABLE Employees
(
 EmpId int IDENTITY(1,1) NOT NULL PRIMARY KEY ,
 EmpName varchar(50) ,
 DeptId int CONSTRAINT FOREIGN_KEY REFERENCES Departments (DeptId) ,
 EmpSalary money
)
GO

SET NOCOUNT ON

— Populate Departments table with test data
INSERT INTO Departments (DeptName)
VALUES (‘HR’)

INSERT INTO Departments (DeptName)
VALUES (‘Payroll’)

INSERT INTO Departments (DeptName)
VALUES (‘Admin’)

— Populate Employees table with test data
INSERT INTO Employees (EmpName, DeptId, EmpSalary)
VALUES (‘John’, 1, 5000)

INSERT INTO Employees (EmpName, DeptId, EmpSalary)
VALUES (‘Albert’, 1, 4500)

INSERT INTO Employees (EmpName, DeptId, EmpSalary)
VALUES (‘Crain’, 2, 6000)

INSERT INTO Employees (EmpName, DeptId, EmpSalary)
VALUES (‘Micheal’, 2, 5000)

SET NOCOUNT OFF

GO

— Create udf fn_DepartmentEmployees that accepts department id and
— returns the list of employee’s name and salary along with the department id.
CREATE FUNCTION fn_DepartmentEmployees (@DeptId int)
RETURNS @DeptEmp TABLE (DeptId int, EmpName varchar(50), EmpSalary money)
BEGIN
 INSERT INTO @DeptEmp (DeptId, EmpName, EmpSalary)
 SELECT DeptId, EmpName, EmpSalary
 FROM Employees
 WHERE DeptId = @DeptId

 RETURN
END
GO

— End  environemt setup

/*
— Execute listing 1, 2 and 3 in SQL Server 2000 for understanding table-valued function limitation

— Listing 1
SELECT DeptId, EmpName, EmpSalary
FROM fn_DepartmentEmployees (1)
GO

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

— Listing 3
SELECT DeptName, EmpName, EmpSalary
FROM Departments d
JOIN fn_DepartmentEmployees (d.DeptId) de ON d.DeptId = de.DeptId
GO
*/
/*
— Execute listings 4 & 5 in SQL Server 2005 to understand the new APPLY construct
— that overcomes the SQL Server 2000 limitation of table-valued function

— Listing 4
— CROSS APPLY query
SELECT DeptName, EmpName, EmpSalary
FROM Departments d
CROSS APPLY fn_DepartmentEmployees (d.DeptId)
GO

— Listing 5
— OUTER APPLY query
SELECT DeptName, EmpName, EmpSalary
FROM Departments d
OUTER APPLY fn_DepartmentEmployees (d.DeptId)
GO
*/