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.

create function dbo.f_People(@lastName as nvarchar(50))

        returns table




  select ContactID

      , FirstName + ‘ ‘ + LastName as FullName

      , Phone

    from Person.Contact

  where LastName like (@lastName + ‘%’)


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.

select * from dbo.f_People(‘Ab’)

ContactID FullName Phone
———– —————- ————-
2 Catherine Abel 747-555-0171
3 Kim Abercrombie 334-555-0137
1012 Syed Abbas 926-555-0182
1212 Kim Abercrombie 208-555-0114
1268 Hazem Abolrous 869-555-0125
1370 Kim Abercrombie 919-555-0100
1557 Sam Abolrous 567-555-0100


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.


2 thoughts on “SQL Server Table-valued UDFs (User Defined Functions)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s