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

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

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

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

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

документы:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SELECT function_name(field1) FROM table

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

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

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

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

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

мои 2 цента

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

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

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

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

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

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