Chapter 13
User-Defined Functions
WHAT YOU WILL LEARN IN THIS CHAPTER:
- What a user-defined function (UDF) is, and how it differs from stored procedures or views
- How to create UDFs that return either scalar values or tables
- The importance of .NET UDFs within SQL Server
Well, here you 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, not only am I going to introduce you to UDFs, but I’m also going to give you a look at the different types of UDFs, how they vary from stored procedures (often called sprocs), and, of course, what kinds of situations you might want to use them in. Finally, you’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 ...
