Скалярные функции среды CLR

Статья
05/07/2013

Скалярная функция возвращает единственное значение, например строку, целочисленное или битовое значение. Начиная с версии SQL Server 2005 можно создавать определяемые пользователем скалярные функции в управляемом коде на любом языке программирования платформы .NET Framework. Эти функции доступны для Transact-SQL и другого управляемого кода. Сведения о преимуществах интеграции со средой CLR и выборе между управляемым кодом и Transact-SQL см. в разделе Общие сведения об интеграции со средой CLR.

Требования к скалярным функциям среды CLR

Скалярные функции .NET Framework реализуются в виде методов класса в сборке .NET Framework. Типами входных параметров и типом, возвращаемым скалярной функцией, могут быть любые типы данных, которые поддерживаются в SQL Server, за исключением varchar, char, rowversion, text, ntext, image, timestamp, table и cursor. Скалярные функции должны обеспечивать соответствие типа данных SQL Server и типа данных, возвращаемого методом реализации. Дополнительные сведения о преобразованиях типов см. в разделе Сопоставление данных о параметрах CLR.

При реализации скалярной функции .NET Framework на одном из языков .NET Framework можно включить дополнительные сведения о функции, задав пользовательский атрибут SqlFunction. Атрибут SqlFunction указывает, получает ли функция доступ к данным или вносит изменения в данные, является ли детерминированной и предусматривает ли выполнение операций с плавающей запятой.

Определяемые пользователем скалярные функции могут быть детерминированными или недетерминированными. Детерминированная функция всегда возвращает один и тот же результат при вызове с конкретным набором входных параметров. Недетерминированная функция может возвращать разные результаты при вызове с конкретным набором входных параметров.

Примечание
Не следует помечать функцию как детерминированную, если она не всегда выдает одинаковые выходные значения при передаче одинаковых входных значений и при одинаковом состоянии базы данных. Не следует определять функцию как детерминированную, если в действительности она таковой не является. Это может привести к искажению индексированных представлений и вычисляемых столбцов. Определить функцию как детерминированную можно, задав для свойства IsDeterministic значение true.

Не следует помечать функцию как детерминированную, если она не всегда выдает одинаковые выходные значения при передаче одинаковых входных значений и при одинаковом состоянии базы данных. Не следует определять функцию как детерминированную, если в действительности она таковой не является. Это может привести к искажению индексированных представлений и вычисляемых столбцов. Определить функцию как детерминированную можно, задав для свойства IsDeterministic значение true.

Параметры, возвращающие табличные значения

Возвращающие табличное значение параметры — это определяемые пользователем табличные типы, которые передаются в процедуру или функцию, предоставляя эффективный способ передачи на сервер нескольких строк данных. Возвращающие табличное значение параметры выполняют функции, аналогичные массивам параметров, но обладают большей гибкостью и лучше интегрируются с Transact-SQL. Они также обеспечивают возможность повышения производительности. Кроме того, возвращающие табличное значение параметры способствуют сокращению циклов приема-передачи данных с сервера и на сервер. Вместо того чтобы отправлять на сервер несколько запросов (как в случае списка скалярных параметров), данные можно отправить в виде возвращающего табличное значение параметра. Определяемый пользователем табличный тип нельзя передавать в виде возвращающего табличное значение параметра в управляемую хранимую процедуру или функцию, которая выполняется в процессе SQL Server. Кроме того, такие процедуры и функции не могут возвращать определяемые пользователем табличные типы. Дополнительные сведения о возвращающих табличное значение параметрах см. в разделе Использование параметров, возвращающих табличные значения (компонент Database Engine).

Пример скалярной функции среды CLR

Ниже приводится простой пример скалярной функции, получающей доступ к данным и возвращающей целочисленное значение.

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

В первой строке кода содержится ссылка на объект Microsoft.SqlServer.Server для доступа к атрибутам и на объект System.Data.SqlClient для доступа к пространству имен ADO.NET. (Это пространство имен содержит SqlClient, поставщик данных .NET Framework для SQL Server.)

Далее функция получает пользовательский атрибут SqlFunction, относящийся к пространству имен Microsoft.SqlServer.Server. Пользовательский атрибут указывает, используется ли определяемая пользователем функция (UDF) внутрипроцессным поставщиком для чтения данных с сервера. SQL Server не позволяет определяемым пользователем функциям обновлять, вставлять и удалять данные. SQL Server может оптимизировать выполнение определяемой пользователем функции, не использующей внутрипроцессный поставщик. На это указывает параметр DataAccessKind, имеющий значение DataAccessKind.None. На следующей строке целевой метод определяется как public static (или на языке Visual Basic .NET — shared).

После этого класс SqlContext, находящийся в пространстве имен Microsoft.SqlServer.Server, может получить доступ к объекту SqlCommand с уже созданным соединением с экземпляром SQL Server. Кроме того, становится доступным в рамках API System.Transactions контекст текущей транзакции, хотя в данном случае не используется.

Большая часть строк кода в тексте функции должна быть знакома для разработчика, имеющего опыт написания клиентских приложений с использованием типов из пространства имен System.Data.SqlClient.

[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

Необходимый текст команды можно задать путем инициализации объекта SqlCommand. В предыдущем примере подсчитывалось число строк в таблице SalesOrderHeader. Далее вызывается метод ExecuteScalar объекта cmd. Он возвращает значение типа int на основе запроса. Наконец происходит возврат сведений о количестве заказов в вызывающий код.

После сохранения в файле с именем FirstUdf.cs этот код можно скомпилировать в сборку следующим образом:

[C#]

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

[Visual Basic]

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

Примечание
/t:library указывает, что результатом компиляции должна быть библиотека, а не исполняемый объект. Исполняемые объекты нельзя регистрировать в SQL Server.

Примечание
Выполнение объектов базы данных, написанных на языке Visual C++ и скомпилированных с параметром /clr:pure, в СУБД SQL Server не поддерживается. В частности, такими объектами базы данных являются скалярные функции.

Ниже приведены запрос Transact-SQL и образец вызова для регистрации сборки и определяемой пользователем функции.

CREATE ASSEMBLY FirstUdf FROM 'FirstUdf.dll';
GO

CREATE FUNCTION CountSalesOrderHeader() RETURNS INT 
AS EXTERNAL NAME FirstUdf.T.ReturnOrderCount; 
GO

SELECT dbo.CountSalesOrderHeader();
GO

Обратите внимание, что имя функции в Transact-SQL не обязательно должно соответствовать имени общего статического целевого метода.