Programming SQL Server 2005

Chapter 5. Programming SQL Server CLR Routines

This chapter demonstrates how to create each type of SQL Server 2005 CLR routine: user-defined functions (scalar-valued functions and table-valued functions), stored procedures, user-defined aggregate (UDA) functions, user-defined types (UDTs), and both DML and DDL triggers. All examples in this section use Visual Studio 2005 to create and compile the CLR routines. If you don’t have Visual Studio 2005, you can use the C# command-line compiler (csc.exe) discussed in Chapter 4.

Scalar-Valued Functions

A scalar-valued function (SVF) is a user-defined function (UDF) that returns a single value. Scalar-valued functions can take arguments and return values of any scalar data type supported by SQL Server except rowversion, text, ntext, image, timestamp, table, or cursor.

An SVF is implemented as a method of a class in a .NET Framework assembly. The return value of the method must be compatible with the SQL Server data type that the method returns. Table 4-16 lists SQL Server data types and their equivalent CLR data types.

You identify a .NET SVF or TVF by annotating the method where you implement the function with the SqlFunction attribute. In addition to indicating that the method should be registered as a function, the SqlFunction attribute can be used to define characteristics of the function. The SqlFunction attribute has the following syntax:

    SqlFunction [ ( function-attribute [,...] ) ]

    function-attribute::= IsDeterministic = {true | false} ...