Статья
09/28/2010

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

Скалярная функция возвращает единственное значение, например строку, целочисленное или битовое значение. Начиная с версии 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. Этот атрибут указывает, какие действия выполняет функция: получает ли доступ к данным или изменяет их, детерминирована ли она, и включает ли эта функция операции с плавающей запятой.

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

Примечание
Не следует помечать функцию как детерминированную, если она не всегда выдает одинаковые выходные значения при передаче одинаковых входных значений и при одинаковом состоянии базы данных. Не следует определять функцию как детерминированную, если на самом деле она таковой не является. Это может привести к повреждению индексированных представлений и вычисляемых столбцов. Определить функцию как детерминированную можно, задав для свойства 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.ServerImports System.Data.SqlClientPublic 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 FunctionEnd 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';GOCREATE FUNCTION CountSalesOrderHeader() RETURNS INT AS EXTERNAL NAME FirstUdf.T.ReturnOrderCount; GOSELECT dbo.CountSalesOrderHeader();GO

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

Примечание
Начиная с версии SQL Server 2005 в базе данных SQL Server с уровнем совместимости 80 нельзя создавать управляемые определяемые пользователем типы, хранимые процедуры, функции, статистические функции и триггеры. Чтобы сделать эти функции интеграции со средой CLR доступными в SQL Server, необходимо при помощи хранимой процедуры sp_dbcmptlevel (Transact-SQL) задать для базы данных уровень совместимости 100.

Начиная с версии SQL Server 2005 в базе данных SQL Server с уровнем совместимости 80 нельзя создавать управляемые определяемые пользователем типы, хранимые процедуры, функции, статистические функции и триггеры. Чтобы сделать эти функции интеграции со средой CLR доступными в SQL Server, необходимо при помощи хранимой процедуры sp_dbcmptlevel (Transact-SQL) задать для базы данных уровень совместимости 100.