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

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

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

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

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

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

Мои 2 цента

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

SELECT function_name(field1) FROM table

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

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

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

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

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

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

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

If a stored procedure meets the following criteria, it is a good candidate for being rewritten as a table-valued function:

• The logic is expressible in a single SELECT statement but is a stored procedure, rather than a view, only because of the need for parameters.

• The stored procedure does not perform update operations, except to table variables.

• There is no need for dynamic EXECUTE statements.

• The stored procedure returns one result set.

• The primary purpose of the stored procedure is to build intermediate results that are to be loaded into a temporary table, which is then queried in a SELECT statement.

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

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

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

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

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

Я провел несколько тестов с длительной логикой, с одним и тем же фрагментом кода (длинным оператором 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

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

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

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

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

4. Функции могут быть вызваны из процедуры, тогда как процедуры не могут быть вызывается из функции.

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

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

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

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

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

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

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

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

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

  A non deterministic function is that function which returns different outputs for same input values at different time, like getdate(). It always returns different value whenever it is run.

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

  Earlier versions of sql server prior to sql 2000 do not allow to use getdate() function in user defined functions, but version 2005 and onward allows us to use getdate() function within a user defined function.

  Newid() is another example of non deterministic function but cannot be used in user defined functions but we can use it in stored procedure.

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

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

Хранимые процедуры - это предварительно скомпилированные запросы, которые выполняются быстрее и избавляют от SQL-инъекций. Они могут возвращать 0 или N значений. Мы можем выполнять операции DML внутри хранимых процедур. Мы можем использовать функции внутри процедур и можем использовать функции в запросе выбора. Функции используются для возврата любого значения, а операции DML невозможны в функциях. функции бывают двух типов: скалярные и табличнозначные. скалярная функция возвращает одно значение, функция с табличным значением, используемая для возврата строк таблиц.