CREATE FUNCTION (Transact-SQL)

Статья
04/26/2024

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

Создает определяемую пользователем функцию (UDF), которая является подпрограммой Среды CLR Transact-SQL или среды CLR. Определяемая пользователем функция принимает параметры, выполняет действие, например сложное вычисление, и возвращает результат этого действия в качестве значения. Возвращаемое значение может быть скалярным значением или таблицей. При помощи этой инструкции можно создать подпрограмму, которую можно повторно использовать следующими способами.

В инструкциях Transact-SQL, таких как SELECT
В приложениях, вызывающих функцию
В определении другой пользовательской функции.
Для параметризации представления или улучшения функциональности индексированного представления.
Для определения столбца таблицы.
Определение CHECK ограничения для столбца
Для замены хранимой процедуры.
Использование встроенной функции в качестве предиката фильтра для политики безопасности

В этой статье рассматривается интеграция среды CLR .NET Framework с SQL Server. Интеграция СРЕДЫ CLR не применяется к База данных SQL Azure.

Сведения о Azure Synapse Analytics или Microsoft Fabric см. в статье CREATE FUNCTION (Azure Synapse Analytics и Microsoft Fabric).

Соглашения о синтаксисе Transact-SQL

Синтаксис

Синтаксис скалярных функций Transact-SQL.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
 [ = default ] [ READONLY ] }
    [ , ...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ , ...n ] ]
    [ AS ]
    BEGIN
        function_body
        RETURN scalar_expression
    END
[ ; ]

Синтаксис встроенных функций Transact-SQL с табличным значением.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] [ READONLY ] }
    [ , ...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ , ...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

Синтаксис для функций с несколькими инструкциями Transact-SQL с табличным значением.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] [ READONLY ] }
    [ , ...n ]
  ]
)
RETURNS @return_variable TABLE <table_type_definition>
    [ WITH <function_option> [ , ...n ] ]
    [ AS ]
    BEGIN
        function_body
        RETURN
    END
[ ; ]

Синтаксис предложений функций Transact-SQL.

<function_option> ::=
{
    [ ENCRYPTION ]
  | [ SCHEMABINDING ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
  | [ INLINE = { ON | OFF } ]
}

<table_type_definition> ::=
( { <column_definition> <column_constraint>
  | <computed_column_definition> }
    [ <table_constraint> ] [ , ...n ]
)
<column_definition> ::=
{
    { column_name data_type }
    [ [ DEFAULT constant_expression ]
      [ COLLATE collation_name ] | [ ROWGUIDCOL ]
    ]
    | [ IDENTITY [ (seed , increment ) ] ]
    [ <column_constraint> [ ...n ] ]
}

<column_constraint> ::=
{
    [ NULL | NOT NULL ]
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ]
      [ WITH FILLFACTOR = fillfactor
        | WITH ( <index_option> [ , ...n ] )
      [ ON { filegroup | "default" } ] ]
  | [ CHECK ( logical_expression ) ] [ , ...n ]
}

<computed_column_definition> ::=
column_name AS computed_column_expression

<table_constraint> ::=
{
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ]
      ( column_name [ ASC | DESC ] [ , ...n ]
        [ WITH FILLFACTOR = fillfactor
        | WITH ( <index_option> [ , ...n ] )
  | [ CHECK ( logical_expression ) ] [ , ...n ]
}

<index_option> ::=
{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
}

Синтаксис скалярных функций CLR.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] }
    [ , ...n ]
)
RETURNS { return_data_type }
    [ WITH <clr_function_option> [ , ...n ] ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

Синтаксис для функций с табличным значением среды CLR.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] }
    [ , ...n ]
)
RETURNS TABLE <clr_table_type_definition>
    [ WITH <clr_function_option> [ , ...n ] ]
    [ ORDER ( <order_clause> ) ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

Синтаксис предложений функций CLR.

<order_clause> ::=
{
   <column_name_in_clr_table_type_definition>
   [ ASC | DESC ]
} [ , ...n ]

<method_specifier> ::=
    assembly_name.class_name.method_name

<clr_function_option> ::=
{
    [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

<clr_table_type_definition> ::=
( { column_name data_type } [ , ...n ] )

Синтаксис OLTP в памяти для скомпилированных в собственном коде скалярных пользовательских функций.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
 ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
    [ NULL | NOT NULL ] [ = default ] [ READONLY ] }
    [ , ...n ]
  ]
)
RETURNS return_data_type
     WITH <function_option> [ , ...n ]
    [ AS ]
    BEGIN ATOMIC WITH (set_option [ , ... n ] )
        function_body
        RETURN scalar_expression
    END

