User-Defined Functions: Powerful Alternatives to Views and Stored Procedures

Views and stored procedures allow you to modularize and decouple your Transact-SQL (T-SQL) code well, but have you ever wanted to get more out of them? Like using a parameter-driven view or a stored procedure in a SELECT statement? Well, SQL Server has an often-overlooked alternative to views and stored procedures that you should consider: table-valued user defined functions (UDFs). Table-valued UDFs have all the important features of views and stored procedures and some additional functionality that views and stored procedures lack.

For example, my development team used table-valued UDFs to add a new layer of filtering to an ancient ASP business-intelligence application. Substituting parameter-driven UDFs for tables in the FROM clause of the select statements, we left the core T-SQL largely unchanged. Even better, we were able to automate most of the changes using "search and replace."

Using modified samples from the AdventureWorks sample database that ships with SQL Server 2005, this article explains how to use table-valued UDFs effectively in your T-SQL code. To see the UDFs stored in the AdventureWorks database, use Management Studio to navigate to the area in the database shown in Figure 1.

Figure 1. Location of UDFs in AdventureWorks Database

Every UDF is written in T-SQL (like a stored procedure or view) and has parameter values (like a stored procedure). You use the CREATE FUNCTION statement to define a UDF.

User-Defined Functions Defined

User-defined functions come in the following two flavors:

  • Scalar-valued UDFs can return almost any single data type.
  • Table-valued UDFs return a temporary table containing multiple rows.

You can use scalar UDFs inside any DML (INSERT, UPDATE, SELECT, and so forth) or T-SQL statement. Table UDFs have a few more limitations, and you typically use a table-valued UDF in the FROM part of a SELECT statement. This article focuses on the uses of table-valued UDFs.

Introduction to table-valued UDFs

There are two types of table-valued UDFs:

  • Inline table-valued functions return a TABLE datatype. They each contain a single T-SQL statement.
  • Multi-statement table-valued functions return a defined table. They can contain multiple T-SQL statements.

Both types of table-valued UDF return single result sets. The following is an example of an inline table-valued UDF definition:

CREATE FUNCTION dbo.TestInlineFunctionName
(

)
RETURNS TABLE
AS
RETURN
(
   SELECT 0 as RetVal,* from [Person].[Contact]
)

A multi-statement UDF looks similar to an inline UDF, but with one major difference: It contains table definition statements after the RETURNS directive, as in the following sample code:

RETURNS @retContactInformation TABLE
(
   -- Columns returned by the function
   [ContactID] int PRIMARY KEY NOT NULL,
   [FirstName] [nvarchar](50) NULL,
   [LastName] [nvarchar](50) NULL,
   [JobTitle] [nvarchar](50) NULL,
   [ContactType] [nvarchar](50) NULL
)

Because a multi-statement UDF can include many SELECT statements (as well as other T-SQL statements), it must explicitly populate the returning table, as in the following sample code:

INSERT @retContactInformation
SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;

A multi-statement table-valued UDF enables you to do things such as modify the contents of the returned table. For example, the following code is perfectly legal in a multi-statement UDF:

UPDATE @retContactInformation SET [JobTitle] = 'None'

Now that you are familiar with table-valued functions, it's time to learn how they can replace views and stored procedures.

Replacement for Views

Table-valued UDFs work a lot like parameter-driven views. So, by using a table-driven UDF, you get all the flexibility of a view with the added benefit of parameters to filter data and act as an added layer of security. Because a table UDF requires permission to execute (like a view), you can use table UDFs as a sort of security mechanism.

The following code illustrates how to use a table-valued UDF in a SELECT statement:

select * from ufnGetContactInformation_MDY(1209)

Notice how the table UDF can replace a view in the FROM clause of the SELECT statement, and it also accepts a parameter. Although the parameter is hard-coded in the example code, you can pass variables to the function.

Table-valued UDFs also are a nice alternative to using multiple views for filtering a result set. For example, you can utilize IF/ELSE statements in a table UDF to filter on different parameter values. In the development scenario I mentioned previously, had we not used table-valued UDFs instead of views, we would've needed IF/ELSE statements in every stored procedure. In a database with 50+ stored procedures, that amounts to a lot of coding. Using the table UDFs, we could confine the required IF/ELSE statements to five functions.

Also, as with a view, the order by statement is invalid in table-valued functions./p>

