SQL Server Multi-Statement Table-valued UDFs (User Defined Functions)

There’s a special variation of the Table-valued UDF called the multi-statement table-value. As with the regular Table-Value, it can only return a single table. However, you can create a table on the fly, populate it, then return the results. Lets take a look at an example, based on our sample from yesterday.

create function dbo.f_LotsOfPeople(@lastNameA as nvarchar(50), @lastNameB as nvarchar(50))

        returns @ManyPeople table

          (PersonID int, FullName nvarchar(101), PhoneNumber nvarchar(25))

as

begin

 

  insert @ManyPeople (PersonID, FullName, PhoneNumber)

    select ContactID

        , FirstName + ‘ ‘ + LastName

        , Phone

      from Person.Contact

    where LastName like (@lastNameA + ‘%’);

 

  insert @ManyPeople (PersonID, FullName, PhoneNumber)

    select ContactID

        , FirstName + ‘ ‘ + LastName

        , Phone

      from Person.Contact

    where LastName like (@lastNameB + ‘%’);

 

  return

end

The first line declares the function and passes in two parameters. The next line declares the return type will be a table, named @ManyPeople. We need to give our table a name, as we’ll be using it inside the function.

The third line defines the layout of our in memory table, @ManyPeople. I’ve declared three columns, and given their names and data type. Next comes the as, followed by a begin/end construct to house our code.

Inside the code I add data to the @ManyPeople table by using traditional Insert syntax. Please note this isn’t quite like a scalar function, I’m restricted to statements that insert or update records in the @ManyPeople table. Complex calculations, etc are restricted unless they take the form of inserting / updating into our table.

To end the function I have to have a Return statement that will end the function and return the @ManyPeople table to the calling routine. And how do we call it? Pretty simple, just like we did yesterday.

select * from dbo.f_LotsOfPeople(‘Abe’, ‘Zie’)

PersonID FullName PhoneNumber
———– —————- ————-
2 Catherine Abel 747-555-0171
3 Kim Abercrombie 334-555-0137
1212 Kim Abercrombie 208-555-0114
1370 Kim Abercrombie 919-555-0100
988 Arvid Ziegler 398-555-0100

(5 row(s) affected)

Multi-Statement Table-valued UDFs can be handy when you need to assemble data from multiple places and return a single table.

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.

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.

Brilliant!

Lately I’ve been delving more and more in to the SQL Server world, as it pertains to Business Intelligence. Coincidentally, our DBA complains of not sleeping at night, and seems to have developed a nervous twitch. I keep telling him to lay off the caffeine, but oh well I digress.

One of the things that I’ve found irritating is the inability for long running T-SQL scripts to be able to keep the user (in other words, ME) updated as it progress through. Instead it seems to save up any print or select messages until the entire job is over then prints them out in a big explosion, not unlike an episode of Mythbusters.

Mladen Prajdic on his “I want some Moore” blog came up with a brilliant solution. Use RAISERROR, with the NOWAIT option and a low severity, to flush the message buffers immediately. To quote those two guys from the Guinness ads, Brilliant! But hey, I don’t want to steal Mladen’s thunder, go read it for yourself:

http://weblogs.sqlteam.com/mladenp/archive/2007/10/01/SQL-Server-Notify-client-of-progress-in-a-long-running.aspx

True, I doubt I’d suggest this for production code that would run unattended, but for those long scripts we all wind up writing to do some tests or fix some bad records well… you can bet this is a handy tip I’ll be using over and over.

Installing Kubuntu 7.10 In Virtual PC 2007

