Microsoft SQL Server 2008 R2 Unleashed- P101:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 944 CHAPTER 29 Creating and Managing User-Defined Functions Creating and Using CLR Functions Prior to SQL Server 2005 the only way to extend the functionality of SQL Server beyond what was available using the T-SQL language was to create extended stored procedures or Component Object Model COM components. The main problem with these types of extensions was that if not written very carefully they could have an adverse impact on the reliability and security of SQL Server. For example extended stored procedures are implemented as DLLs that run in the same memory space as SQL Server. An access violation raised in a poorly written extended stored procedure could crash SQL Server itself. In addition neither extended stored procedures nor COM components allow you to create custom user-defined functions that can be written in any programming language other than T-SQL which has a limited command set for operations such as complex string comparison and manipulation and complex numeric computations. In SQL Server 2008 you can write custom user-defined functions in any Microsoft .NET Framework programming language such as Microsoft Visual Basic .NET or Microsoft Visual C . SQL Server supports both scalar and table-valued CLR functions as well as CLR user-defined aggregate functions. These extensions written in the CLR are much more secure and reliable than extended stored procedures or COM components. For information on the methods and tools to actually create and compile CLR user-defined functions see Chapter 46. This chapter focuses only on how to install and use CLR functions in a SQL Server database. NOTE The CLR function examples presented in the following sections are provided as illustrations only. The sample code will not execute successfully because the underlying CLR assemblies have not been provided. Adding CLR Functions to a Database If you ve already created and compiled a CLR function your next task is to install that CLR function in the database. The first step