Finally, as the article shows later, table-valued UDFs have most of the capabilities of a stored procedure.

Replacement for Stored Procedures

Table-valued UDFs are functionally similar to views, but structurally similar to stored procedures. Like a stored procedure, a table-valued UDF contains one or more T-SQL statements and up to 1,024 parameters. Also like a stored procedure, you can declare variables and use other functions, though there are limitations (more on these later).

Like a stored procedure, table UDFs allow common table expressions, as in the following recursive example:

WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS
(
   SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
   FROM HumanResources.Employee
   WHERE ManagerID IS NULL
   UNION ALL
   SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
   FROM HumanResources.Employee e
      INNER JOIN DirectReports d
      ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, EmployeeLevel
FROM DirectReports )

As discussed previously, unlike a stored procedure, a table-valued UDF can be used in the FROM clause of a SELECT statement. In the development scenario mentioned previously, being able to add the table UDF to the FROM clause saved us from having to completely rewrite all 50+ stored procedures using dynamic code and the EXEC statement.

Table-valued UDFs are also more functional than stored procedures in some other interesting ways. As with a correlated subquery, single-value result sets can be used as parameters of table-value UDFs. For example, the following code below is legal:

select * from ufnGetContactInformation_MDY
   ((SELECT MAX(ContactID)FROM Person.Contact))

Functionality, of course, comes with a price. As such, table-valued UDFs have some usability limitations.

Table-Valued UDF Limitations

Like everything in software development, functionality comes with trade-offs. As you might imagine, there is a practical limit on the amount of data you should return in a table UDF. A table UDF's data limitations are similar to the limitations on a temporary table. Because you can add parameters to a table UDF, you usually can avoid these limitations by adding more parameters to filter the result set.

Unlike a stored procedure, a table UDF limits you to returning one result set.

Table UDFs are limited to using deterministic functions. Nondeterministic functions are not allowed. SQL Server books online defines nondeterministic functions as functions that have no predictable return value given their input. For example, you can't use the EXEC statement or even GETDATE() in a table UDF.

There are also limitations on how you handle text and image fields. Data returned from a text or image field is limited to 256 bytes, and you cannot use the READTEXT, WRITETEXT, and UPDATETEXT statements.

Fine Alternatives

As you have seen, table UDFs are fine alternatives to using views and stored procedures. They offer similar functionality to views and similar structure to stored procedures, enabling you to take advantage of the rich coding capabilities that views lack and the flexibility to use them anywhere in a SELECT statement, which stored procedures lack.



About the Author

Jeffrey Juday

Jeff is a software developer specializing in enterprise application integration solutions utilizing BizTalk, SharePoint, WCF, WF, and SQL Server. Jeff has been developing software with Microsoft tools for more than 15 years in a variety of industries including: military, manufacturing, financial services, management consulting, and computer security. Jeff is a Microsoft BizTalk MVP. Jeff spends his spare time with his wife Sherrill and daughter Alexandra.

Downloads

Comments

  • nondeterministic work-around

    Posted by mmcginty on 04/18/2006 04:45pm

    Though you can't call nondeterministic functions from within a UDF, there is no restriction on how you derive scalar values you pass to UDFs as parameters. IOW, if you find you need to call GETDATE() in a UDF, simply add a datetime parameter to the UDF and pass the return value of GETDATE() to the UDF in that parameter. That way the nondeterministic function is executed by the UDF's caller, to make the value it returns available within the UDF.

    True it is a little messy, but it's not the first time source code aesthetics have been traded for functionality, and it surely won't be the last. :-)

    -Mark McGinty

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: December 11, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT Market pressures to move more quickly and develop innovative applications are forcing organizations to rethink how they develop and release applications. The combination of public clouds and physical back-end infrastructures are a means to get applications out faster. However, these hybrid solutions complicate DevOps adoption, with application delivery pipelines that span across complex hybrid cloud and non-cloud environments. Check out this …

  • On-demand Event Event Date: October 29, 2014 It's well understood how critical version control is for code. However, its importance to DevOps isn't always recognized. The 2014 DevOps Survey of Practice shows that one of the key predictors of DevOps success is putting all production environment artifacts into version control. In this webcast, Gene Kim discusses these survey findings and shares woeful tales of artifact management gone wrong! Gene also shares examples of how high-performing DevOps …

Most Popular Programming Stories

More for Developers

RSS Feeds