Funkcja a procedura składowana w SQL Server

Różnica między SP i UDF jest wymieniona poniżej:

+---------------------------------+----------------------------------------+
| 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.    |                                        |
+---------------------------------+----------------------------------------+

Funkcje i procedury przechowywane służą odrębnym celom. Chociaż nie jest to najlepsza analogia, funkcje mogą być postrzegane dosłownie jako każda inna funkcja, której można użyć w dowolnym języku programowania, ale przechowywane proc są bardziej jak pojedyncze programy lub skrypt wsadowy.

Funkcje zwykle mają wyjście i opcjonalnie wejścia. Wyjście może być następnie użyte jako wejście do innej funkcji (wbudowanego serwera SQL, takiego jak DATEDIFF, len itp.) lub jako predykat do zapytania SQL - np. SELECT a, b, dbo.MyFunction(c) FROM table lub SELECT a, b, c FROM table WHERE a = dbo.MyFunc(c).

Przechowywane proc są używane do łączenia zapytań SQL razem w transakcji i interfejsu ze światem zewnętrznym. Ramy takie jak ADO.NET, itd. nie mogą bezpośrednio wywołać funkcji, ale mogą bezpośrednio wywołać zapisany proces.

Funkcje mają jednak ukryte niebezpieczeństwo: mogą być nadużywane i powodować dość nieprzyjemne problemy z wydajnością: rozważ to zapytanie:

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

Gdzie Mojafunkcja jest zadeklarowana jako:

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

   SELECT localValue 
      FROM dbo.localToNationalMapTable
      WHERE nationalValue = @someValue

   RETURN @retval
END

Dzieje się tak, że funkcja Mojafunkcja nazywa się dla każdego wiersza w tabeli MyTable. Jeśli MyTable ma 1000 wierszy, to jest to kolejne 1000 zapytań ad-hoc do bazy danych. Podobnie, jeśli funkcja jest wywoływana, gdy jest określona w specyfikacji kolumny, to funkcja będzie wywoływana dla każdego wiersza zwróconego przez SELECT.

Więc musisz być ostrożny pisząc funkcje. Jeśli wybierzesz z tabeli w funkcji, musisz zadać sobie pytanie, czy można ją lepiej wykonać za pomocą JOIN w przechowywanym procku nadrzędnym lub innej konstrukcji SQL (np. CASE ... Kiedy ... ELSE ... END).

Napisz zdefiniowaną przez użytkownika funkcję, gdy chcesz obliczyć i zwrócić wartość do użycia w innych instrukcjach SQL; napisz procedurę składowaną, gdy chcesz zamiast tego, aby zgrupować prawdopodobnie złożony zestaw instrukcji SQL. W końcu są to dwa całkiem różne przypadki użycia!

Różnice między procedurami składowanymi a funkcjami zdefiniowanymi przez użytkownika:

• procedury składowane nie mogą być używane w poleceniach Select.
• procedury składowane obsługują odroczone rozwiązywanie nazw.
• procedury składowane są zwykle używane do wykonywania logiki biznesowej.
• procedury przechowywane mogą zwracać dowolny typ danych.
• procedury składowane mogą przyjmować większą liczbę parametrów wejściowych niż funkcje zdefiniowane przez użytkownika. Procedury składowane mogą mieć do 21 000 wejść parametry.
• procedury przechowywane mogą wykonywać dynamiczny SQL.
• procedury składowane obsługują obsługę błędów.
• Funkcje niedeterministyczne mogą być używane w procedurach składowanych.

• funkcje zdefiniowane przez Użytkownika mogą być używane w poleceniach Select.
• funkcje zdefiniowane przez użytkownika nie obsługują rozwiązywania nazw odroczonych.
• funkcje zdefiniowane przez Użytkownika są zwykle używane do obliczeń.
• funkcje zdefiniowane przez użytkownika powinny zwracać wartość.
• funkcje zdefiniowane przez użytkownika nie mogą zwracać obrazów.
• funkcje zdefiniowane przez użytkownika akceptują mniejszą liczbę parametrów wejściowych niż procedury składowane. UDFs może mieć do 1023 parametrów wejściowych.
• tabele tymczasowe nie mogą być używane w funkcjach zdefiniowanych przez użytkownika.
• funkcje zdefiniowane przez użytkownika nie mogą wykonywać dynamicznego SQL.
• funkcje zdefiniowane przez użytkownika nie obsługują obsługi błędów. RAISEERROR LUB {[1] } nie są dozwolone w UDFs.
• funkcji niedeterministycznych nie można używać w UDFs. Na przykład, GETDATE() nie może być użyty w UDFs.