After last weeks post on Ubuntu 7.10 (http://arcanecode.wordpress.com/2007/10/18/installing-ubuntu-710-under-virtual-pc-2007/), I had several requests for Kubuntu. Since I’m happy to please, here are the step by step instructions for Kubnutu. By the way, I’ve reduced the screen sizes a little to make them fit the flow of the blog, but you can click any of them to see them in full size should you need to make out any of the details.

If you haven’t already done so, you’ll need to download the latest image of Kubuntu, you can get it from http://www.kubuntu.com/download.php .

Kubuntu has some of the same issues as Ubuntu under Virtual PC when it comes to graphics and the mouse. When you fire up the VPC with the Kubuntu disk in the drive (or you’ve captured it’s ISO image), you’ll want to move the highlight down to “Start Kubuntu in safe graphics mode”. To fix the mouse, at least for this session, press F6 for boot options, and type in “i8042.noloop” after the –. Once your screen looks like the one below, press Enter to continue.

k710_001

Once Kubuntu boots, click the install icon on the desktop to begin the installation process.

k710_002

On the Welcome screen, just confirm your language, then press Next.

k710_003

In this step you get to play Carmen whats-her-name and do the “Where in the world are you” bit. Select a city in the time zone in which you live, then click Next.

k710_004

Now pick your keyboard, and click Next.

k710_005

On the disk space screen, just take the defaults and click next.

k710_006

OK, now we actually have to do some work, and give Kubuntu some info. Make sure to remember your password, not only will you need it to login but you’ll also need it for any commands that need super user privlidges.

k710_007

OK, Kubuntu finally knows everything it needs in order to install so just hit next.

k710_008

And wait. And wait. And wait. If you thought Ubuntu took a while to install, just wait for Kubuntu. My experience was a couple of hours, but to be fair I was also playing a couple of Quicktime videos (some of the cool shows from http://www.revision3.com) and testing an openSuse install in another VPC. And I have sloooooooooooow hard disks, so your milage (or kilometers) may vary. But it’ll still take a while.

About 84% or so into it I got this error. This is similar to the error I got with Ubuntu, just click OK to let it keep going. Oh, and wait some more.

k710_009

Yea! It finally finished the install.

k710_010

When you get to this screen, just press OK, then reboot Kubuntu. Don’t forget to eject the CD (or release the ISO) during the reboot. But before you press OK, make sure to read the next step!

OK, time for a tricky part. Pay close attention during the reboot. When you get to the screen that talks about the GRUB menu, press the ESCape key. You should see a screen like this:

k710_011

With the top line highlighted, press the e key to edit the command line.

On the next screen, press the down arrow once to highlight the line that begins in “kernel”, then press e again to edit that line. When the edit screen appears, we need to add the – i8042.noloop to the end of the line. It should look something like:

k710_012

Press Enter, then when you return to the screen with “kernel” on it, press ‘b’ (just the letter b) to boot Kubuntu. What this will do is enable the mouse for this session only! Once we get booted, we’ll fix the mouse permanently, so hang on.

When the login screen appears, enter your user name and password (the ones you entered on the “Who Are You” screen during the installation) and press enter. Now give it a minute while Kubuntu finishes loading.

OK, now it’s time to fix that pesky mouse issue once and for all. Click on the big K in the lower left (it’s like the Start button in Windows), and go to System, then bring up Konsole.

k710_013

Now in the Konsole window, type in

sudo kate /boot/grub/menu.lst

and press enter.

k710_014

Enter your password when prompted, and you should be in the kate editor. Note you may see a few errors on the terminal window. These can be ignored.

Once kate is up, scroll to the very bottom of the editor where you’ll find three sections of “title… kernel… “ etc. In the first section, which is the default, we need to edit the kernel line to add:

– i8042.noloop

to the end of the line, as you see here. Once done, save it by using File, Save on the menu or clicking the floppy disk. Then exit kate (File, Quit) and exit the terminal window (type exit and press enter, or close by just clicking the x button as you would in Windows).

k710_015

And that’s it, you should be good to go and enjoy Kubuntu 7.10 virtually.

PS If you found this useful, please give it a digg so others can find it too.

Arcane Links

I recently joined LinkedIn (http://www.linkedin.com/). Seems like a good way to get hooked up and stay connected. See my public profile at:

http://www.linkedin.com/in/arcanecode

Join yourself, you might be surprised how many people you know that are on Linked In.

Installing Ubuntu 7.10 Under Virtual PC 2007

Update April 24, 2008 – The newest version of Ubuntu, 8.04 is out. Look for complete install instructions here.

Update April 7 2008 – If you are interested in also playing with the 8.04 BETA, you can read my post here.

Ubuntu version 7.10 was just released. In keeping up with tradition I’d like to describe step by step instructions on how to install and get it running under Virtual PC 2007.

Before I begin though, I’d like to give a word of thanks to all the folks who have commented on my previous postings. It was their findings and efforts that helped to create this work, I owe them a big thanks.

OK, first thing you need is to download the Desktop install ISO from the Ubuntu site (http://www.ubuntu.com). You can skip right to the download mirrors page at http://www.ubuntu.com/getubuntu/downloadmirrors if you want to save a few mouse clicks.

Once you get it downloaded fire up Virtual PC, and create a new machine. If you are not familiar with VPC, see my step by step instructions for creating a machine at http://arcanecode.wordpress.com/2006/09/20/virtual-pc-step-by-step/ Make sure to pick “other” as the OS type. I used 512 meg of ram because my system has 2 gig, but if you have less you can get away with 256 meg of ram for the Ubuntu Virtual machine.

Fire up your new virtual machine, and use the option in the CD menu to “Capture ISO image”. Point the image at the desktop iso you just downloaded.When it starts, immediately press the down arrow, so that “Start Ubuntu in Safe Graphics Mode” is highlighted.

When 7.04 was released, the new kernel had issues with the mouse emulated by Virtual PC. To be blunt, the mouse just didn’t work. However, several work arounds were found. The easiest was brought to my attention via comments on the blog, the i8042.noloop option. That’s what we’ll implement, so we can use the mouse during the “live mode”.

Hit the F6 key, for Options. When the line appears, at the very end type in a space (if there’s not one after the two dashes) then i8042.noloop . Your screen should look something like this:

u710_001

Press Enter to start the launch process. Be patient, it takes quite a while. Once it’s finally up though, you’ll see this screen:

u710_002

Double click on the Install icon to begin the install.

On the first screen, below, you are welcomed and asked about a language. Pick your language and hit Forward.

u710_003

Now pick your time zone, since I’m in the Central zone I picked Chicago as a city in my time zone and clicked Forward.

u710_004

No it asks about keyboard layout, pick your keyboard if yours isn’t US English, then press Forward.

u710_005

Ubuntu will crank and grind for a minute, then you’ll see this dialog asking about your disks. Just take the defaults and click Forward.

u710_006

Time for a little personal info, give your name, a login id, enter the password you want to use, and what you want to name the “computer”. When done click Forward.

u710_007

OK, you’re almost ready to start the install process. Look this over, if everything looks good just press the Install button and we’re off to the races.

u710_008

Did I say races? Well, turtle race might be more like it, the install runs pretty slow, so get some coffee, or maybe a second bowl of ice cream if you’re doing a late night install.

u710_009

I did encounter one error during the install. You may see this as well, but you can go back later and correct this through the normal updates process.

u710_010

Now Ubuntu will finish, and ask if we want to reboot. Tell it no, then reboot by shutting down by pressing the red shut down icon in the very upper right of the Ubuntu window.

OK, you’ll have to be very quick with this next step. Remember the mouse issue? We’ll still need to fix it. First, boot the new machine, after clicking on CD and releasing the ISO if it’s still held. Now when you see the words “Grub loader” hit the Escape key. If you were fast enough, you’ll see this screen.

u710_012

With the line you see selected, press the “e” to edit the line. Now a new screen will appear.

u710_013

Move the highlight down one to the Kernal line, and press “e” to edit that line. When the new screen appears, you’ll need to add two dashes, then the i8042.noloop command. Your screen should look like this:

u710_014

Press Enter, then when you are returned to the screen with “kernel…” on it, make sure the kernel line is still highlighted and press b to boot.

Once booted, login using your user id and password. When Unbuntu is up, it’s time to fix the mouse issue once and for all. Click on Applications, Accessories, Terminal. When the terminal window appears, type in:

sudo gedit /boot/grub/menu.lst

u710_015

When you press Enter you’ll be prompted for your password, enter it. An editor should appear. Scroll down to the very bottom of the text and find the line that begins with “kernel”. Add the – i8042.noloop to the end of the line, as I’ve shown below. (Note I have highlighted the line to make it easy to see, yours won’t be normally highlighted in your session.)

u710_016

Save the file and exit the editor and the terminal window. When you next reboot, you should be able to just login normally, and the mouse should work.

And there you go, Ubuntu 7.10 up and running, complete with mouse, under Virtual PC 2007.

P.S. If you found this post useful, please give it a Digg so others can find the same happiness you did.

Follow

Get every new post delivered to your Inbox.

Join 103 other followers