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

as

return

(

  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.

About these ads

2 Responses to “SQL Server Table-valued UDFs (User Defined Functions)”

  1. Dick Lam Says:

    can I declare any variable in the above function so that I can use it in the return (…..)?

  2. Tanuj Kumar Says:

    This is best one article so far I have read online. I would like to appreciate you for making it very simple and easy. I have found another nice post related to this post over the internet which also explained very well. For more details please check out this link…

    http://mindstick.com/Articles/8a08ba84-57f6-4f8f-a18c-e4c5c96a549b/?Table-Valued%20Functions%20in%20SQL%20Server

    Thanks


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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 102 other followers

%d bloggers like this: