Section 5.1. Scalar-Valued Functions

5.1. 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}       | DataAccess = { DataAccessKind.None | DataAccessKind.Read }       | SystemDataAccess = { SystemDataAccessKind.None | SystemDataAccessKind.Read }       | IsPrecise = { true | false }       | FillRowMethodName = string       | Name = string       | TableDefinition = string 

where:

IsDeterministic

Specifies whether the function always returns the same output values for the same set of input values and the same database state. This allows the server to do performance optimizations. The default value is false.

DataAccess = { DataAccessKind.None | DataAccessKind.Read }

Specifies the type of data access the function requires if it accesses data on the local SQL Server or on a remote server if transaction integration is required. The DataAccess argument takes one of two values of the DataAccessKind enumeration:

DataAccessKind.None

The function does not access data

DataAccessKind.Read

The function only reads data

The DataAccess property should be set to DataAccessKind.Read if a T-SQL statement is executed inside a CLR SVF or TVF routine.

User-defined functions cannot insert, update, or delete data.

SystemDataAccess = { SystemDataAccessKind.None | SystemDataAccessKind.Read }

Specifies the type of data access the function requires if it accesses data stored in the system catalogs or virtual system tables. The SystemDataAccess argument takes one of the two values of the SystemDataAccessKind enumeration:

SystemDataAccessKind.None

The function does not access data. This is the default value.

SystemDataAccessKind.Read

The function only reads data.

IsPrecise

Specifies whether the return value of the function depends on imprecise calculations involving single or double data types (float or real in SQL Server). This property is used to determine whether the computed columns using the function can be indexed. The default value is false.

FillRowMethodName

Specifies the name of the method used by a table-valued function to fill a row of data in the table returned by the function. Fill row methods are discussed in the next section, "Table-Valued Functions."

Name

Specifies the name with which the function should be registered in SQL Server.

TableDefinition

Specifies the layout of the table returned by a table-valued function.

The following example creates, registers, and executes a scalar-valued function. This function returns the total for a specific sales order by summing the LineTotal values in the Sales.SalesOrderDetail table in AdventureWorks for a specified sales order ID. Follow these steps:

1. Using the Visual Studio 2005 IDE, create a new SQL Server project named ScalarUdf.

2. Create a user-defined function item in the project by right-clicking on the project in Solution Explorer and selecting Add User-Defined function from the context menu. Name the function SumLineTotal and click the Add button.

3. Modify the SqlFunction attribute to indicate that the function will be reading data:

        [SqlFunction(DataAccess = DataAccessKind.Read)] 

   
   

4. Change the return type of the SumLineTotal( ) method from SqlString to SqlMoney to match the data type of the LineTotal column being summed. Add an int argument named salesOrderID to the SumLineTotal( ) method.

5. Add code to the SumLineTotal( ) method to perform the calculation. The complete code follows:

        using System;     using System.Data;     using System.Data.Sql;     using System.Data.SqlTypes;     using Microsoft.SqlServer.Server;     using System.Data.SqlClient;     public partial class UserDefinedFunctions     {         [SqlFunction(DataAccess = DataAccessKind.Read)]         public static SqlMoney SumLineTotal(int salesOrderID)         {             using (SqlConnection conn =                 new SqlConnection("context connection=true"))             {                 conn.Open(  );                 SqlCommand cmd = new SqlCommand(                     "SELECT SUM(LineTotal) " +                     "FROM Sales.SalesOrderDetail " +                     "WHERE SalesOrderdocList">The function reads data from SQL Server, so the DataAccess property of the SqlFunction attribute is set to DataAccessKind.Read.
   Notice that the return value is decimal, which is compatible with the SQL Server Money type. Table 4-16 lists SQL Server data types and their equivalent CLR data types.
   

6. Build the solution.

7. In SQL Server Management Studio, register the assembly and create the scalar-valued function by executing this query:

        USE AdventureWorks     GO     CREATE ASSEMBLY ScalarUdf     FROM 'C:\PSS2005\ScalarUdf\ScalarUdf\bin\Debug\ScalarUdf.dll'     GO     CREATE FUNCTION SumLineTotal(@salesOrderID int)     RETURNS MONEY     AS EXTERNAL NAME ScalarUdf.UserDefinedFunctions.SumLineTotal 

   
   

8. Execute the scalar-valued function with the following T-SQL statement:

        SELECT dbo.SumLineTotal(43660) 

   
   

   The results are shown in Figure 5-1.

Figure 5-1. Results for scalar-valued function example