Różnica Podstawowa

Funkcja musi zwracać wartość, ale w procedurze składowanej jest to opcjonalne(procedura może zwracać zero lub n wartości).

Funkcje mogą mieć dla niego tylko parametry wejściowe, podczas gdy procedury mogą mieć parametry wejściowe/wyjściowe .

Funkcja pobiera jeden parametr wejściowy jest to obowiązkowe, ale procedura składowana może przyjmować od o do n parametrów wejściowych..

Funkcje mogą być wywoływane z procedury, podczas gdy procedury nie mogą być wywoływane z Funkcja.

Różnica Zaliczek

Procedura pozwala na SELECT oraz polecenie DML (INSERT/UPDATE/DELETE), podczas gdy funkcja zezwala tylko na select.

Procedury nie mogą być użyte w instrukcji SELECT, podczas gdy funkcja może być osadzona w instrukcji SELECT.

Procedury składowane nie mogą być używane w poleceniach SQL w dowolnym miejscu w sekcji WHERE/HAVING / SELECT, podczas gdy funkcja może być.

Funkcje zwracające tabele mogą być traktowany jako inny zestaw wierszy. Można to wykorzystać w połączeniach z innymi tabelami.

Funkcja Inline może być traktowana jako widok, który przyjmuje parametry i może być używana w połączeniach i innych operacjach zestawu wierszy.

Wyjątek może być obsługiwany przez blok try-catch w procedurze, podczas gdy blok try-catch nie może być użyty w funkcji.

Możemy przejść do zarządzania transakcjami w procedurze, podczas gdy nie możemy przejść w funkcji.

Źródło

Funkcja zdefiniowana przez użytkownika jest ważnym narzędziem dostępnym dla programisty sql server. Możesz użyć go w wierszu w instrukcji SQL jak so

SELECT a, lookupValue(b), c FROM customers 

Gdzie {[1] } będzie UDF. Tego rodzaju funkcjonalność nie jest możliwa przy użyciu procedury składowanej. Jednocześnie nie można robić pewnych rzeczy wewnątrz UDF. Podstawową rzeczą do zapamiętania jest to, że UDF ' s:

• nie można tworzyć trwałych zmian
• nie można zmienić danych

Procedura składowana może rzeczy.

Dla mnie inline użycie UDF jest najważniejszym użyciem UDF.

Procedury składowane są używane jako skrypty . Uruchamiają serię poleceń dla Ciebie i możesz zaplanować ich uruchomienie w określonych godzinach.

Funkcje są stosowane jako metody. przekazujesz to coś i zwraca wynik. Powinien być mały i szybki-robi to w locie.

Procedura składowana:

• jest jak miniaturowy program w SQL Server.
• może być tak proste jak polecenie select lub tak złożone jak długie skrypt, który dodaje, usuwa, aktualizuje i/lub odczytuje dane z wielu tabele w bazie danych.
• (potrafi zaimplementować pętle i kursory, które pozwalają na pracę z mniejsze wyniki lub operacje wiersz po wierszu na danych.)
• należy wywołać za pomocą EXEC lub EXECUTE.
• zwraca zmienne tabeli, ale nie możemy użyj parametru OUT.
• obsługuje transakcje.

Funkcja:

• nie może być używany do aktualizacji, usuwania lub dodawania rekordów do bazy danych.
• po prostu zwraca pojedynczą wartość lub wartość tabeli.

