Invoke UDFs that accept tables with SQL Server 2005's APPLY operator

by Tim Chapman | Aug 24, 2006 7:00:00 AM

Tags: Tim Chapman

Takeaway: A new feature in SQL Server 2005 is the APPLY operator, which allows the database developer to invoke a user-defined function that takes table value parameters. In this article, Tim Chapman shows you how to use this incredibly useful new operator.

A user-defined function (UDF) is a database object introduced in SQL Server 2000. It comes in two flavors: scalar-valued UDFs and table valued UDFs. A scalar-valued function returns a single value for each function call, while a table valued UDF returns a table recordset that can be joined with other tables/resultsets. For a more detailed description of table-valued UDFs, see Arthur Fuller's article.

Limitations

UDFs are definitely a useful tool in the SQL Server database engine, but they do come with some limitations. First, the database engine has a difficult time handling large recordsets returned from table-valued UDFs. Another limitation is that in SQL Server 2000 you cannot join a table-valued UDF with another result set and pass in values from the result set to the UDF. For a bit clearer of an explanation, I'll offer an example.

First, I create a table and enter some data in Listing A. Then, I create a UDF to pull data from the table, as shown in Listing B.

This is a very simple table value UDF. I pass the IDCol value into the function, and the recordset returned is the row for the IDCol in the TempTable table. I can call this function with a simple SELECT query on the UDF:

SELECT * FROM dbo. udf_TestFunction(5)

I can even join this function on the TempTable table to return a recordset, like this:

SELECT * 

FROM dbo.udf_TestFunction(5) f

INNER JOIN TempTable t ON f.IDCol = t.IDCol

Even though this isn't a practical example, it does illustrate that you can join a table-valued UDF with other objects (even other table-valued UDFs) to return results. In SQL Server 2000, this is valid as long as the parameters you pass into the UDF are scalar variables. SQL Server 2000 does not support passing row values to a table valued UDF in a JOIN operation. For instance, the following query results in an error in SQL Server 2000:

SELECT * 

FROM TempTable t 

INNER JOIN dbo.fn_Test(t.IDCol)  f on t.ColID = f.ColID

However, in SQL Server 2005, this problem is resolved with the introduction of the APPLY operator.

The APPLY operator

The APPLY operator enables you to pass a value from a table into a table valued UDF as a parameter. However, the order of the operation does matter. To use the APPLY operator successfully, you must first list the table you wish to join followed by the UDF that you wish to pass the table row value into.

There are two flavors of the APPLY operator: CROSS APPLY and OUTER APPLY. The CROSS APPLY operator is similar to the INNER JOIN transact-sql construct. The results returned are based upon the criteria contained in the UDF, and are returned after the necessary values from the outer table are passed into the UDF as parameters. You can rewrite the query issued early for SQL Server 2005 easily using the CROSS APPLY construct with this code:

SELECT * FROM TempTable t CROSS APPLY dbo.fn_Test(t.IDCol)

The OUTER APPLY operator is similar to the OUTER JOIN transact-sql construct. Like OUTER JOIN, OUTER APPLY returns all rows from the outer table, and any rows from the table-valued UDF that meet the criteria for the variables passed into the function. To take advantage of this functionality, simply substitute OUTER APPLY for CROSS APPLY from our previous statement:

SELECT * FROM TempTable t OUTER APPLY dbo.fn_Test(t.IDCol)

The CROSS APPLY construct (as it pertains to table valued UDFs) is very similar to the way in which a correlated sub-query works. For more information regarding a correlated sub-query, read my recent article on the subject. The similarity is that a value from the outer query (in our situation, the first table is listed) is retrieved and passed to a sub-query, which in this case is the UDF.

Points to ponder

It's valuable to be able to pass values from table rows to a UDF; this will allow you to solve many problems you may be facing in your current applications. However, there is always a tradeoff when you get added functionality.

Passing a value from a row in a table invokes a call of the UDF for every row returned from the outer table based on the criteria of the query. This additonal UDF call may require that your application incur more overhead than necessary. If you decide to use the APPLY operator in your application, I recommend doing some profiling on the server first to determine if the performance of the query will adhere to the service level performance requirements that your company requries.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

Print/View all Posts Comments on this article

Hihgly proprietaryjcelko212@...  | 09/05/06

Could be valuable in a denormalisedTony Hopkinson  | 09/05/06

Actually the udfalaniane@...  | 11/29/07

what about remote access ?guttorm.haaversen@...  | 04/08/08