SQL Server: Advantages and Drawbacks of User-Defined Functions

• By Baya Dewald
• May 16, 2003

Like this article? We recommend
Microsoft SQL Server 2000 DBA Survival Guide, 2nd Edition

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:

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:

1. Define a temporary table with the CREATE TABLE statement.

2. Populate the temporary table with the output of the stored procedure.

3. Join the temporary table to other tables.

4. 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).