• Może być używany tylko do wybierania rekordów. Można go jednak nazwać bardzo łatwo z poziomu standardowego SQL, takiego jak:

  SELECT dbo.functionname('Parameter1')
  

  Lub

  SELECT Name, dbo.Functionname('Parameter1') FROM sysObjects
  

• W przypadku prostych operacji wyboru wielokrotnego użytku funkcje mogą uprościć kod. Tylko uważaj na używanie JOIN klauzul w swoich funkcjach. Jeśli funkcja ma klauzulę JOIN i wywołujesz ją z innego select instrukcja, która zwraca wiele wyników, wywołanie funkcji będzie JOIN te tabele razem dla każdej linii zwróconej w zestawie wyników. Więc chociaż mogą być pomocne w uproszczeniu jakiejś logiki, mogą być również wąskie gardło wydajności, jeśli nie są prawidłowo używane.

• Zwraca wartości używając parametru OUT.
• nie obsługuje transakcje.

Aby zdecydować, kiedy użyć co poniższe punkty mogą pomóc -

1. Procedury składowane nie mogą zwracać zmiennej tabeli, gdzie funkcja as może to zrobić.

2. Możesz użyć procedur przechowywanych, aby zmienić parametry środowiska serwera, gdzie nie możesz używać funkcji.

Cheers

Funkcje SQL Server, takie jak Kursory, mają być używane jako ostatnia Broń! Mają problemy z wydajnością i dlatego należy unikać korzystania z funkcji o wartości tabelarycznej w jak największym stopniu. Mówienie o wydajności to mówienie o tabeli z ponad 1,000,000 rekordów hostowanych na serwerze na sprzęcie Klasy średniej; w przeciwnym razie nie musisz się martwić o hit wydajności spowodowany przez funkcje.

1. nigdy nie używaj funkcji do zwracania wyniku-ustawionego na Zewnętrzny kod (jak ADO.Net)
2. używaj kombinacji views/stored procs w jak największym stopniu. możesz odzyskać z przyszłych problemów z wydajnością, korzystając z sugestii DTA (Database Tuning Advisor), które dadzą Ci (jak indeksowane widoki i statystyki) - czasami!

W celu uzyskania dalszych informacji patrz: http://databases.aspfaq.com/database/should-i-use-a-view-a-stored-procedure-or-a-user-defined-function.html

              STORE PROCEDURE                 FUNCTION (USER DEFINED FUNCTION)    
 * Procedure can return 0, single or   | * Function can return only single value   
   multiple values.                    |
                                       |
 * Procedure can have input, output    | * Function  can have only input 
   parameters.                         |   parameters.         
                                       |
 * Procedure cannot be called from     | * Functions can be called from 
   function.                           |   procedure.
                                       |
 * Procedure allows select as well as  | * Function allows only select statement 
   DML statement in it.                |   in it.
                                       |
 * Exception can be handled by         | * Try-catch block cannot be used in a 
   try-catch block in a procedure.     |   function.
                                       |
 * We can go for transaction management| * We can't go for transaction 
   in procedure.                       |   management in function.
                                       |
 * Procedure cannot be utilized in a   | * Function can be embedded in a select 
   select statement                    |   statement.
                                       |
 * Procedure can affect the state      | * Function can not affect the state 
   of database means it can perform    |   of database means it can not    
   CRUD operation on database.         |   perform CRUD operation on 
                                       |   database. 
                                       |
 * Procedure can use temporary tables. | * Function can not use 
   temporary tables                    |   temporary tables. 
                                       |
 * Procedure can alter the server      | * Function can not alter the  
   environment parameters.             |   environment parameters.
                                       |   
 * Procedure can use when we want      | * Function can use when we want
   instead is to group a possibly-     |   to compute and return a value
   complex set of SQL statements.      |   for use in other SQL 
                                           statements.

Zacznij od funkcji zwracających pojedynczą wartość. Fajną rzeczą jest to, że możesz umieścić często używany kod w funkcji i zwrócić je jako kolumnę w zestawie wyników.

Następnie możesz użyć funkcji do sparametryzowanej listy miast. dbo.GetCitiesIn("NY"), która zwraca tabelę, która może być używana jako join.

