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!