SQL Server: Advantages and Drawbacks of User-Defined Functions
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
In the previous article in this series, I introduced you to user-defined functions (UDFs), which are excellent tools for certain programming tasks. This article discusses the advantages and limitations that are associated with UDFs, and shows you the coding tasks that qualify as good candidates for UDFs. Hope you find this advice helpful.
Advantages of UDFs
So why are UDFs important? What can you do with UDFs that you cannot with stored procedures? Well, UDFs are not functionally stronger than stored procedures (in fact, UDFs have many limitations, which we will examine shortly). However, UDFs do provide certain advantages, discussed in the following sections.
Execution Within the SELECT Statement
UDFs are executed within an inline query; for instance, you can call a scalar UDF that calculates the number of business days between two dates, as follows:
SELECT dbo.no_of_business_days(date1, date2)
The developer's ability to execute UDF within inline queries makes UDF easier to use than a procedure within other routines. If you had to perform the same calculation in a procedure, you'd have to return the value as an output parameter. Using output parameters isn't necessarily cumbersome, but would involve more coding, as follows:
DECLARE @no_of_business_days INT EXEC dbo.proc_for_calculating_business_days date1, date2, @no_of_business_days OUTPUT
Execution from Various Parts of SQL Statements
Scalar UDFs can be called from within the WHERE/HAVING clause of queries or even within a SET list of the UPDATE statement. For instance, the following UDF determines the total sales for a given customer within a particular year by using the Northwind database:
CREATE FUNCTION dbo.total_orders (@customerid CHAR(6), @year INT) RETURNS INT AS BEGIN DECLARE @total INT SELECT @total = SUM(unitprice * quantity) FROM [order details] a INNER JOIN orders b ON a.orderid = b.orderid WHERE DATEPART(YEAR, orderdate) = @year AND customerid = @customerID RETURN @total END
This function can be executed within a WHERE clause of the query returning customers who have purchased goods for more than $15,000 in 1996:
SELECT DISTINCT CustomerID FROM orders a INNER JOIN [order details] b ON a.orderid = b.orderid WHERE dbo.total_orders(customerID, 1996) > 15000
Results:
|
CustomerID |
|
ERNSH |
UDFs can also be used within a CASE statement; for instance, you can use total_orders UDF to rank customers according to their sales in 1996, as follows:
SELECT CompanyName, SalesRank_1996 = CASE WHEN dbo.total_orders(customerID, 1996) < 1000 THEN 'poor' WHEN dbo.total_orders(customerID, 1996) BETWEEN 1001 AND 5000 THEN 'average' WHEN dbo.total_orders(customerID, 1996) BETWEEN 5001 AND 13000 THEN 'good' ELSE 'super' END FROM customers
Results (abbreviated list):
CompanyName SalesRank_1996 ---------------------------------------- -------------- Alfreds Futterkiste super Ana Trujillo Emparedados y helados poor Antonio Moreno Taquería poor Around the Horn average Berglunds snabbköp average Blauer See Delikatessen super Blondesddsl père et fils good Bólido Comidas preparadas poor Bon app' average Bottom-Dollar Markets average
UDF Output Can Be Used as a Rowset
UDFs that return tables can be treated as another rowset. Thus, you can join other tables within your code module to the output of a UDF. For example, if you have a UDF returning two best-selling titles along with their authors, you can easily join it with another table, as follows:
SELECT
a.title,
a.author_name,
b.royalty_amount
FROM dbo.udf_bestselling_titles a INNER JOIN royalty b
ON a.author_id = b.author_id
If you had to use a stored procedure to do the same procedure, you'd have to do the following:
Define a temporary table with the CREATE TABLE statement.
Populate the temporary table with the output of the stored procedure.
Join the temporary table to other tables.
Drop the temporary table at the end of the routine.
Here is the code for a solution that uses a stored procedure instead of a UDF:
CREATE TABLE #temp_table (
Title_id INT,
Title VARCHAR(200),
Author_name VARCHAR(45)
)
INSERT #temp_table (
title_id,
title,
author_name )
EXECUTE dbo.usp_bestseller_titles
SELECT
a.title,
a.author_name,
b.royalty_amount
FROM #temp_table a INNER JOIN royalty b
ON a.author_id = b.author_i
UDFs as Parameterized Views
In-line UDFs can be thought of as views that accept parameters. This flavor of UDFs can be used effectively to return different data from the same tables based on the parameter values. For instance, the following UDF will return different categories of titles based on the supplied parameter:
CREATE FUNCTION dbo.udf_category_titles (@category VARCHAR(12)) RETURNS TABLE AS RETURN ( SELECT title, pub_id, price FROM titles WHERE type = @category)
In-line UDFs can also be used for security—they can return different data based on the user executing the UDF. For instance, the following UDF returns the hire date only if executed by the database owner:
CREATE FUNCTION dbo.udf_get_employee (@p1 INT) RETURNS TABLE AS RETURN ( SELECT emp_id, fname, lname, hire_date = CASE WHEN user_name() = 'dbo' THEN CAST(hire_date AS VARCHAR(12)) ELSE 'n/a' END FROM employee WHERE job_id = @p1 )
This UDF can be executed as follows:
SELECT * FROM dbo.udf_get_employee(13)
If executed by the database owner, the output is the following:
emp_id fname lname hire_date --------- -------------------- ------------------------------ ------------ PMA42628M Paolo Accorti Aug 27 1992 TPO55093M Timothy O'Rourke Jun 19 1988 CGS88322F Carine Schmitt Jul 7 1992
If executed by any other user, the output will contain "n/a" in the "hire_date" column.
Multi-Statement Functions: Alternatives to Stored Procedures
Multi-statement functions allow you to perform additional logic, such as variable declaration, populating of table variables, and updating values within it, looping through records and more. Multi-statement functions are a great way to rewrite a stored procedure if the following conditions are true:
The output of the stored procedure can be used by other routines.
Stored procedures don't make any data changes in the permanent tables.
The only purpose of the stored procedure is to create a result set that might have to be manipulated prior to returning it to the user.
Stored procedures do not perform any tasks prohibited inside the UDFs (see the next section for details).