OK, I admit it sometimes I’m a little late to the party. Even though they’ve been around since SQL Server 2000, I haven’t had the pleasure of getting acquainted with User Defined Functions (UDFs) inside SQL Server. But now that I know about them, I can see how useful they can be. There are several kinds of UDFs, today let’s chat about the scalar types.
A scalar UDF returns one, and only one value from the function. You can pass in parameters, have it do everything from simple to complex calculations, then return a result. Let’s take a look at how one is coded, using the AdventureWorks database.
create function dbo.f_ContactFullName(@id as int)
returns varchar(101)
as
begin
declare @FullName varchar(101);
select @FullName = FirstName + ‘ ‘ + LastName
from Person.Contact
where ContactID = @id;
return @FullName;
end;
Running the above code will create your UDF. To confirm it worked, in SQL Server Management Studios Object Explorer open the tree under AdentureWorks. Go down to Progammability, Functions, Scalar-valued Functions and you should see the new function.
The purpose of this function is to take the contact ID (the Primary Key) and look up the name in the Person.Contact table. It then concatenates the first and last names for us and returns the full name.
We begin with a create function command, followed by the name of the function. I would suggest you adopt some naming scheme to identify your functions, to separate them from stored procedures or other built in functions. Some folks use ufn, some fn, I chose f_ for my prefix. At the end of the function is the parameter list, enclosed in parenthesis. If there had been more than one parameter, I would have used commas to separate them.
Next comes the return type, in this case I’m returning a varchar. Your function will then be enclosed in a begin…end construct. I’ve declared a variable to hold the return value (@FullName), and then run a simple select statement to get the name, concatenate it, and store it in our variable. Finally I use the return command to return the value back to the caller. And how do we call it?
Well, one simple way is to simply say “select functionname()” as in
select dbo.f_ContactFullName(1);
————————-
Gustavo Achong
(1 row(s) affected)
I passed in a contact id from the table (1) and it returned the full name of that person. What might be more useful though is to include the UDF as part of a select statement.
select Person.Contact.ContactID
, dbo.f_ContactFullName(Person.Contact.ContactID)
, EmailAddress
from Person.Contact
order by Person.Contact.ContactID
| ContactID | EmailAddress | |
| ——— | —————– | ——————————- |
| 1 | Gustavo Achong | gustavo0@adventure-works.com |
| 2 | Catherine Abel | catherine0@adventure-works.com |
| 3 | Kim Abercrombie | kim2@adventure-works.com |
| 4 | Humberto Acevedo | humberto0@adventure-works.com |
| 5 | Pilar Ackerman | pilar1@adventure-works.com |
| 6 | Frances Adams | frances0@adventure-works.com |
| 7 | Margaret Smith | margaret0@adventure-works.com |
| 8 | Carla Adams | carla0@adventure-works.com |
| 9 | Jay Adams | jay1@adventure-works.com |
On the second line of the select statement I call my small function, to comprise the full name. And under it you can see the results.
I should mention there are a few tradeoffs to using user defined functions. First, they are not portable to other databases. That means if you ever moved your data to another database type they would not transfer, and would have to be written as something else. But I mean, really now. How many times have you ever moved your data to another database. It happens so seldom that I would not worry about it.
What you should be concerned over though is the performance trade offs. For example, looking at my example above each row winds up making two calls to the same table, one to fetch the record and a second to fetch it again inside the function to get the name. Not very efficient. On the other hand, if I was reading another table, this might be just as efficient as joining the tables, and easier to use.
select SalesOrderID
, dbo.f_ContactFullName(ContactID) as OurCustomer
, OrderDate
, PurchaseOrderNumber
from Sales.SalesOrderHeader
order by SalesOrderID
| SalesOrderID | OurCustomer | OrderDate | PurchaseOrderNumber |
| ———— | ——————– | ———– | ——————– |
| 43659 | James Hendergart | 2001-07-01 | PO522145787 |
| 43660 | Takiko Collins | 2001-07-01 | PO18850127500 |
| 43661 | Jauna Elson | 2001-07-01 | PO18473189620 |
| 43662 | Robin McGuigan | 2001-07-01 | PO18444174044 |
| 43663 | Jimmy Bischoff | 2001-07-01 | PO18009186470 |
| 43664 | Sandeep Katyal | 2001-07-01 | PO16617121983 |
| 43665 | Richard Bready | 2001-07-01 | PO16588191572 |
| 43666 | Abraham Swearengin | 2001-07-01 | PO16008173883 |
In this example I’ve created a very simple User Defined Function. However, you can get as complex as you need and create long, intricate functions. Given the right circumstances UDFs can add a new dimension to your code reuse libraries.
Arcane Code 
You are definitely right about performance trade offs. I’m a big fan of using UDFs to encapsulate logic such as an ugly case statement, a series of IF conditions, or some kind of calculation. They are awesome in that context for improving readability and reuse. However, I am very cautious about using them for select operations.
At a previous job, I was tasked with resolving some ridiculous performance issues for a large report. It turned out that someone had written a nasty stored procedure using 7 or 8 UDFs as parameterized tables. After rewriting it to use derived tables, the execution time dropped from several minutes to less than 10 seconds!
This was a dramatic case, but it has always stuck with me.
And this is why I love racanecode.com. Awesome posts.
howdy there, i just found your web portal listed on yahoo, and i would like to tell that you compose exceptionally good on your blog. i am very moved by the way that you write, and the message is quality. in any case, i would also love to know whether you would love to exchange links with my web portal? i will be to the great extent than happy to reciprocate and insert your link on in the blogroll. waiting for your response, i give my sincere thanks and gooday!