Table UDFs (user-defined functions) are one of Arthur Fuller's favorite features of SQL Server 2000 and 2005. Find out why he says that, if you've never used them, you may be in for a surprise.
Table UDFs (user-defined functions) are one of my favorite features of SQL Server 2000 and 2005. If you've never used them, you may be in for a surprise.
A table UDF is similar to a stored procedure that returns a recordset. You might do something as simple as the following code example, which results in a recordset with two columns and zero or more rows.
CREATE PROCEDURE simple_ap
( @cityID integer )
SELECT CustomerID, CompanyName
WHERE Customers.CityID = @cityID
There is no easy way to reuse the results of such a stored procedure. For example, you can't feed the recordset to another procedure, or JOIN the results to a table, view, or another recordset. If you could, then your opportunities to reuse code would multiply significantly, and you could design your code with this in mind.
Enter the table UDF. This construct enables you to treat the result set just as if it were a genuine table or view—you can JOIN it to other objects, add a WHERE clause to it, and much more.
The following code contains the syntax for creating a table UDF in SQL Server 2000:
CREATE FUNCTION [owner].[function_name]
@parm1 <datatpe> = <default>
SELECT <column1, column2, ...>
FROM <table, view, etc.>
WHERE <some condition applies> -- optional clauses
To turn the stored procedure above into a table UDF, just substitute the appropriate chunks for the place markers in the UDF syntax, so that it looks like this:
CREATE FUNCTION [dbo].[Customers_By_City_Select] -- denotes table UDF
SELECT CustomerID, CompanyName
WHERE Customers.City = @city
The result set is identical but with some cool new advantages. First, using the procedure instead, a call would look like:
EXEC simple_ap 12345
A call to the UDF would look like this:
SELECT * FROM Customers_By_City_Select ('Berlin')
Note that using the UDF, we can add the word DISTINCT to the call, which I did to reduce the result set in this code sample:
ANATRAna Trujillo Emparedados y helados
ANTONAntonio Moreno Taquería
AROUTAround the Horn
This not only demonstrates that a table function is an actual table; it also indicates that a procedure that allowed this flexibility would have to be more complicated.
The fun is just beginning. We can select any subset of the columns returned by the function:
SELECT CustomerID FROM Customers_By_City_Select ('Berlin')
The coolest aspect of table functions is their table-ness: you can JOIN to tables, views, and other table functions, referring to the UDF just as if it were a table:
FROM Customers_By_City_Select ('Berlin')
INNER JOIN Orders ON Orders.CustomerID = Customers_By_City_Select.CustomerID
This returns the following result set:
This ability facilitates what I like to call atomic queries. An atomic query returns results from one table (or view). By combining several atomic queries, you can create a molecular query (consisting of several atoms). Table UDFs enable you to reach a level of granularity and reusability that is impossible to achieve using stored procedures.
Miss a tip?
Check out the SQL Server archive, and catch up on the most recent editions of Arthur Fuller's column.
TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!