Beginning Microsoft® SQL Server® 2008 Programming

Chapter 13. User-Defined Functions

Well, here we are already at one of my favorite topics. Long after their introduction, user-defined functions—or UDFs—remain one of the more underutilized and misunderstood objects in SQL Server. In short, these were awesome when Microsoft first introduced them in SQL Server 2000, and the addition of .NET functionality back in SQL Server 2005 just added all that much more to them. One of the best things about UDFs from your point of view is, provided you've done the book in order, you already know most of what you need to write them. They are actually very, very similar to stored procedures—they just have certain behaviors and capabilities about them that set them apart and make them the answer in many situations.

In this chapter, we're not only going to introduce what UDFs are, but we're also going to take a look at the different types of UDFs, how they vary from stored procedures (often called sprocs), and, of course, what kinds of situations we might want to use them in. Finally, we'll take a quick look at how you can use .NET to expand on their power.

What a UDF Is

A user-defined function is, much like a sproc, an ordered set of T-SQL statements that are pre-optimized and compiled and can be called to work as a single unit. The primary difference between them is how results are returned. Because of things that need to happen in order to support these different kinds of returned values, UDFs have a few more limitations to them than sprocs do.