Use of Function in Where clause

Hi,

Let's look at what happens when we use function in Where clause.

There are 2 places where we can use a function, 1st when we want to fetch some value in select clause and 2nd when we want to compare a value using a function in where clause.

Usage of both ways will affect the performance of the query.

Let's see a simple example of function.

Create a function that return Employee LastName, Firstname for passed Employee ID.

CREATE FUNCTION GetEmpName 
(
-- Add the parameters for the function here
@EmployeeId int
)
RETURNS NVARCHAR(100)
AS
BEGIN
-- Declare the return variable here
DECLARE @empname NVARCHAR(100)
SELECT @empname = LastName +', ' + FirstName FROM Employees WHERE EmployeeID=@EmployeeId

RETURN @empname
END
GO

Let's see how this works:

SELECT dbo.GetEmpName(9) AS Employee_Name

Dodsworth, Anne

SELECT distinct dbo.GetEmpName(a.EmployeeID), b.TerritoryDescription
FROM dbo.EmployeeTerritories a
INNER JOIN dbo.Territories b
ON a.TerritoryID=b.TerritoryID
WHERE dbo.GetEmpName(a.EmployeeID)='Davolio, Nancy'

Result

Employee TerritoryDescription
Davolio, Nancy Neward
Davolio, Nancy Wilton

Let's look at the profiler to see how the query is executing:

As we can see that the profiler has executed the function so many times, which actually slow down the query performance.

Let's change our function a little:

CREATE FUNCTION dbo.GetEmpNameCrossJoin 
(
 -- Add the parameters for the function here
 @EmpId int
)
RETURNS Table
AS
Return(
 SELECT LastName + ', ' + FirstName EName FROM dbo.Employees)
GO


--Now execute this using Cross APPLY

SELECT distinct I.EName, b.TerritoryDescription
FROM dbo.EmployeeTerritories a
INNER JOIN dbo.Territories b
ON a.TerritoryID=b.TerritoryID
CROSS APPLY dbo.GetEmpNameCrossJoin(a.EmployeeID) I

We have changed the function to return table instead of a scalar value and the function call is done using CROSS APPLY.

Now there is only 1 row in profiler.

This way we can improve the performance of the query.

Happy SQL Coding.