To sposób organizacji kodu. Wiedząc, kiedy coś jest wielokrotnego użytku, a kiedy jest to strata czasu, jest czymś zyskanym tylko metodą prób i błędów i doświadczenie.

Również funkcje są dobrym pomysłem w SQL Server. Są szybsze i mogą być dość potężne. Selekcje Inline i direct. Uważaj, aby nie nadużywać.

• jest obowiązkowe, aby funkcja zwracała wartość, podczas gdy nie jest ona dla procedury składowanej.
• instrukcje Select akceptowane tylko w UDF, podczas gdy instrukcje DML nie są wymagane.
• procedura składowana akceptuje wszelkie deklaracje, jak również deklaracje DML.
• UDF zezwala tylko na wejścia, a nie na wyjścia.
• procedura składowana umożliwia zarówno wejście, jak i wyjście.
• bloki Catch nie mogą być używane w UDF, ale mogą być używane w procedurach składowanych.
• transakcje niedozwolone w funkcje w UDF, ale w procedurze składowanej są dozwolone.
• w UDF można używać tylko zmiennych tabelkowych, a nie tabel tymczasowych.
• procedura składowana pozwala na stosowanie zarówno zmiennych tabel, jak i tabel tymczasowych.
• UDF nie zezwala na wywoływanie procedur składowanych z funkcji, podczas gdy procedury składowane pozwalają na wywoływanie funkcji.
• UDF jest używany w klauzuli join, podczas gdy procedury składowane nie mogą być używane w klauzuli join.
• procedura składowana zawsze pozwoli na powrót do zero. UDF, przeciwnie, ma wartości, które muszą wrócić do z góry określonego punktu.

Oto praktyczny powód, aby preferować funkcje nad procedurami składowanymi. Jeśli masz procedurę składowaną, która wymaga wyników innej procedury składowanej, musisz użyć instrukcji insert-exec. Oznacza to, że musisz utworzyć tabelę tymczasową i użyć instrukcji exec, aby wstawić wyniki procedury składowanej do tabeli tymczasowej. Jest bałagan. Problem polega na tym, że insert-execs nie może być zagnieżdżony .

Jeśli utkniesz z procedurami składowanymi, które wywołują inne procedury przechowywane, możesz na to wpaść. Jeśli zagnieżdżona procedura składowana po prostu zwróci zbiór danych, może zostać zastąpiona funkcją o wartości tabelarycznej i nie będzie już tego błędu.

(jest to kolejny powód, dla którego powinniśmy trzymać logikę biznesową z dala od bazy danych )

• Funkcje mogą być używane w instrukcji select, gdzie procedury as nie mogą.

• Procedura składowana pobiera zarówno parametry wejściowe, jak i wyjściowe, Ale funkcje pobierają tylko parametry wejściowe.

• Funkcje nie mogą zwracać wartości typu text, ntext, image & timestamps, gdzie jako procedury mogą.

• Funkcje mogą być używane jako typy danych zdefiniowane przez użytkownika w tabeli create, ale procedury nie mogą.

* * * np: - create table <tablename>(name varchar(10),salary getsal(name))

Tutaj getsal jest funkcją zdefiniowaną przez użytkownika, która zwraca typ wynagrodzenia, podczas tworzenia tabeli nie jest przydzielane miejsce na typ wynagrodzenia, a funkcja getsal również nie jest wykonywana, ale gdy pobieramy pewne wartości z tej tabeli, funkcja getsal get jest wykonywana i zwraca wartość get. Typ jest zwracany jako zestaw wyników.

W SQL serverze funkcje i procedura składowana to dwa różne typy encji.

Funkcja: w bazie danych SQL Server funkcje są używane do wykonywania niektórych akcji i akcja natychmiast zwraca wynik. Funkcje są dwa typy:

1. Zdefiniowany System

2. Zdefiniowany przez Użytkownika

Procedury przechowywane: w SQL Server procedury przechowywane są na serwerze i mogą być zwracane zero, single i wiele wartości. Procedury przechowywane są dwa typy:

1. System Stored Procedures
2. Procedury Zdefiniowane Przez Użytkownika