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))
, FirstName + ‘ ‘ + LastName as FullName
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’)
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)”
can I declare any variable in the above function so that I can use it in the return (…..)?
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…