Пользовательские функции

Статья
11/15/2023

Применяется к:SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure Управляемый экземпляр SQL Azure sql analytics в хранилище Microsoft Fabricв Microsoft Fabric

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

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

Зачем нужны определяемые пользователем функции (UDF)?

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

Функции CLR обеспечивают значительное преимущество производительности функций Transact-SQL для вычислительных задач, обработки строк и бизнес-логики. Функции Transact-SQL лучше подходят для логики интенсивного доступа к данным.
Уменьшение сетевого трафика. Операция, которая фильтрует данные на основе некоторых сложных ограничений, которые нельзя выразить в одном скалярном выражении, можно выразить как функцию. Ее можно вызвать из предложения WHERE, чтобы уменьшить число строк, возвращаемых клиенту.

Важно!

Определяемые пользователем функции Transact-SQL в запросах могут выполняться только в одном потоке (последовательном плане выполнения). Поэтому использование определяемых пользователем функций запрещает параллельную обработку запросов. Дополнительные сведения о параллельной обработке запросов см. в статье Руководство по архитектуре обработки запросов.

Типы функций

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

Пользовательские скалярные функции возвращают одно значение типа данных, заданного в предложении RETURNS. Для встроенной скалярной функции возвращаемое скалярное значение является результатом одной инструкции. Для скалярной функции с несколькими статистиками текст функции может содержать ряд инструкций Transact-SQL, возвращающих одно значение. Такие функции могут возвращать любые типы данных, кроме text, ntext, image, cursorи timestamp. Примеры см. в статье "Создание определяемых пользователем функций (ядро СУБД)".

Функции с табличным значением

Определяемые пользователем табличные функции (TVFs) возвращают тип данных таблицы . Встроенная функция с табличным значением не имеет текста, таблица является результирующим набором одной инструкции. Примеры см. в статье "Создание определяемых пользователем функций (ядро СУБД)".

Системные функции

SQL Server предоставляет множество системных функций, которые можно использовать для выполнения различных операций. Их нельзя изменить. Дополнительные сведения см. в статьях о встроенных функциях (Transact-SQL), системных хранимых функциях (Transact-SQL) и динамических административных представлениях и функциях (Transact-SQL).

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

Ошибки Transact-SQL, которые вызывают отмену инструкции и продолжаются с следующей инструкцией в модуле (например, триггеры или хранимые процедуры), обрабатываются по-разному внутри функции. В функциях такие ошибки вызывают остановку выполнения функции. Это вызывает отмену инструкции, вызвавшей функцию.

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

CREATE FUNCTION Если инструкция создает побочные эффекты для ресурсов, которые не существуют при CREATE FUNCTION выпуске инструкции, SQL Server выполняет инструкцию. Однако SQL Server не выполняет функцию при вызове.

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

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

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

Инструкции, допустимые в функциях

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

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

Встроенные системные функции

Следующие недетерминированные встроенные функции могут быть использованы в определяемых пользователем функциях языка Transact-SQL.

CURRENT_TIMESTAMP
GET_TRANSMISSION_STATUS
GETDATE
GETUTCDATE
@@CONNECTIONS
@@CPU_BUSY
@@DBTS
@@IDLE
@@IO_BUSY
@@MAX_CONNECTIONS
@@PACK_RECEIVED
@@PACK_SENT
@@PACKET_ERRORS
@@TIMETICKS
@@TOTAL_ERRORS
@@TOTAL_READ
@@TOTAL_WRITE

Следующие недетерминированные встроенные функции нельзя использовать в определяемых пользователем функциях на языке Transact-SQL.

NEWID
NEWSEQUENTIALID
RAND
TEXTPTR

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

Привязанные к схеме функции

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

Перед указанием предложения SCHEMABINDING в инструкции CREATE FUNCTION нужно соблюсти перечисленные ниже условия.

Все представления и пользовательские функции, к которым обращается функция, должны быть привязаны к схеме.
Все объекты, к которым обращается функция, должны находиться в той же базе данных, что и функция. Обращение к объектам должно производиться по однокомпонентным либо двухкомпонентным именам.
Для всех объектов (таблиц, представлений и пользовательских функций), к которым обращается функция, должно быть получено разрешение REFERENCES.

Для удаления привязки к схеме можно использовать инструкцию ALTER FUNCTION. В инструкции ALTER FUNCTION следует переопределить функцию без указания предложения WITH SCHEMABINDING.

Указание параметров

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