Пользовательские функции
Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure конечную точку аналитики SQL в хранилище 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) возвращают тип данных таблицы . Для встроенной табличной функции нет текста функции; Таблица — это результирующий набор одной инструкции SELECT. Примеры см. в статье "Создание определяемых пользователем функций (ядро СУБД)".
Системные функции
SQL Server предоставляет множество системных функций, которые можно использовать для выполнения различных операций. Их нельзя изменить. Дополнительные сведения см. в разделе "Что такое функции базы данных 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 или более входных параметров и возвращать либо скалярное, либо табличное значение. Функция может иметь не более 1 024 входных параметров. Если параметр функции имеет значение по умолчанию, ключевое слово DEFAULT
должно быть указано при вызове функции, чтобы получить значение по умолчанию. Это поведение отличается от использования параметров со значениями по умолчанию в пользовательских хранимых процедурах, в которых пропущенный параметр также принимает значение по умолчанию. Определяемые пользователем функции не поддерживают выходные параметры.
Связанный контент
- Создание определяемых пользователем функций (ядро СУБД)
- Создание функций CLR
- Создание определяемых пользователем агрегатов
- Изменение определяемых пользователем функций
- Удаление определяемых пользователем функций
- Выполнение определяемых пользователем функций
- Переименование определяемых пользователем функций
- Просмотр определяемых пользователем функций