Функции против хранимых процедур

Ответ 1

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

Как правило, существует иерархия (View < TV Function < Stored Proc). Вы можете делать больше в каждом, но способность компоновки выходов и для того, чтобы оптимизатор действительно задействован, уменьшается по мере увеличения функциональности.

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

Ответ 2

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

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

Но они очень полезны для табличных возвратов в очень конкретных случаях.

Если вам нужно разобрать список с разделителями-запятыми, чтобы имитировать передачу массива в процедуру, функция может превратить список в таблицу для вас. Это обычная практика с Sql Server 2005, поскольку мы еще не можем передавать таблицы в хранимые процедуры (мы можем с 2008 года).

Ответ 3

Из документов:

Если хранимая процедура соответствует следующим критериям, она является хорошим кандидатом для перезаписи в виде табличной функции:

• Логика выражается в одном выражении SELECT, но является хранимой процедурой, а не представлением, только из-за необходимости в параметрах.

• Хранимая процедура не выполняет операции обновления, кроме переменных таблицы.

• Нет необходимости в динамических операциях EXECUTE.

• Хранимая процедура возвращает один результирующий набор.

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

Ответ 4

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

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

• Мы не можем использовать недетерминированные функции в функциях, но мы можем использовать не детерминированные функции в хранимых процедурах. Теперь возникает вопрос, что является недетерминированной функцией. Ans: -

  Не детерминированная функция - это функция, которая возвращает разные выходы для одинаковых входных значений в разное время, например getdate(). Он всегда возвращает различное значение всякий раз, когда он запускается.

  Исключение: -

  Более ранние версии sql-сервера до sql 2000 не позволяют использовать функцию getdate() в пользовательских функциях, но версия 2005 и далее позволяет нам использовать функцию getdate() в пределах определенной пользователем функции.

  Newid() - еще один пример недетерминированной функции, но не может использоваться в пользовательских функциях, но мы можем использовать его в хранимой процедуре.

• Мы можем использовать инструкции DML (вставить, обновить, удалить) в сохраненных но мы не можем использовать инструкции DML в функциях на физическом таблиц или постоянных таблиц. Если мы хотим выполнить операцию DML в функции мы можем сделать это над табличными переменными не на постоянных таблицах.

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

Ответ 5

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

• Процедуры могут иметь параметры ввода/вывода для него, тогда как функции могут иметь только входные параметры.

• Процедура позволяет выбирать, а также оператор DML в нем, тогда как функция разрешает в нем только оператор select.

• Функции можно вызывать из процедуры, тогда как процедуры не могут быть вызванный из функции.

• Исключение может быть обработано блоком try-catch в процедуре, тогда как блок try-catch не может использоваться в функции.

• Мы можем пойти для управления транзакциями в процедуре, тогда как мы не можем войти в функцию.

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

• UDF (функция, определяемая пользователем) может использоваться в операторах SQL в любом месте раздела WHERE/HAVING/SELECT, тогда как хранимые процедуры не могут быть.

• UDF, возвращающие таблицы, можно рассматривать как другой набор строк. Это можно использовать в JOIN с другими таблицами.

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

Ответ 6

Если у вас есть функция, вы можете использовать ее как часть вашего оператора SQL, например

SELECT function_name(field1) FROM table

Это не работает для хранимых процедур.

Ответ 7

Я провел несколько тестов с длинным ходом логики с одним и тем же кодом (длинным оператором SELECT), выполняемым как в таблице, так и в хранимой процедуре, и в прямом EXEC/SELECT, и каждый из них выполнялся одинаково.

По-моему, всегда используйте функцию с табличной оценкой, а не хранимую процедуру, чтобы вернуть результирующий набор, поскольку он делает логику намного проще и читабельнее в запросах, которые впоследствии присоединяются к ним, и позволяет вам повторно использовать ту же логику. Чтобы избежать слишком большого количества ударов по производительности, я часто использую "необязательные" параметры (т.е. Вы можете передать им NULL), чтобы включить функцию для возврата набора результатов быстрее, например:

CREATE FUNCTION dbo.getSitePermissions(@RegionID int, @optPersonID int, optSiteID int)
AS
RETURN 
    SELECT DISTINCT SiteID, PersonID
    FROM dbo.SiteViewPermissions
    WHERE (@optPersonID IS NULL OR @optPersonID = PersonID)
    AND (@optSiteID IS NULL OR @optSiteID = SiteID)
    AND @RegionID = RegionID

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

SELECT * FROM dbo.getSitePermissions(@RegionID) WHERE SiteID = 1

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

Ответ 8

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

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

Мои 2 цента

Ответ 9

Как упоминалось выше, функции более читабельны/скомпилированы/самодокументированы, но в целом они менее эффективны и могут быть значительно менее эффективными, если вы увлечены ими в таких объединениях, как

SELECT *
FROM dbo.tvfVeryLargeResultset1(@myVar1) tvf1
INNER JOIN dbo.tvfVeryLargeResultset1(@myVar2) tvf2
    ON (tvf1.JoinId = tvf2.JoinId)

Часто вам просто нужно принять избыточность кода, которую может устранить tvf (при неприемлемой стоимости исполнения.)

Еще один момент, о котором я еще не упоминал, заключается в том, что вы не можете использовать временные таблицы изменения состояния базы данных внутри многозадачного tvf. Самый функционально эквивалентный механизм для таблицы temp является изменением состояния, измененным в ядре, в переменной таблицы памяти и для больших наборов данных временная таблица, вероятно, будет более высокой, чем переменная таблицы. (Другие альтернативы включают динамические таблицы и общие табличные выражения, но с некоторой степенью сложности они перестают быть хорошим вариантом ИМО.)

Ответ 10

Это зависит:) Если вы хотите использовать результат с табличной оценкой в ​​другой процедуре, вам лучше использовать функцию TableValued. Если результаты для клиента, сохраненный процесс, как правило, лучший способ пойти.

Ответ 11

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

Ответ 12

Для сохранения хранимой процедуры и функции:

Сохраненная процедура и функция на сервере MS Sql

Сохраненная процедура в MS Sql Server 2008