User-Defined Functions: Powerful Alternatives to 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)
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.
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.
Download the Code
You can download the code that accompanies the article here.
About the Author
Jeffrey Juday is a software developer with Crowe Chizek in South Bend, Indiana. He has been developing software with Microsoft tools for more than 12 years in a variety of industries. Jeff currently builds solutions using BizTalk 2004, ASP.NET, SharePoint, and SQL Server 2000. You can reach Jeff at firstname.lastname@example.org.
Page 2 of 2