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

Функции являются вычисляемыми значениями и не могут выполнять постоянные изменения среды SQL Server (т.е. не допускаются операторы INSERT или UPDATE).

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

Примечание из комментариев, которое подытоживает ответ. Спасибо @Sean K Anderson:.

Функции следуют определению компьютерной науки в том, что они ДОЛЖНЫ возвращать значение и не могут изменять данные, которые они получают в качестве параметров. (аргументы). Функциям не разрешается изменять что-либо, они должны иметь хотя бы один параметр и должны возвращать значение. Хранимые процедуры не обязательно должны иметь параметр, могут изменять объекты базы данных, и не должны возвращать значение.

Разница между SP и UDF перечислены ниже:

в

+---------------------------------+----------------------------------------+
| Stored Procedure (SP)           | Function (UDF - User Defined           |
|                                 | Function)                              |
+---------------------------------+----------------------------------------+
| SP can return zero , single or  | Function must return a single value    |
| multiple values.                | (which may be a scalar or a table).    |
+---------------------------------+----------------------------------------+
| We can use transaction in SP.   | We can't use transaction in UDF.       |
+---------------------------------+----------------------------------------+
| SP can have input/output        | Only input parameter.                  |
| parameter.                      |                                        |
+---------------------------------+----------------------------------------+
| We can call function from SP.   | We can't call SP from function.        |
+---------------------------------+----------------------------------------+
| We can't use SP in SELECT/      | We can use UDF in SELECT/ WHERE/       |
| WHERE/ HAVING statement.        | HAVING statement.                      |
+---------------------------------+----------------------------------------+
| We can use exception handling   | We can't use Try-Catch block in UDF.   |
| using Try-Catch block in SP.    |                                        |
+---------------------------------+----------------------------------------+

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

Функции, как правило, имеют выход и опционально входы. Затем данные могут быть использованы в качестве входных данных для другой функции (например, SQL сервер встроенный, таких как функция datediff, лен и т. д.), Либо как предикат в SQL-запрос - например, выберите, б, ДБО.Myfunction в консоли(C) из таблицы " или " выбрать А, B, C из таблицы, где = ДБО.MyFunc(с).

Хранимые процедуры используются, чтобы связать воедино запросы SQL в транзакции, и интерфейс с внешним миром. Структур, таких как ADO.NET и т. д. может'т вызвать функцию напрямую, но они могут вызвать хранимую proc и напрямую.

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

SELECT * FROM dbo.MyTable WHERE col1 = dbo.MyFunction(col2)

Где myfunction в консоли объявлен как:

CREATE FUNCTION MyFunction (@someValue INTEGER) RETURNS INTEGER
AS
BEGIN
   DECLARE @retval INTEGER

   SELECT localValue 
      FROM dbo.localToNationalMapTable
      WHERE nationalValue = @someValue

   RETURN @retval
END

То, что происходит здесь заключается в том, что функции myfunction вызывается для каждой строки в таблице mytable. Если таблица mytable имеет 1000 строк, то, что'ы еще 1000 нерегламентированных запросов к базе данных. Аналогично, если функция вызывается, когда указанным в спецификации столбца, то функция будет вызываться для каждой строки, возвращаемой на выбор.

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

Различия между хранимыми процедурами и пользовательскими функциями:

• Хранимые процедуры не могут использоваться в инструкции Select.
• Хранимые процедуры поддерживают отложенное разрешение имен. Хранимые процедуры обычно используются для выполнения бизнес-логики.
• Хранимые процедуры могут возвращать любой тип данных.
• Хранимые процедуры могут принимать большее количество входных параметров, чем пользовательские функции. Хранимые процедуры могут иметь до 21000 входных параметров.
• Хранимые процедуры могут выполнить динамический SQL.
• Хранимые процедуры поддержка обработки ошибок.
• Недетерминированные функции можно использовать в хранимых процедурах.

<ч/>

