Разница между скалярными, табличными и агрегатными функциями на SQL-сервере?

В чем разница между скалярно-значными, табличными и агрегатными функциями на SQL-сервере? И вызывает ли их вызов из запроса другой метод, или мы называем их одинаковым образом?

Ответ 1

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

Скалярные функции (иногда называемые пользовательскими функциями /UDF) возвращают одно значение как возвращаемое значение, а не как набор результатов, и могут использоваться в большинстве мест в запросе или в операторе SET, за исключением предложения FROM (и может в других местах?). Кроме того, скалярные функции можно вызывать через EXEC, как и хранимые процедуры, хотя использовать эту возможность не так часто (для получения более подробной информации об этой возможности см. Мой ответ на следующий вопрос о DBA.StackExchange: почему скаляр ценным функциям нужно разрешение на выполнение, а не выбор?). Они могут быть созданы как в T-SQL, так и в SQLCLR.

T-SQL (UDF):
- До SQL Server 2019: эти скалярные функции обычно являются проблемой производительности, потому что они обычно выполняются для каждой возвращенной строки (или отсканированной) и всегда запрещают параллельные планы выполнения.
- Начиная с SQL Server 2019: некоторые скалярные пользовательские функции T-SQL могут быть встроенными, то есть их определения помещаются непосредственно в запрос, так что запрос не вызывает UDF (аналогично тому, как работают iTVF (см. Ниже)). Существуют ограничения, которые могут помешать UDF быть встроенным (если раньше это не было ни слова, а сейчас), и UDF, которые могут быть встроены, не всегда будут встроены из-за нескольких факторов. Эта функция может быть отключена на уровне базы данных, запроса и отдельных уровней UDF. Для получения дополнительной информации об этой действительно крутой новой функции, пожалуйста, смотрите: Scalar UDF Inlining (обязательно ознакомьтесь с разделом "требования").
SQLCLR (UDF): эти скалярные функции также обычно выполняются для каждой строки, возвращенной или отсканированной, но есть два важных преимущества по сравнению с пользовательскими функциями T-SQL:
- Начиная с SQL Server 2012, возвращаемые значения можно постоянно сворачивать в план выполнения, если UDF не осуществляет доступ к данным, и если он помечен как IsDeterministic = true. В этом случае функция не будет запускаться для каждой строки.
- Скалярные функции SQLCLR могут работать в параллельных планах (😃), если они не осуществляют никакого доступа к базе данных.

Табличные функции

Табличные функции (TVF) возвращают наборы результатов и могут использоваться в FROM, JOIN или CROSS APPLY/OUTER APPLY любого запроса, но, в отличие от простых представлений, не могут быть целью каких-либо операторов DML (INSERT/UPDATE/DELETE) Они также могут быть созданы как в T-SQL, так и в SQLCLR.

