SQL Server Table-valued UDFs (User Defined Functions)

Yesterday we talked about one type of UDF, the scalar. There is also a great comment that emphasizes a point I made, namely you need to be careful to test your UDFs for performance (take time to read it, it’s worth your time). Often a UDF will give you can get a nice performance boost, but sometimes they can negatively affect your queries. TEST!

Today we’ll cover the Table value type of UDF. Unlike the scalar type, which returns only one value, the table type can return multiple rows. They are similar to views, only they perform slightly better. Let’s look at an example.

As you can see, you are only allowed one statement inside the function, and it must be a select statement. It can be a complex one, or a simple one as I’ve done above. The return type of the function is declared as table, which flags this as a table valued UDF.

To use it, treat the UDF as if it were a table and place it in the from clause. You’ll notice that unlike a table though, you can pass in a parameter. Here I pass in a varchar string, and use it as part of the where clause inside the UDF. Here’s an example of using our UDF as part of a SQL statement.

So why would you want to use this instead of a view? Well as you can see from my example, you have the ability to pass a parameter to the function. With a view, SQL Server precompiles the SQL, then you have to limit the results with a where clause. With the function, SQL also precompiles the statement but this time we can use a parameter, which is precompiled into the select statement, to limit the results. That means you’ll get slightly better performance out of the UDF versus the view.