• Пользовательские функции могут использоваться в инструкции Select.
• Пользовательские функции не поддерживают отложенное разрешение имен.
• Пользовательские функции используются для вычислений.
• Пользовательские функции должны возвращать значение.
• Определяемые пользователем функции не могут возвращать изображения.
• Пользовательские функции принимают меньшее число входных параметров, чем хранимые процедуры. Пользовательские функции могут иметь до 1,023 входных параметров.
• Временные таблицы не могут быть использованы в пользовательских функциях.
• Определяемые пользователем функции не могут выполнить динамический SQL.
• Пользовательские функции не поддерживает обработку ошибок. RAISEERROR или @@ошибке не допускается в пользовательские функции.
• Недетерминированные функции не могут быть использованы в функции. Например, функции getdate()` не может быть использован в функции.

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

Основные Отличия

Функция должна возвращать значение, но в хранимой процедуре это необязательно( процедура может возвращать ноль или N значений).

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

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

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

Предварительная Разница

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

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

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

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

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

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

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

Источник

пользовательская функция-это важный инструмент, доступный программисту SQL-сервера. Вы можете использовать встроенные в SQL-оператор, как так

SELECT a, lookupValue(b), c FROM customers 

где lookupValue будет ОДС. Такой функционал не возможен при использовании хранимой процедуры. В то же время вы не можете делать определенные вещи внутри ОДС. Основная вещь, чтобы помнить здесь является то, что ОДС'ы:

• не может создавать постоянные изменения
• не могу изменить данные

хранимая процедура может сделать эти вещи.

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

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

Функции используются в качестве методов. Вы передадите ему что-то и возвращает результат. Должен быть маленький и быстрый - это на лету.

Хранимая процедура:

• Это как миниатюрная программа в SQL сервере.
• Может быть как простой, как инструкция select, или же сложным, как долго скрипт, который добавляет, удаляет, обновляет и/или считывает данные из нескольких таблицы в базе данных.
• (Может выполнять циклы и курсоры, которые позволяют работать с меньшие результаты или ряда операций подряд на данных.)
• Следует называть, используя метод exec или выполнить заявление.
• Таблицы возвращает переменных, но мы можем'т использовать параметр "снаружи".
• Поддерживает транзакции.

Функции:

• Не может быть использован для обновления, удаления или добавления записей в базу данных.
• Просто возвращает одно значение, или значение в таблице.
• Можно использовать только для выбора записей. Тем не менее, его можно назвать очень легко из стандартного SQL, такие как:

Выберите dbo.имя_функции('Параметр1')

или

Выберите имя, ДБО.Имя_функции('Параметр1') из таблице sysobjects

• Для простых многоразовые выберите операции, функции, можно упростить код. Просто будьте осторожны с использованием "присоединиться" предложения в вашей функции. Если ваше функция "присоединиться" предложение и вы называете это из другого выбора оператор, который возвращает несколько результатов, что вызов функции будет "присоединиться" эти таблицы вместе для каждого строки возвращаются в результирующем наборе. Так хотя они могут быть полезны для упрощения какая-то логика, они также могут быть снижение производительности, если они'повторно не используется должным образом.
• Возвращает значения через параметр выход.
• Не поддерживает транзакции.

Функции SQL-сервера, такие как курсоры, предназначены для использования в качестве последнего оружия! У них действительно есть проблемы с производительностью и, следовательно, используя табличное значение функции следует избегать, насколько это возможно. Про производительность говорить о столе с более чем 1,000,000 записей, размещенные на сервере на оборудовании среднего класса; в противном случае вы не'т нужно беспокоиться о производительности, вызванной функции.

1. Никогда не используйте функцию, чтобы возвратить результат-значение внешнего кода (как ADO.Net)
2. Использовать представления/хранимые как можно больше различных комбинаций. вы можете взыскать с будущем вырастают-проблем с производительностью, используя предложения ДТА (советник настройки базы данных) даст вам (как индексированные представления и статистика) --иногда!

для получения дополнительной информации см.: http://databases.aspfaq.com/database/should-i-use-a-view-a-stored-procedure-or-a-user-defined-function.html

Чтобы решить, когда что использовать, могут помочь следующие пункты...

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

2. Вы можете использовать хранимые процедуры для изменения параметров окружения сервера, а функции - нет.

спасибо

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

Затем, вы можете использовать функцию параметризованного списка городов. ДБО.GetCitiesIn (с"Нью-Йорк" У), которая возвращает таблицу, которая может использоваться как присоединиться.

Это'ы способ организации кода. Зная, когда что-то многоразовые, а когда это пустая трата времени что-то приобрел только путем проб и ошибок и опыта.

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

• Он является обязательным для функции возвращают значение, хотя это не для хранимой процедуры.
• Выбор заявления принимаются только в формате UDF, а DML-инструкций не требуется.
• Хранимая процедура принимает любые заявления, а также инструкции DML.
• ОДС позволяет только входы, а не выходы.
• Хранимая процедура позволяет для обоих входов и выходов.
• Блоков catch не может быть использован в ОДС, но может быть использован в хранимую процедуру.
• Никаких сделок допускается в функции ОДС, но в хранимые процедуры, которые им разрешены.
• Могу только табличные переменные использоваться в ОДС, а не временные таблицы.
• Хранимая процедура позволяет как табличные переменные и временные таблицы.
• ОДС не допускает хранимые процедуры из функции, в то время как хранимые процедуры позволяют вызывающей функции.
• ОДС используется в предложении Join в то время как хранимые процедуры не могут быть использованы в предложении Join.
• Хранимая процедура всегда позволит вернуться к нулю. ОДС, наоборот, имеет ценности, которые должны прийти обратно на заданную точку.

Здесь'ы практическая причина предпочесть функций хранимых процедур. Если у вас есть хранимая процедура, которая нуждается в результатах другой хранимой процедуры, вы должны использовать команду вставка-инструкции exec. Это означает, что вы должны создать временную таблицу и использовать метод exec заявление, чтобы вставить результаты хранимой процедуры во временную таблицу. Это'ы грязный. Одна проблема с этим является то, что вставка-боссы не могут быть вложенными.

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

(это еще одна причина, по которой мы должны держать бизнес-логику из базы данных)

Пользовательские Функции.

1. Функция должна возвращать значение.
2. Позволит выбрать только заявления, не позволяет нам использовать операторы DML.
3. Это позволит только входные параметры, не'т поддерживать выходные параметры.
4. Он не позволит нам использовать try-catch блоки.
5. Сделок не допускается в пределах функции.
6. Мы можем использовать только табличные переменные, это не позволит использовать временные таблицы.
7. Хранимые процедуры могут'т быть вызвана из функции.
8. Функции могут быть вызваны из инструкции Select.
9. Пользовательская функция может использоваться в предложении Join в виде результирующего набора.

Хранимая Процедура

1. Хранимая процедура может или не возвращать значения.
2. Может есть инструкции Select, а также инструкции DML, такие как вставка, обновление, удаление и так далее
3. Он может иметь как входные, так и выходные параметры.
4. Для обработки исключений можно использовать попробовать блоков catch.
5. Можно использовать транзакции в хранимых процедурах.
6. Можно использовать как таблицы переменных, а также временные таблицы в ней.
7. Хранимые процедуры могут вызывать функции.
8. Процедуры могут'т быть вызваны из выбрать/где/имеющие и так на заявления. Выполнение/инструкция exec можно использовать для вызова/выполнение хранимой процедуры.
9. Процедуры могут'т быть использованы в предложении Join

• Функции могут использоваться в инструкции Select, где в качестве процедуры не могут.

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

• Функции не могут возвращать значения типа text, типа ntext, изображения & метки времени, где в качестве процедуры.

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

***Например:-создать таблицу <имятаблицы>(имя, тип varchar(10),зарплата getsal(имя))`

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

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