T-SQL MultiStatement (TVF): эти TVF, как следует из их названия, могут иметь несколько операторов, аналогично хранимой процедуре. Какие бы результаты они ни собирались вернуть, они хранятся в табличной переменной и возвращаются в самом конце; то есть ничего не возвращается, пока функция не завершит обработку. Предполагаемое количество строк, которые они будут возвращать, как сообщается оптимизатору запросов (что влияет на план выполнения), зависит от версии SQL Server:
- До SQL Server 2014: они всегда сообщают 1 (да, только 1) строку.
- SQL Server 2014 и 2016: они всегда содержат 100 строк.
- Начиная с SQL Server 2017: по умолчанию сообщается о 100 строках, НО при некоторых условиях счетчик строк будет достаточно точным (на основе текущей статистики) благодаря новой функции чередующегося выполнения.
T-SQL Inline (iTVF): эти TVF могут быть только одним оператором, и этот оператор является полным запросом, как и View. Фактически, встроенные TVF - это, по сути, View, который принимает входные параметры для использования в запросе. Они также не кэшируют свой собственный план запроса, поскольку их определение помещается в запрос, в котором они используются (в отличие от других объектов, описанных здесь), поэтому их можно оптимизировать намного лучше, чем другие типы TVF (😃). Эти TVF работают достаточно хорошо и предпочтительнее, если логику можно обработать в одном запросе.
SQLCLR (TVF): эти TVF аналогичны многостадийным TVF T-SQL в том смысле, что они формируют весь набор результатов в памяти (даже если это файл подкачки/подкачки) перед тем, как выпустить его полностью в самом конце. Предполагаемое количество строк, которые они будут возвращать, как сообщается оптимизатору запросов (что влияет на план выполнения), всегда составляет 1000 строк. Учитывая, что фиксированное число строк далеко от идеального, поддержите мой запрос, чтобы разрешить указание количества строк: Разрешить TVF (T-SQL и SQLCLR) предоставлять определяемые пользователем оценки строк для оптимизатора запросов
Потоковая передача SQLCLR (sTVF): эти функции TVF допускают сложный код С#/VB.NET, как обычные TVF SQLCLR, но отличаются тем, что возвращают каждую строку в вызывающий запрос по мере их генерирования ((). Эта модель позволяет вызывающему запросу начать обработку результатов сразу после отправки первого, поэтому запросу не нужно ждать завершения всего процесса функции, прежде чем он увидит какие-либо результаты. И это требует меньше памяти, так как результаты не сохраняются в памяти, пока процесс не завершится. Предполагаемое количество строк, которые они будут возвращать, как сообщается оптимизатору запросов (что влияет на план выполнения), всегда составляет 1000 строк. Учитывая, что фиксированное число строк далеко от идеального, поддержите мой запрос, чтобы разрешить указание количества строк: Разрешить TVF (T-SQL и SQLCLR) предоставлять определяемые пользователем оценки строк для оптимизатора запросов

Агрегатные функции

Определяемые пользователем агрегаты (UDA) - это агрегаты, аналогичные SUM(), COUNT(), MIN(), MAX() и т.д., И обычно для них требуется предложение GROUP BY. Они могут быть созданы только в SQLCLR, и эта возможность была введена в SQL Server 2005. Кроме того, начиная с SQL Server 2008, UDA были расширены, чтобы учесть несколько входных параметров (😃). Особый недостаток заключается в том, что в группе нет сведений об упорядочении строк, поэтому создание промежуточного итога, которое было бы относительно простым, если бы упорядочение могло быть гарантировано, невозможно в рамках SAFE Assembly.

Пожалуйста, смотрите также:

СОЗДАТЬ ФУНКЦИЮ (документация MSDN)
СОЗДАТЬ АГРЕГАТ (документация MSDN)
Пример табличной функции CLR с полной потоковой передачей (STVF/TVF) (статья, которую я написал)

Ответ 2

Скалярная функция возвращает одно значение. Это может быть даже не связано с таблицами в вашей базе данных.

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

Агрегированная функция возвращает вычисление по строкам таблицы - например, суммируя значения.

Ответ 3

Функции Aggregate и Scalar возвращают одно значение, но функции Scalar работают на основе одного аргумента входного значения, в то время как функции Aggregate работают с одним входным набором значений (именем коллекции или столбца). Примерами скалярных функций являются строковые функции, ISNULL, ISNUMERIC, примерами агрегатных функций являются AVG, MAX и другие, которые можно найти в разделе " Агрегатные функции " на веб-сайте Microsoft.

Табличные функции возвращают таблицу независимо от наличия какого-либо входного аргумента. Выполнение этих функций выполняется с использованием их в качестве обычной физической таблицы, например: SELECT * FROM fnGetMulEmployee()

Следующая ссылка очень полезна, чтобы понять разницу: https://www.dotnettricks.com/learn/sqlserver/different-types-of-sql-server-functions

Ответ 4

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

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

Таблица Значения функции

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

CREATE FUNCTION <function name>(parameter datatype)

RETURN table

AS

RETURN

(

-- *write your query here* ---

)

Обратите внимание, что здесь нет операторов BEGIN и END.

Совокупные функции

Включает встроенные функции, которые используются вместе с предложением GROUP. Например: SUM(), MAX(), MIN(), AVG(), COUNT() являются агрегатными функциями.