User-defined functions (UDFs) are routines that perform calculations/computations and return a value–scalar (singular) or table. Microsoft SQL Server 2000 introduced UDFs, which you developed with T-SQL and could incorporate in queries, computed columns, and constraints. SQL Server 2005 introduces common language runtime (CLR) integration, allowing you to develop UDFs–as well as other routines and objects–using a .NET language of your choice.
This chapter explores the types of UDFs that are supported by SQL Server: scalar-valued UDFs, which return a single value, and table-valued UDFs (inline and multiple-statement), which return a table. I’ll provide sample code for CLR UDFs in both C# and Microsoft Visual Basic.
All the .NET routines in this book were developed by Dejan Sarka.
Note
This chapter is the first of three chapters (Chapter 6, Chapter 7, and Chapter 8) that cover CLR routines. Some of the steps involved in building and deploying CLR code into a database are common to any type of CLR routine that you create in SQL Server and also very technical. To avoid repetition of such technical steps in the text and to allow you to focus on the code of the routines, I compiled all the relevant information about CLR routines into Appendix A.
In Appendix A, you will find instructions for creating a test database called CLRUtilities, which you will use to test all CLR routines covered in these three chapters. You will also find step-by-step instructions required to develop, build, deploy, and test all the CLR routines.
The appendix also gathers in one place all the code of CLR routines that is scattered throughout these three chapters. I recommend that, before reading the remainder of this chapter, you follow the instructions in Appendix A, which will have you create in advance all the routines you will use in these three chapters. Then return to this chapter and continue reading, focusing on the code of the CLR routines instead of on the common technical steps.