Функции могут быть...

1. Скаляр:

Создать функция ... возвращает scalar_type как начать ... конец`

2. Мульти-заявление с табличным значением:

Создать функция ... возвращает @таблица р(...) как начать ... конец`

3. Встроенной возвращающей табличное значение:

СОЗДАЕМ ФУНКЦИЮ ... ТАБЛИЦЫ ВОЗВРАЩАЕТ КАК ВЕРНУТЬ ВЫБЕРИТЕ ...

Третий вид (встроенной возвращающей табличное значение) обрабатываются оптимизатором запросов по существу (параметризованных) вид, который означает, что ссылка на функцию из вашего запроса аналогично скопировать-вставить функцию'ы в SQL тела (без копировать-вставить), что приводит к следующие преимущества:

• Планировщик запросов может оптимизировать встроенная функция's в исполнение так же, как и любой другой суб-запроса (например, ликвидировать неиспользуемые столбцы, нажимаем предикатов вниз, выбираете объединения различных стратегий и т. д.).
• Объединение нескольких встроенная функция не'т требует материализации результата от первого до подачи его на следующий.

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

Примечание: похоже, что SQL-сервер 2019 будет представить некоторые формы скалярной функции подстановкой, а также.

В SQL сервере, функции и хранимые процедуры являются двумя разными типами сущностей.

Функции: в базе данных SQL Server функции используются, чтобы выполнить некоторые действия и действия немедленно возвращает результат. Функции бывают двух типов:

1. Системные

2. Пользовательские

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

1. Системные Хранимые Процедуры
2. Пользовательские Процедуры