<function_option> ::=
{
  |  NATIVE_COMPILATION
  |  SCHEMABINDING
  | [ EXECUTE_AS_Clause ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
}

Аргументы

OR ALTER

Область применения: SQL Server 2016 (13.x) с пакетом обновления 1 (SP 1) и более поздних версий и База данных SQL Azure.

Условно изменяет функцию только в том случае, если она уже существует.

Необязательный OR ALTER синтаксис доступен для среды CLR, начиная с SQL Server 2016 (13.x) с пакетом обновления 1 (CU 1).

schema_name

Имя схемы, к которой принадлежит определяемая пользователем функция.

function_name

Имя определяемой пользователем функции. Имена функций должны удовлетворять правилам построения идентификаторов и быть уникальными в пределах базы данных и схемы.

Скобки требуются после имени функции, даже если параметр не указан.

@parameter_name

Параметр в определяемой пользователем функции. Может быть объявлен один или несколько аргументов.

Для функций допускается не более 2 100 параметров. При выполнении функции значение каждого из объявленных параметров должно быть указано пользователем, если для них не определены значения по умолчанию.

Определяет имя параметра, используя знак @ как первый символ. Имя параметра должно соответствовать правилам для идентификаторов. Параметры являются локальными в пределах функции, в разных функциях могут быть использованы одинаковые имена параметров. Параметры могут занять место только констант; их нельзя использовать вместо имен таблиц, имен столбцов или других объектов базы данных.

ANSI_WARNINGS Не учитывается при передаче параметров в хранимой процедуре, определяемой пользователем функции или при объявлении и установке переменных в инструкции пакетной службы. Например, если объявить переменную как char(3), а затем присвоить ей значение длиннее трех символов, данные будут усечены до размера переменной, а инструкция INSERT или UPDATE завершится без ошибок.

[ type_schema_name. ] parameter_data_type

Тип данных параметра и, при необходимости, схема, к которой она принадлежит. Для функций Transact-SQL допустимы любые типы данных, включая определяемые пользователем типы данных CLR и определяемые пользователем табличные типы, за исключением типа данных timestamp. Для функций CLR все типы данных, включая определяемые пользователем типы CLR, допускаются за исключением текста, ntext, изображения, определяемых пользователем типов таблиц и типов данных метки времени. Некаларовые типы, курсор и таблица не могут быть указаны в качестве типа данных параметров в функциях Transact-SQL или CLR.

Если type_schema_name не указан, ядро СУБД ищет scalar_parameter_data_type в следующем порядке:

Схема, содержащая имена системных типов данных SQL Server.
в установленной по умолчанию для текущего пользователя схеме в текущей базе данных;
Схема dbo в текущей базе данных.

[ = default ]

Значение по умолчанию для параметра. Если определено значение default, то функция выполняется даже в том случае, если для данного параметра значение не указано.

Значения параметров по умолчанию можно указать для функций CLR, за исключением типов данных varchar(max) и varbinary(max ).

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

READONLY

Указывает, что параметр нельзя обновить или изменить в определении функции. READONLY требуется для пользовательских параметров типа таблицы (TVPs) и не может использоваться для любого другого типа параметров.

return_data_type

Возвращаемое значение скалярной определяемой пользователем функции. Для функций Transact-SQL допустимы любые типы данных, включая определяемые пользователем типы данных CLR, кроме типа данных timestamp. Для функций CLR допустимы любые типы данных, включая определяемые пользователем типы данных CLR, кроме типов данных text, ntext, image и timestamp. Некаларовые типы, курсор и таблица не могут быть указаны в качестве возвращаемого типа данных в функциях Transact-SQL или CLR.

function_body

Указывает, что ряд инструкций Transact-SQL, которые вместе не создают побочный эффект, например изменение таблицы, определяют значение функции. function_body используется только в скалярных функциях и функциях с табличным значением из нескольких инструкций (MSTVF).

Для скалярных функций function_body представляет собой ряд инструкций Transact-SQL, совместное выполнение которых вычисляет скалярное выражение.

В MSTVFs function_body представляет собой ряд инструкций Transact-SQL, заполняющих возвращаемую TABLE переменную.

scalar_expression

Указывает скалярное значение, возвращаемое скалярной функцией.

TABLE

Указывает, что возвращаемым значением функции с табличным значением (TVF) является таблица. Функциям с табличным значением могут передаваться только константы и @local_variables.

В встроенных ТВФ TABLE возвращаемое значение определяется с помощью одной SELECT инструкции. Встроенные функции не имеют связанных возвращаемых переменных.

В MSTVFs @return_variable — это переменная, используемая TABLE для хранения и накапливания строк, возвращаемых в качестве значения функции. @Аргумент return_variable может быть указан только для функций Transact-SQL, но не для функций CLR.

select_stmt

SELECT Одна инструкция, определяющая возвращаемое значение встроенной табличной функции (TVF).

ORDER (<order_clause>)

Указывает порядок, в котором возвращаются результаты из табличной функции. Дополнительные сведения см. в разделе " Использование порядка сортировки в функциях с табличным значением clR" далее в этой статье.

EXTERNAL NAME <method_specifier>assembly_name.class_name.method_name

Область применения: SQL Server 2008 (10.0.x) с пакетом обновления 1 (SP 1) и более поздних версий.

Указывает сборку и метод, на которые должно ссылаться имя создаваемой функции.

assembly_name — должно соответствовать значению в столбце name инструкции SELECT * FROM sys.assemblies;.

Имя, которое использовалось в инструкции CREATE ASSEMBLY .
class_name — должно соответствовать значению в столбце assembly_name инструкции SELECT * FROM sys.assembly_modules;.

Часто это значение содержит точку или пунктир. В таких случаях синтаксис Transact-SQL требует, чтобы значение было привязано к паре квадратных квадратных скобок ([]) или с парой двойных кавычки ("").
method_name — должно соответствовать значению в столбце method_name инструкции SELECT * FROM sys.assembly_modules;.

Метод должен быть статическим.

В типичном примере, MyFood.dllв котором все типы находятся в MyFood пространстве имен, EXTERNAL NAME значение может быть MyFood.[MyFood.MyClass].MyStaticMethod.

По умолчанию SQL Server не производит выполнение кода CLR. Можно создавать, изменять и удалять объекты базы данных, ссылающиеся на модули среды CLR. Однако эти ссылки нельзя выполнить в SQL Server, пока не включите параметр clr. Для его включения воспользуйтесь хранимой процедурой sp_configure. Этот параметр недоступен в автономной базе данных.

<> table_type_definition ( { <column_definition column_constraint><| <> computed_column_definition } [ <table_constraint> ] [ , ...n ] )

Определяет тип данных таблицы для функции Transact-SQL. Объявление таблицы включает определения столбцов, а также ограничений для столбцов и таблиц. Таблица всегда помещается в первичную файловую группу.

<> clr_table_type_definition ( { column_namedata_type } [ , ...n ] )

Область применения: SQL Server 2008 (10.0.x) с пакетом обновления 1 (SP 1) и более поздних версий, а также База данных SQL Azure (предварительная версия в некоторых регионах).

Определяет табличные типы данных для функции CLR. Объявление таблицы включает только имена столбцов и типы данных. Таблица всегда помещается в первичную файловую группу.

NULL | NOT NULL

Поддерживается только для скомпилированных в собственном коде скалярных определяемых пользователем функций. Дополнительные сведения см. в разделе Скалярные определяемые пользователем функции для выполняющейся в памяти OLTP.

NATIVE_COMPILATION

Указывает, скомпилирована ли в собственном коде определяемая пользователем функция. Этот аргумент требуется только для скомпилированных в собственном коде скалярных определяемых пользователем функций.

BEGIN ATOMIC WITH

Обязательный и поддерживаемый только для скомпилированных скалярных пользовательских функций. Дополнительные сведения см. в разделе "Атомарные блоки" в машинных процедурах.

SCHEMABINDING

Аргумент SCHEMABINDING требуется для скомпилированных в собственном коде скалярных пользовательских функций.

EXECUTE AS

EXECUTE AS требуется для скомпилированных в собственном коде скалярных пользовательских функций.

<> function_option ::= и <clr_function_option> ::=

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

ШИФРОВАНИЕ

Область применения: SQL Server 2008 (10.0.x) с пакетом обновления 1 (SP 1) и более поздних версий.

Указывает, что ядро СУБД преобразует исходный текст CREATE FUNCTION инструкции в скрытый формат. Выходные данные обфускации не отображаются непосредственно в представлениях каталога. Пользователи, у которых нет доступа к системным таблицам или файлам базы данных, не могут получить скрытый текст. Однако текст доступен привилегированным пользователям, которые могут получить доступ к системным таблицам через диагностическое подключение для администраторов баз данных или напрямую получить доступ к файлам базы данных. Кроме того, пользователи, имеющие право на подключение отладчика к серверному процессу, могут получить исходный текст процедуры из памяти во время выполнения. Дополнительные сведения о доступе к метаданным системы см. в статье Настройка видимости метаданных.

Использование этого параметра предотвращает публикацию функции в рамках репликации SQL Server. Этот параметр нельзя указать для функций CLR.

SCHEMABINDING

Указывает, что функция привязана к объектам базы данных, которые содержат ссылки на нее. Если SCHEMABINDING задано, базовые объекты нельзя изменить таким образом, чтобы повлиять на определение функции. Сначала нужно изменить или удалить само определение функции, чтобы удалить зависимости от объекта, который требуется изменить.

Привязка функции к ссылающимся на нее объектам удаляется в следующих случаях:

При удалении функции.
При изменении функции инструкцией ALTER, если не указан параметр SCHEMABINDING.

Функция может быть привязана к схеме только в том случае, если выполняются следующие условия.

Функция является функцией Transact-SQL.
Пользовательские функции и представления, на которые ссылается данная функция, также привязаны к схеме.
Объекты, на которые ссылается функция, указываются двухкомпонентными именами.
Функция и объекты, на которые она ссылается, относятся к одной и той же базе данных.
Пользователь, выполняющий инструкцию CREATE FUNCTION, имеет разрешение REFERENCES на объекты базы данных, на которые ссылается функция.

RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT

Задает OnNULLCall атрибут скалярной функции. Если этот параметр не указан, CALLED ON NULL INPUT по умолчанию подразумевается. Другими словами, текст функции выполняется даже в том случае, если NULL передается в качестве аргумента.

Если RETURNS NULL ON NULL INPUT указана в функции CLR, она указывает, что SQL Server может возвращать NULL , когда любой из получаемых аргументов не NULLвызывает текст функции. Если метод функции CLR, указанной уже <method_specifier> имеет настраиваемый атрибут, RETURNS NULL ON NULL INPUTуказывающий, но CREATE FUNCTION инструкция указывает CALLED ON NULL INPUT, CREATE FUNCTION оператор имеет приоритет. Атрибут OnNULLCall нельзя указать для функций с табличным значением среды CLR.

EXECUTE AS

Указывает контекст безопасности, в котором выполняется определяемая пользователем функция. Поэтому можно отслеживать, какая учетная запись пользователя SQL Server используется для проверки разрешений на любые объекты базы данных, на которые ссылается функция.

EXECUTE AS Нельзя указать для встроенных табличных функций.

Дополнительные сведения см. в разделе EXECUTE AS (Transact-SQL).

INLINE = { ON | OFF }

Область применения: SQL Server 2019 (15.x) и более поздних версий и База данных SQL Azure.

Указывает, должна ли эта скалярная пользовательская функция быть встроенной. Это предложение применяется только к скалярным пользовательским функциям. Предложение INLINE не является обязательным. INLINE Если предложение не указано, оно автоматически устанавливается ON или OFF зависит от того, является ли UDF встроенным. Если INLINE = ON задано, но определяемая пользователем функция не является встроенной, возникает ошибка. Дополнительные сведения: Встраивание скалярной функции, определяемой пользователем.

<> column_definition ::=

Определяет тип данных таблицы. Декларация таблицы включает определения столбцов и ограничений. Для функций CLR можно указать только column_name и data_type.

column_name

Имя столбца в таблице. Имена столбцов должны соответствовать правилам для идентификаторов и быть уникальными в рамках таблицы. column_name может иметь длину от 1 до 128 символов.

data_type

Указывает тип данных столбца. Для функций Transact-SQL допустимы любые типы данных, включая определяемые пользователем типы данных CLR, кроме типа данных timestamp. Для функций CLR все типы данных, включая определяемые пользователем типы CLR, допускаются за исключением текста, ntext, image, char, varchar, varchar(max) и метки времени. Курсор типа nonscalar нельзя указать как тип данных столбца в функциях Transact-SQL или CLR.

DEFAULT constant_expression

Указывает значение, присваиваемое столбцу в случае отсутствия явно заданного значения при вставке. constant_expression является константой NULLили значением системной функции. DEFAULT определения можно применять к любому столбцу, кроме тех, которые имеют IDENTITY свойство. DEFAULT Нельзя указать для функций с табличным значением CLR.

COLLATE collation_name

Задает параметры сортировки для столбца. Если не указано, столбцу назначаются параметры сортировки, принятые в базе данных по умолчанию. Именем параметров сортировки может быть либо имя параметров сортировки Windows, либо имя параметров сортировки SQL. Список и дополнительные сведения о параметрах сортировки см. в разделах Имя параметра сортировки Windows (Transact-SQL) и Имя параметра сортировки SQL Server (Transact-SQL).

Предложение COLLATE можно использовать для изменения параметров сортировки только столбцов типов данных char, varchar, nchar и nvarchar. COLLATE Нельзя указать для функций с табличным значением CLR.

ROWGUIDCOL

Показывает, что новый столбец является строковым столбцом идентификаторов GUID. В качестве столбца можно указать только один столбец uniqueidentifier для каждой ROWGUIDCOL таблицы. Свойство ROWGUIDCOL может быть назначено только столбцу uniqueidentifier .

Свойство ROWGUIDCOL не применяет уникальность значений, хранящихся в столбце. Он также не создает значения для новых строк, вставленных в таблицу. Чтобы создать уникальные значения для каждого столбца, используйте функцию NEWID для INSERT инструкций. Можно указать значение по умолчанию; NEWID однако не удается указать значение по умолчанию.

IDENTITY

Указывает, что новый столбец является столбцом идентификаторов. При добавлении новой строки в таблицу SQL Server предоставляет уникальное добавочное значение для столбца. Столбцы удостоверений обычно используются вместе с PRIMARY KEY ограничениями для использования в качестве уникального идентификатора строки для таблицы. Свойство IDENTITY может назначаться столбцам типа tinyint, smallint, int, bigint, decimal(p,0) или numeric(p,0). Для каждой таблицы можно создать только один столбец идентификаторов. Привязанные значения по умолчанию и DEFAULT ограничениям нельзя использовать с столбцом удостоверений. Необходимо указывать либо оба аргумента (и seed, и increment), либо не указывать ни одного из них. Если ничего не указано, применяется значение по умолчанию (1,1).

IDENTITY Нельзя указать для функций с табличным значением CLR.

seed

Целочисленное значение, назначенное первой строке таблицы.

increment

Целочисленное значение, добавляемое в начальное значение для последовательных строк в таблице.

<> column_constraint ::= и <table_constraint> ::=

Определяет ограничение для указанного столбца или таблицы. Для функций CLR разрешен NULLединственный тип ограничения. Именованные ограничения не допускаются.

NULL | NOT NULL

Определяет, допустимы ли для столбца значения NULL. NULL не является строго ограничением, но может быть указан так же, как NOT NULL. NOT NULL Нельзя указать для функций с табличным значением CLR.

ПЕРВИЧНЫЙ КЛЮЧ

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

UNIQUE

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

CLUSTERED | NONCLUSTERED

Указывает, что кластеризованный или некластеризованный индекс создается для PRIMARY KEY или UNIQUE ограничения. PRIMARY KEY ограничения используются CLUSTEREDи UNIQUE используются NONCLUSTEREDограничения.

CLUSTERED можно указать только для одного ограничения. Если CLUSTERED для UNIQUE ограничения задано и PRIMARY KEY ограничение также указано, PRIMARY KEY используется NONCLUSTERED.

CLUSTERED и NONCLUSTERED не может быть указан для функций с табличным значением среды CLR.

ПРОВЕРКА

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

logical_expression

Логическое выражение, возвращающее TRUE или FALSE.

<> computed_column_definition ::=

Указывает вычисляемый столбец. Дополнительные сведения о вычисляемых столбцах см. в разделе CREATE TABLE (Transact-SQL).

column_name

Имя вычисляемого столбца.

computed_column_expression

Выражение, определяющее значение вычисляемого столбца.

<index_option> ::=

Указывает параметры индекса для PRIMARY KEY индекса или UNIQUE индекса. Дополнительные сведения о параметрах индекса см. в разделе CREATE INDEX (Transact-SQL).

PAD_INDEX = { ON | OFF }

Определяет разреженность индекса. Значение по умолчанию — OFF.

FILLFACTOR = fillfactor

Указывает процент, указывающий, насколько полный ядро СУБД должен сделать конечный уровень каждой страницы индекса во время создания или изменения индекса. Значение fillfactor должно быть целым числом от 1 до 100. По умолчанию установлено значение 0.

IGNORE_DUP_KEY = { ON | OFF }

Определяет ответ на ошибку, случающуюся, когда операция вставки пытается вставить в уникальный индекс повторяющиеся значения ключа. Параметр IGNORE_DUP_KEY применяется только к операциям вставки, производимым после создания или перестроения индекса. Значение по умолчанию — OFF.

STATISTICS_NORECOMPUTE = { ON | OFF }

Указывает, выполнялся ли перерасчет статистики распределения. Значение по умолчанию — OFF.

ALLOW_ROW_LOCKS = { ON | OFF }

Указывает, разрешена ли блокировка строк. Значение по умолчанию — ON.

ALLOW_PAGE_LOCKS = { ON | OFF }

Указывает, разрешена ли блокировка страниц. Значение по умолчанию — ON.

Типы данных

Если параметры указаны в функции CLR, они должны быть типами SQL Server, как определено ранее для scalar_parameter_data_type. Дополнительные сведения о сравнении системных типов данных SQL Server с типами данных интеграции CLR или платформа .NET Framework типами данных среды CLR см. в разделе "Сопоставление данных параметра CLR".

Чтобы SQL Server ссылался на правильный метод при перегрузке в классе, метод, указанный в <method_specifier> нем, должен иметь следующие характеристики:

Получение того же количества параметров, что и указано в [ , ...n ].
Принимать все параметры по значению, а не по ссылке.
Используйте типы параметров, совместимые с типами, указанными в функции SQL Server.

Если возвращаемый тип данных функции CLR задает тип таблицы (RETURNS TABLE), возвращаемый тип данных метода должен <method_specifier> быть типом IEnumerator или IEnumerable, и предполагает, что интерфейс реализуется создателем функции. В отличие от функций Transact-SQL, функции CLR не могут включать PRIMARY KEYUNIQUEили CHECK ограничения.<table_type_definition> Типы данных столбцов, указанные в <table_type_definition>, должны совпадать с типами данных соответствующих столбцов результирующего набора, возвращаемого методом в <method_specifier> во время выполнения. Эта проверка типа не выполняется во время создания функции.

Дополнительные сведения о программировании функций CLR см. в разделе Определяемые пользователем функции среды CLR.

Замечания

Скалярные функции можно вызывать, когда используются скалярные выражения, которые включают вычисляемые столбцы и CHECK определения ограничений. Скалярные функции также можно выполнять с помощью инструкции EXECUTE (Transact-SQL). Скалярные функции должны вызываться по крайней мере с помощью двух частей имени функции (<schema>.<function>). Дополнительные сведения о многокомпонентных именах см. в разделе Соглашения о синтаксисе в Transact-SQL (Transact-SQL). Функция, возвращающая табличное значение, может быть вызвана в любом месте, где допускаются табличные выражения, — в предложении FROMROM инструкций SELECT, INSERT, UPDATE или DELETE. Дополнительные сведения см. в разделе "Выполнение определяемых пользователем функций".

Совместимость

В функциях допустимы следующие инструкции.

Инструкции присваивания.
Инструкции управления потоком, за исключением инструкций TRY...CATCH.
Инструкции DECLARE, объявляющие локальные переменные и локальные курсоры.
Инструкции SELECT, которые содержат списки выбора с выражениями, присваивающими значения локальным переменным.
Операции над локальными курсорами, которые объявляются, открываются, закрываются и освобождаются в теле функции. Разрешены только FETCH инструкции, которые назначают значения локальным переменным с помощью INTO предложения. FETCH Операторы, возвращающие данные клиенту, не допускаются.
Инструкции INSERT, UPDATE и DELETE, которые изменяют локальные табличные переменные.
Инструкции EXECUTE, вызывающие расширенные хранимые процедуры.

Дополнительные сведения см. в разделе "Создание определяемых пользователем функций" (ядро СУБД).

Взаимодействие с вычисляемого столбца

Функции имеют перечисленные ниже свойства. Значения этих свойств определяют, может ли данная функция быть указана в вычисляемых столбцах, которые могут быть материализованными или индексированными.

Свойство	Описание	Основание
`IsDeterministic`	Функция детерминированная или недетерминированная.	Для детерминированных функций разрешается доступ к локальным данным. Например, функции, которые всегда возвращают один и тот же результат при каждом вызове с использованием определенного набора входных значений, и с тем же состоянием базы данных будет помечено детерминированным.
`IsPrecise`	Функция точная или неточная.	Неточные функции содержат такие операции, как операции с плавающей запятой.
`IsSystemVerified`	Свойства точности и детерминизма функции можно проверить SQL Server.
`SystemDataAccess`	Функция обращается к системным данным (системным каталогам или таблицам виртуальной системы) в локальном экземпляре SQL Server.
`UserDataAccess`	Функция обращается к данным пользователя в локальном экземпляре SQL Server.	Сюда входят определяемые пользователем и временные таблицы, но не табличные переменные.

Для функций Transact-SQL свойства точности и детерминизма SQL Server определяет автоматически. Свойства доступа к данным и детерминированности функций CLR могут быть указаны пользователем. Дополнительные сведения см. в разделе интеграции СРЕДЫ CLR: настраиваемые атрибуты для подпрограмм CLR.

Чтобы отобразить текущие значения этих свойств, используйте OBJECTPROPERTYEX (Transact-SQL).

Внимание

Функции необходимо создавать с детерминированным SCHEMABINDING.

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

IsDeterministic имеет значение true.
IsSystemVerified is true (если вычисляемый столбец не сохраняется)
UserDataAccess имеет значение false.
SystemDataAccess имеет значение false.

Дополнительные сведения см. в разделе "Индексы" для вычисляемых столбцов.

Вызов расширенных хранимых процедур из функций

Расширенная хранимая процедура при вызове из функции не может возвращать результирующие наборы клиенту. Все API ODS, возвращающие результирующие наборы клиенту, возвращаются FAIL. Расширенная хранимая процедура может подключиться к экземпляру SQL Server; однако не следует пытаться присоединиться к той же транзакции, что и функция, которая вызвала расширенную хранимую процедуру.

Как и вызовы из пакета или хранимой процедуры, расширенная хранимая процедура выполняется в контексте учетной записи безопасности Windows, в которой выполняется SQL Server. Владелец хранимой процедуры должен учитывать этот сценарий при предоставлении EXECUTE ему разрешения пользователям.

Ограничения

Определяемые пользователем функции нельзя использовать для выполнения действий, изменяющих состояние базы данных.

Определяемые пользователем функции не могут содержать OUTPUT INTO предложение, содержащее таблицу в качестве целевой цели.

Следующие инструкции Service Broker нельзя включить в определение определяемой пользователем функции Transact-SQL:

BEGIN DIALOG CONVERSATION
END CONVERSATION
GET CONVERSATION GROUP
MOVE CONVERSATION
RECEIVE
SEND

Определяемые пользователем функции могут быть вложенными, то есть из одной функции может быть вызвана другая. Уровень вложенности увеличивается на единицу каждый раз, когда начинается выполнение вызванной функции и уменьшается на единицу, когда ее выполнение завершается. Вложенность определяемых пользователем функций не может превышать 32 уровней. Превышение максимального уровня вложенности приводит к ошибке выполнения для всей цепочки вызываемых функций. Каждый вызов управляемого кода из определяемой пользователем функции Transact-SQL считается одним уровнем вложенности из 32 возможных. Это ограничение не распространяется на методы, вызываемые из управляемого кода.

Использование порядка сортировки в функциях с табличным значением среды CLR

При использовании предложения ORDER в функциях CLR с табличным значением придерживайтесь следующих рекомендаций.

Необходимо гарантировать, чтобы результаты всегда были упорядочены в указанном порядке. Если результаты не указаны в указанном порядке, SQL Server создает сообщение об ошибке при выполнении запроса.
Если указано предложение ORDER, выходные данные функции с табличным значением должны быть отсортированы в соответствии с параметрами сортировки столбца (явными или неявными). Например, если параметры сортировки столбцов являются китайскими, возвращаемые результаты должны быть отсортированы в соответствии с правилами сортировки китайского языка. (Параметры сортировки указываются в DDL для функции с табличным значением или из параметров сортировки базы данных.)
SQL Server всегда проверяет ORDER предложение, если указано, возвращая результаты, независимо от того, используется ли обработчик запросов для дальнейшей оптимизации. Используйте ORDER предложение только в том случае, если вы знаете, что это полезно для обработчика запросов.
Обработчик запросов SQL Server автоматически использует ORDER предложение в следующих случаях:
- Запросы Insert, в которых предложение ORDER совместимо с индексом.
- Предложения ORDER BY, совместимые с предложением ORDER.
- Статистические выражения, где GROUP BY совместим с предложением ORDER.
- Статистические выражения с ключевым словом DISTINCT, в которых уникальные столбцы совместимы с предложением ORDER.

Предложение ORDER не гарантирует упорядоченные результаты при SELECT выполнении запроса, если ORDER BY в запросе также не указано. Сведения о запросе столбцов, включенных в порядок сортировки для функций с табличным значением, см. в разделе sys.function_order_columns (Transact-SQL).

Метаданные

В следующей таблице приводятся системные представления каталога, возвращающие метаданные об определяемых пользователем функциях.

Системное представление	Description
sys.sql_modules	См. пример E в разделе "Примеры".
sys.assembly_modules	Выводит сведения об определяемых пользователем функциях CLR.
sys.parameters	Выводит сведения о параметрах, определенных в определяемых пользователем функциях.
sys.sql_expression_dependencies	Отображает базовые объекты, на которые ссылается функция.

Разрешения

Требуется разрешение CREATE FUNCTION на базу данных и разрешение ALTER для схемы, в которой создается функция. Если в функции указан определяемый пользователем тип, требуется разрешение EXECUTE на этот тип.

Примеры

Дополнительные примеры и рекомендации по производительности для определяемых пользователем функций см. в разделе "Создание определяемых пользователем функций" (ядро СУБД).

А. Использование скалярной определяемой пользователем функции, которая вычисляет неделю ISO

В следующем примере показано создание определяемой пользовательской функции ISOweek, которая получает в качестве аргумента дату и вычисляет номер недели по ISO. Для правильной работы этой функции перед ее вызовом должна быть выполнена инструкция SET DATEFIRST 1.

В примере также показано использование предложения EXECUTE AS (Transact-SQL) для указания контекста безопасности, в котором можно выполнить хранимую процедуру. В примере параметр CALLER указывает, что процедура выполняется в контексте пользователя, вызывающего его. Также могут быть указаны параметры SELF, OWNER и user_name.

Вот вызов функции. DATEFIRST задан как 1.

CREATE FUNCTION dbo.ISOweek (@DATE DATETIME)
RETURNS INT
WITH EXECUTE AS CALLER
AS
BEGIN
    DECLARE @ISOweek INT;

    SET @ISOweek = DATEPART(wk, @DATE) + 1 -
        DATEPART(wk, CAST(DATEPART(yy, @DATE) AS CHAR(4)) + '0104');

    --Special cases: Jan 1-3 may belong to the previous year
    IF (@ISOweek = 0)
        SET @ISOweek = dbo.ISOweek(CAST(DATEPART(yy, @DATE) - 1 AS CHAR(4))
           + '12' + CAST(24 + DATEPART(DAY, @DATE) AS CHAR(2))) + 1;

    --Special case: Dec 29-31 may belong to the next year
    IF ((DATEPART(mm, @DATE) = 12)
        AND ((DATEPART(dd, @DATE) - DATEPART(dw, @DATE)) >= 28))
    SET @ISOweek = 1;

    RETURN (@ISOweek);
END;
GO

SET DATEFIRST 1;

SELECT dbo.ISOweek(CONVERT(DATETIME, '12/26/2004', 101)) AS 'ISO Week';

Вот результирующий набор.

ISO Week
----------------
52

B. Создание встроенной табличной функции

В следующем примере возвращается встроенная табличная функция в базе данных AdventureWorks2022. Для каждого из товаров, проданных в магазине, она возвращает три столбца: ProductID, Name и статистику с начала года по магазину — YTD Total.

CREATE FUNCTION Sales.ufn_SalesByStore (@storeid INT)
RETURNS TABLE
AS
RETURN (
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
    FROM Production.Product AS P
    INNER JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
    INNER JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    INNER JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
    WHERE C.StoreID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

При вызове этой функции выполняется следующий запрос.

SELECT * FROM Sales.ufn_SalesByStore (602);

В. Создание функции с табличным значением с несколькими операторами

В следующем примере создается табличная функция fn_FindReports(InEmpID) в AdventureWorks2022 базе данных. Если ей передать допустимый идентификатор сотрудника, она вернет таблицу, в которой содержатся все сотрудники, которые прямо или опосредованно перед ним отчитываются. В функции для построения иерархического списка сотрудников используется рекурсивное обобщенное табличное выражение (CTE). Дополнительные сведения о рекурсивных обобщенных табличных выражениях см. в разделе WITH common_table_expression (Transact-SQL).

CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INT)
RETURNS @retFindReports TABLE (
    EmployeeID INT PRIMARY KEY NOT NULL,
    FirstName NVARCHAR(255) NOT NULL,
    LastName NVARCHAR(255) NOT NULL,
    JobTitle NVARCHAR(50) NOT NULL,
    RecursionLevel INT NOT NULL
    )
    --Returns a result set that lists all the employees who report to the
    --specific employee directly or indirectly.
AS
BEGIN
    WITH EMP_cte (
        EmployeeID,
        OrganizationNode,
        FirstName,
        LastName,
        JobTitle,
        RecursionLevel
        ) -- CTE name and columns
    AS (
        -- Get the initial list of Employees for Manager n
        SELECT e.BusinessEntityID,
            OrganizationNode = ISNULL(e.OrganizationNode, CAST('/' AS HIERARCHYID)),
            p.FirstName,
            p.LastName,
            e.JobTitle,
            0
        FROM HumanResources.Employee e
        INNER JOIN Person.Person p
            ON p.BusinessEntityID = e.BusinessEntityID
        WHERE e.BusinessEntityID = @InEmpID
        
        UNION ALL
        
        -- Join recursive member to anchor
        SELECT e.BusinessEntityID,
            e.OrganizationNode,
            p.FirstName,
            p.LastName,
            e.JobTitle,
            RecursionLevel + 1
        FROM HumanResources.Employee e
        INNER JOIN EMP_cte
            ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
        INNER JOIN Person.Person p
            ON p.BusinessEntityID = e.BusinessEntityID
        )
    -- Copy the required columns to the result of the function
    INSERT @retFindReports
    SELECT EmployeeID,
        FirstName,
        LastName,
        JobTitle,
        RecursionLevel
    FROM EMP_cte

    RETURN
END;
GO

-- Example invocation
SELECT EmployeeID,
    FirstName,
    LastName,
    JobTitle,
    RecursionLevel
FROM dbo.ufn_FindReports(1);
GO

D. Создание функции CLR

В следующем примере создается функция CLR len_s. Перед ее созданием сборка SurrogateStringFunction.dll регистрируется в локальной базе данных.

Область применения: SQL Server 2008 (10.0.x) с пакетом обновления 1 (SP 1) и более поздних версий.

DECLARE @SamplesPath NVARCHAR(1024);

-- You may have to modify the value of this variable if you have
-- installed the sample in a location other than the default location.
SELECT @SamplesPath = REPLACE(physical_name,
    'Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf',
    'Microsoft SQL Server\130\Samples\Engine\Programmability\CLR\'
)
FROM master.sys.database_files
WHERE name = 'master';

CREATE ASSEMBLY [SurrogateStringFunction]
FROM @SamplesPath + 'StringManipulate\CS\StringManipulate\bin\debug\SurrogateStringFunction.dll'
    WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

CREATE FUNCTION [dbo].[len_s] (@str NVARCHAR(4000))
RETURNS BIGINT
AS
EXTERNAL NAME [SurrogateStringFunction].[Microsoft.Samples.SqlServer.SurrogateStringFunction].[LenS];
GO

Пример создания функции CLR с табличным значением см. в разделе Функции среды CLR с табличным значением.

Е. Отображение определения определяемых пользователем функций

SELECT DEFINITION,
    type
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
    ON m.object_id = o.object_id
    AND type IN ('FN', 'IF', 'TF');
GO

Определение функций, созданных с помощью ENCRYPTION параметра, невозможно просмотреть с помощью sys.sql_modules; однако отображаются другие сведения о зашифрованных функциях.

Поделиться через

CREATE FUNCTION (Transact-SQL)

Синтаксис

Аргументы

OR ALTER

schema_name

function_name

@parameter_name

[ type_schema_name. ] parameter_data_type

[ = default ]

READONLY

return_data_type

function_body

scalar_expression

TABLE

select_stmt

ORDER (<order_clause>)

EXTERNAL NAME <method_specifier>assembly_name.class_name.method_name

<> table_type_definition ( { <column_definition column_constraint><| <> computed_column_definition } [ <table_constraint> ] [ , ...n ] )

<> clr_table_type_definition ( { column_namedata_type } [ , ...n ] )

NULL | NOT NULL

NATIVE_COMPILATION

BEGIN ATOMIC WITH

SCHEMABINDING

EXECUTE AS

<> function_option ::= и <clr_function_option> ::=

ШИФРОВАНИЕ

SCHEMABINDING

RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT

EXECUTE AS

INLINE = { ON | OFF }

<> column_definition ::=

column_name

data_type

DEFAULT constant_expression

COLLATE collation_name

ROWGUIDCOL

IDENTITY

seed

increment

<> column_constraint ::= и <table_constraint> ::=

NULL | NOT NULL

ПЕРВИЧНЫЙ КЛЮЧ

UNIQUE

CLUSTERED | NONCLUSTERED

ПРОВЕРКА

logical_expression

<> computed_column_definition ::=

column_name

computed_column_expression

<index_option> ::=

PAD_INDEX = { ON | OFF }

FILLFACTOR = fillfactor

IGNORE_DUP_KEY = { ON | OFF }

STATISTICS_NORECOMPUTE = { ON | OFF }

ALLOW_ROW_LOCKS = { ON | OFF }

ALLOW_PAGE_LOCKS = { ON | OFF }

Рекомендации

Типы данных

Замечания

Совместимость

Взаимодействие с вычисляемого столбца

Вызов расширенных хранимых процедур из функций

Ограничения

Использование порядка сортировки в функциях с табличным значением среды CLR

Метаданные

Разрешения

Примеры

А. Использование скалярной определяемой пользователем функции, которая вычисляет неделю ISO

B. Создание встроенной табличной функции

В. Создание функции с табличным значением с несколькими операторами

D. Создание функции CLR

Е. Отображение определения определяемых пользователем функций

Связанный контент

Обратная связь

Дополнительные ресурсы