CLR Scalar-Valued Functions

更新： 2006 年 4 月 14 日

A scalar-valued function (SVF) returns a single value, such as a string, integer, or bit value. In Microsoft SQL Server 2005, you can create scalar-valued user-defined functions in managed code using any Microsoft .NET Framework programming language. These functions are accessible to Transact-SQL or other managed code. For information about the advantages of CLR integration and choosing between managed code and Transact-SQL, see Overview of CLR Integration.

Requirements for CLR Scalar-Valued Functions

.NET Framework SVFs are implemented as methods on a class in a .NET Framework assembly. The input parameters and the type returned from a SVF can be any of the scalar data types supported by SQL Server, except varchar, char, rowversion, text, ntext, image, timestamp, table, or cursor. SVFs must ensure a match between the SQL Server data type and the return data type of the implementation method. For more information about type conversions, see SQL Server Data Types and Their .NET Framework Equivalents.

When implementing a .NET Framework SVF in a .NET Framework language, the SqlFunction custom attribute can be specified to include additional information about the function. The SqlFunction attribute indicates whether or not the function accesses or modifies data, if it is deterministic, and if the function involves floating point operations.

Scalar-valued user-defined functions may be deterministic or non-deterministic. A deterministic function always returns the same result when it is called with a specific set of input parameters. A non-deterministic function may return different results when it is called with a specific set of input parameters.

附註：
Do not mark a function as deterministic if the function does not always produces the same output values, given the same input values and the same database state. Marking a function as deterministic, when the function isn't truly deterministic can result in corrupted indexed views and computed columns. You mark a function as deterministic by setting the IsDeterministic property to true.

Example of a CLR Scalar-Valued Function

Here is a simple SVF that accesses data and returns an integer value:

using Microsoft.SqlServer.Server;
using System.Data.SqlClient;

public class T
{
    [SqlFunction(DataAccess = DataAccessKind.Read)]
    public static int ReturnOrderCount()
    {
        using (SqlConnection conn 
            = new SqlConnection("context connection=true"))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(
                "SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn);
            return (int)cmd.ExecuteScalar();
        }
    }
}

Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient

Public Class T
    <SqlFunction(DataAccess:=DataAccessKind.Read)> _
    Public Shared Function ReturnOrderCount() As Integer
        Using conn As New SqlConnection("context connection=true")
            conn.Open()
            Dim cmd As New SqlCommand("SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn)
            Return CType(cmd.ExecuteScalar(), Integer)
        End Using
    End Function
End Class

The first line of code references Microsoft.SqlServer.Server to access attributes and System.Data.SqlClient to access the ADO.NET namespace. (This namespace contains SqlClient, the .NET Framework Data Provider for SQL Server.)

Next, the function receives the SqlFunction custom attribute, which is found in the Microsoft.SqlServer.Server namespace. The custom attribute indicates whether or not the user-defined function (UDF) uses the in-process provider to read data in the server. SQL Server does not allow UDFs to update, insert, or delete data. SQL Server can optimize execution of a UDF that does not use the in-process provider. This is indicated by setting DataAccessKind to DataAccessKind.None. On the next line, the target method is a public static (shared in Microsoft Visual Basic .NET).

The SqlContext class, located in the Microsoft.SqlServer.Server namespace, can then access a SqlCommand object with a connection to the SQL Server instance that is already set up. Although not used here, the current transaction context is also available through the System.Transactions application programming interface (API).

Most of the lines of code in the function body should look familiar to developers who have written client applications that use the types found in the System.Data.SqlClient namespace.

[C#]

using(SqlConnection conn = new SqlConnection("context connection=true")) 
{
   conn.Open();
   SqlCommand cmd = new SqlCommand(
        "SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn);
   return (int) cmd.ExecuteScalar();
}  

[Visual Basic]

Using conn As New SqlConnection("context connection=true")
   conn.Open()
   Dim cmd As New SqlCommand( _
        "SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn)
   Return CType(cmd.ExecuteScalar(), Integer)
End Using

The appropriate command text is specified by initializing the SqlCommand object. The previous example counts the number of rows in table SalesOrderHeader. Next, the ExecuteScalar method of the cmd object is called. This returns a value of type int based on the query. Finally, the order count is returned to the caller.

If this code is saved in a file called FirstUdf.cs, it could be compiled into as assembly as follows:

[C#]

csc.exe /t:library /out:FirstUdf.dll FirstUdf.cs 

[Visual Basic]

vbc.exe /t:library /out:FirstUdf.dll FirstUdf.vb

附註：
/t:library indicates that a library, rather than an executable, should be produced. Executables cannot be registered in SQL Server.

附註：
Managed Visual C++ database objects that have been compiled with the /clr:pure Visual C++ compiler option are not supported for execution on SQL Server 2005. For example, such database objects include scalar-valued functions.

The Transact-SQL query and a sample invocation to register the assembly and UDF are:

CREATE ASSEMBLY FirstUdf FROM 'FirstUdf.dll'
CREATE FUNCTION CountSalesOrderHeader() RETURNS INT 
AS EXTERNAL NAME FirstUdf.T.ReturnOrderCount
SELECT dbo.CountSalesOrderHeader()

Note that the function name as exposed in Transact-SQL does not need to match the name of the target public static method.

附註：
On a SQL Server 2005 database with a compatibility level of "80," you cannot create managed user-defined types, stored procedures, functions, aggregates, or triggers. To take advantage of these CLR integration features of SQL Server 2005, you must use the sp_dbcmptlevel (Transact-SQL) stored procedure to set the database compatibility level to "90.".

Change History

Release	History
2006 年 4 月 14 日	Updated content: Added note on database compatibility level. Added note on managed Visual C++ routines compiled with the /clr:pure option. Added note on the danger of marking a function as being deterministic, using the IsDeterministic property, when it isn't truly deterministic.
2006 年 12 月 12 日	Updated content: Updated the list of possible SVF input parameter types in the "Requirements for CLR Scalar-Valued Functions" section.

請參閱

概念

SQL Server Data Types and Their .NET Framework Equivalents
Data Access from CLR Database Objects

其他資源

Overview of CLR Integration Custom Attributes
瞭解使用者自訂函數
決定性與非決定性函數
資料庫相容性層級選項

說明及資訊

取得 SQL Server 2005 協助