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

Определяемая пользователем Функция.

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

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

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

Соединения

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

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

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

Ура

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

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

SELECT a, lookupValue(b), c FROM customers 

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

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

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

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

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

Функции обычно имеют выход и, возможно, входы. Выходные данные затем можно использовать в качестве входных данных для другой функции (встроенный SQL Server, такой как DATEDIFF, LEN и т. Д.) Или в качестве предиката для SQL-запроса, например SELECT a, b, dbo.MyFunction ( c) FROM table или SELECT a, b, c FROM table WHERE a = dbo.MyFunc (c) .

Сохраненные процедуры используются для связывания запросов SQL вместе в транзакции и интерфейса с внешним миром. Фреймворки, такие как ADO.NET и т. Д., Не могут вызывать функцию напрямую, но они могут вызывать хранимую процедуру напрямую.

Однако у функций есть скрытая опасность: они могут использоваться неправильно и вызывать довольно неприятные проблемы с производительностью: рассмотрите этот запрос:

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 специальных запросов к базе данных. Точно так же, если функция вызывается, когда она указана в спецификации столбца, то функция будет вызываться для каждой строки, возвращаемой SELECT.

Так что вам нужно быть осторожным при написании функций. Если вы выполняете SELECT из таблицы в функции, вам нужно спросить себя, можно ли лучше выполнить это с помощью JOIN в родительской хранимой процедуре или какой-либо другой конструкции SQL (например, CASE ... WHEN ... ELSE ... КОНЕЦ).

Однако у функций есть скрытая опасность: ими можно злоупотреблять и вызывать довольно неприятные проблемы с производительностью: рассмотрите этот запрос:

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 специальных запросов к базе данных. Точно так же, если функция вызывается, когда она указана в спецификации столбца, то функция будет вызываться для каждой строки, возвращаемой SELECT.

Так что вам нужно быть осторожным при написании функций. Если вы выполняете SELECT из таблицы в функции, вам нужно спросить себя, можно ли лучше выполнить это с помощью JOIN в родительской хранимой процедуре или какой-либо другой конструкции SQL (например, CASE ... WHEN ... ELSE ... КОНЕЦ).

Однако у функций есть скрытая опасность: они могут использоваться неправильно и вызывать довольно неприятные проблемы с производительностью: рассмотрите этот запрос:

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 специальных запросов к базе данных. Точно так же, если функция вызывается, когда она указана в спецификации столбца, тогда функция будет вызываться для каждой строки, возвращаемой SELECT.

Так что вам нужно быть осторожным при написании функций. Если вы выполняете SELECT из таблицы в функции, вам нужно спросить себя, можно ли лучше выполнить это с помощью JOIN в родительской хранимой процедуре или какой-либо другой конструкции SQL (например, CASE ... WHEN ... ELSE ... КОНЕЦ).

рассмотрим этот запрос:

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 специальных запросов к базе данных. Точно так же, если функция вызывается, когда она указана в спецификации столбца, то функция будет вызываться для каждой строки, возвращаемой SELECT.

Так что вам нужно быть осторожным при написании функций. Если вы выполняете SELECT из таблицы в функции, вам нужно спросить себя, можно ли лучше выполнить это с помощью JOIN в родительской хранимой процедуре или какой-либо другой конструкции SQL (например, CASE ... WHEN ... ELSE ... КОНЕЦ).

рассмотрим этот запрос:

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 специальных запросов к базе данных. Точно так же, если функция вызывается, когда она указана в спецификации столбца, тогда функция будет вызываться для каждой строки, возвращаемой SELECT.

Так что вам нужно быть осторожным при написании функций. Если вы выполняете SELECT из таблицы в функции, вам нужно спросить себя, можно ли лучше выполнить это с помощью JOIN в родительской хранимой процедуре или какой-либо другой конструкции SQL (например, CASE ... WHEN ... ELSE ... КОНЕЦ).

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

Так что вам нужно быть осторожным при написании функций. Если вы выполняете SELECT из таблицы в функции, вам нужно спросить себя, можно ли лучше выполнить это с помощью JOIN в родительской хранимой процедуре или какой-либо другой конструкции SQL (например, CASE ... WHEN ... ELSE ... КОНЕЦ).

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

Так что вам нужно быть осторожным при написании функций. Если вы выполняете SELECT из таблицы в функции, вам нужно спросить себя, можно ли лучше выполнить это с помощью JOIN в родительской хранимой процедуре или какой-либо другой конструкции SQL (например, CASE ... WHEN ... ELSE ... КОНЕЦ).

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

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

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