СОЗДАТЬ ФУНКЦИЮ (Transact-SQL)

2025-06-21

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

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

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

В этой статье рассматривается интеграция .NET Framework CLR в 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 ]
}

Аргументы

ИЛИ ALTER

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

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

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

schema_name

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

function_name

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

Круглые скобки обязательны после имени функции, даже если параметр не указан.

@parameter_name

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

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

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

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

[ type_schema_name. ] parameter_data_type

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

Если type_schema_name не указано, компонент Database Engine выполняет поиск scalar_parameter_data_type в следующем порядке:

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

[ = по умолчанию ]

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

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

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

ТОЛЬКО ДЛЯ ЧТЕНИЯ

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

return_data_type

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

function_body

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

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

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

scalar_expression

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

ТАБЛИЦА

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

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

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

select_stmt

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

ЗАКАЗ (<order_clause>)

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

ВНЕШНЕЕ ИМЯ <method_specifier>assembly_name.class_name. method_name

Область применения: SQL Server 2008 (10.0.x) 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. Можно создавать, изменять и удалять объекты базы данных, которые ссылаются на модули среды выполнения на общем языке. Однако вы не сможете выполнить эти ссылки в 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) SP 1 и более поздних версий, а также База данных SQL Azure (предварительная версия в некоторых регионах).

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

NULL | НЕ NULL

Поддерживается только для скомпилированных в исходном коде скалярных функций, определяемых пользователем. Дополнительные сведения см. в разделе Скалярные User-Defined функции для In-Memory OLTP.

NATIVE_COMPILATION

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

НАЧНИТЕ АТОМАРНОЕ С

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

SCHEMABINDING

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

ВЫПОЛНИТЬ КАК

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

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

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

ШИФРОВАНИЕ

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

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

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

SCHEMABINDING

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

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

Функция отбрасывается.
Функция изменяется с помощью ALTER оператора с SCHEMABINDING опцией not specified.

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

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

ВОЗВРАЩАЕТ NULL ПРИ ВВОДЕ NULL | ВЫЗЫВАЕТСЯ ПРИ ВВОДЕ NULL

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

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

ВЫПОЛНИТЬ КАК

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

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

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

ВСТРОЕННЫЙ = { ВКЛ | ВЫКЛ }

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

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

< > column_definition ::=

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

column_name

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

data_type

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

СТАНДАРТНЫЕ constant_expression

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

СОПОСТАВИТЬ collation_name

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

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

ROWGUIDCOL

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

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

ИДЕНТИЧНОСТЬ

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

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

семя

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

приращение

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

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

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

NULL | НЕ NULL

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

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

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

УНИКАЛЬНЫЙ

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

CLUSTERED | НЕКЛАСТЕРИЗОВАННЫЙ

Укажите, что для ограничения or UNIQUE создается PRIMARY KEY кластеризованный или некластеризованный индекс. 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 ::=

Задает вычисляемый столбец. Дополнительные сведения о вычисляемых столбцах см. в разделе СОЗДАТЬ ТАБЛИЦУ (Transact-SQL).

column_name

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

computed_column_expression

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

< > index_option ::=

Указывает параметры индекса для индекса PRIMARY KEY or UNIQUE . Дополнительные сведения о параметрах индекса см. в разделе СОЗДАТЬ ИНДЕКС (Transact-SQL).

PAD_INDEX = { ON | OFF }

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

FILLFACTOR = fillfactor

Указывает процент, указывающий, насколько компонентом Database Engine должен быть заполнен конечный уровень каждой страницы индекса во время создания или изменения индекса. 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.

Лучшие практики

Если пользовательская функция не создается с помощью предложения, SCHEMABINDING изменения, внесенные в базовые объекты, могут повлиять на определение функции и привести к неожиданным результатам при ее вызове. Рекомендуется реализовать один из следующих методов, чтобы убедиться, что функция не становится устаревшей из-за изменений в его базовых объектах:

