SQL Server Scalar UDFs (User Defined Functions)

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.

About these ads

5 Responses to “SQL Server Scalar UDFs (User Defined Functions)”

  1. Jeff Barnes Says:

    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.

  2. SQL Server Table-valued UDFs (User Defined Functions) « Arcane Code Says:

    [...] SQL Server Scalar UDFs (User Defined Functions) [...]

  3. Mai Says:

    And this is why I love racanecode.com. Awesome posts.

  4. Wilmer Ridgebear Says:

    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!

  5. Sourabh Says:

    I was looking for this post, many doubts are cleared, 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 100 other followers

%d bloggers like this: