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.