Укажите WITH SCHEMABINDING предложение при создании функции. Этот параметр гарантирует, что объекты, на которые ссылается определение функции, не могут быть изменены, если функция также не будет изменена.
Выполнение хранимой процедуры sp_refreshsqlmodule после изменения любого объекта, указанного в определении функции.

Дополнительные сведения и рекомендации по производительности о встроенных функциях с табличным значением (встроенных TVF) и функциях с табличным значением с несколькими операторами (MSTVF) см. в разделе Создание пользовательских функций (компонент Database Engine).

Типы данных

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

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

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

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

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

Замечания

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

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

В функции допустимы следующие операторы:

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

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

Функциональная совместимость вычисляемых столбцов

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

Недвижимость	Описание	Примечания.
`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 это предложение в следующих случаях:
- Вставляйте запросы, ORDER в которых предложение совместимо с индексом.
- ORDER BY Положения, совместимые с этим пунктом ORDER .
- Агрегаты, где GROUP BY совместимо с ORDER предложением.
- DISTINCT агрегаты, в которых отдельные столбцы совместимы с предложением ORDER .

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

Метаданные

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

Системный вид	Описание
sys.sql_модули	Смотрите пример E в разделе Примеры.
sys.assembly_modules	Отображает информацию о функциях, определенных пользователем в среде CLR.
sys.parameters	Отображает информацию о параметрах, определенных в пользовательских функциях.
sys.sql_зависимостей_выражений	Отображает базовые объекты, на которые ссылается функция.

Разрешения

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

Примеры

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

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

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

В примере также показано использование предложения EXECUTE AS (Transact-SQL) для указания контекста безопасности, в котором может быть выполнена хранимая процедура. В примере опция CALLER указывает, что процедура выполняется в контексте пользователя, который ее вызывает. Другими параметрами, которые можно указать, являются , SELFOWNERи 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

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

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

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

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

Область применения: SQL Server 2008 (10.0.x) 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, возвращающей табличное значение, см. в разделе Функции Table-Valued 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, однако отображается другая информация о зашифрованных функциях.

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

СОЗДАТЬ ФУНКЦИЮ (Transact-SQL)

Синтаксис

Аргументы

ИЛИ ALTER

schema_name

function_name

@parameter_name

[ type_schema_name. ] parameter_data_type

[ = по умолчанию ]

ТОЛЬКО ДЛЯ ЧТЕНИЯ

return_data_type

function_body

scalar_expression

ТАБЛИЦА

select_stmt

ЗАКАЗ (<order_clause>)

ВНЕШНЕЕ ИМЯ <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 | НЕ NULL

NATIVE_COMPILATION

НАЧНИТЕ АТОМАРНОЕ С

SCHEMABINDING

ВЫПОЛНИТЬ КАК

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

ШИФРОВАНИЕ

SCHEMABINDING

ВОЗВРАЩАЕТ NULL ПРИ ВВОДЕ NULL | ВЫЗЫВАЕТСЯ ПРИ ВВОДЕ NULL

ВЫПОЛНИТЬ КАК

ВСТРОЕННЫЙ = { ВКЛ | ВЫКЛ }

< > column_definition ::=

column_name

data_type

СТАНДАРТНЫЕ constant_expression

СОПОСТАВИТЬ collation_name

ROWGUIDCOL

ИДЕНТИЧНОСТЬ

семя

приращение

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

NULL | НЕ NULL

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

УНИКАЛЬНЫЙ

CLUSTERED | НЕКЛАСТЕРИЗОВАННЫЙ

ПРОВЕРКА

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 (разрешить блокировку страниц) = { ВКЛ. | ВЫКЛ. }

Лучшие практики

Типы данных

Замечания

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

Функциональная совместимость вычисляемых столбцов

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

Ограничения

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

Метаданные

Разрешения

Примеры

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

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

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

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

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

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

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

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