Data Management

Building and using table UDFs in SQL Server

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 )
AS
      SELECT CustomerID, CompanyName
      FROM Customers
      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>
)
RETURNS TABLE
AS
      RETURN
      (
      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
(
      @city nvarchar(15)
)
RETURNS TABLE
AS
      RETURN
      (
      SELECT CustomerID, CompanyName
      FROM dbo.Customers
      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:

ALFKIAlfredsFutterkiste
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:

SELECT Customers_By_City_Select.CustomerID, OrderID 
FROM Customers_By_City_Select ('Berlin')
INNER JOIN Orders ON Orders.CustomerID = Customers_By_City_Select.CustomerID

This returns the following result set:

ALFKI10643
ALFKI10692
ALFKI10702
ALFKI10835
ALFKI10952
ALFKI11011

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!

0 comments

Editor's Picks