Funkcja a procedura przechowywana w SQL Server


830

Uczę się funkcji i procedury składowanej od dłuższego czasu, ale nie wiem, dlaczego i kiedy powinienem użyć funkcji lub procedury składowanej. Dla mnie wyglądają tak samo, może dlatego, że jestem trochę początkujący.

Czy ktoś może mi powiedzieć dlaczego?





3
co powiesz na prędkość? który z nich uruchamia to samo zapytanie szybciej?
AmiNadimi

Odpowiedzi:


709

Funkcje są wartościami obliczonymi i nie mogą dokonywać trwałych zmian środowiskowych w SQL Server(tzn. Nie wolno INSERTlub nie UPDATEzezwala się na instrukcje).

Funkcja może być używana w SQLinstrukcjach, jeśli zwraca wartość skalarną, lub może zostać dołączona, jeśli zwraca zestaw wyników.

Warto zwrócić uwagę na komentarze, które podsumowują odpowiedź. Dzięki @Sean K Anderson:

Funkcje są zgodne z definicją informatyki, ponieważ MUSZĄ zwracać wartość i nie mogą zmieniać danych, które otrzymują jako parametry (argumenty). Funkcje nie mogą niczego zmieniać, muszą mieć co najmniej jeden parametr i muszą zwracać wartość. Przechowywane procy nie muszą mieć parametru, mogą zmieniać obiekty bazy danych i nie muszą zwracać wartości.

Jak wywołać SQLfunkcję z procedury składowania i kiedy używamy funkcji zamiast procedury składowanej.

Cześć przyjaciele, dzisiaj omówimy Kiedy używać procedury składowanej i kiedy korzystać z funkcji. W prostym zespole Jeśli chcesz obliczyć niektóre wartości, a to zwróci jedną wartość, więc nie jest to wymagane:

https://programmingtechtutorial.blogspot.com/2020/01/when-use-storeprocedure-and-when-use.html


13
Zasadniczo nie jest dozwolony DML?
David Blaine

173
Funkcje są zgodne z definicją nauki komputerowej, ponieważ MUSZĄ zwrócić wartość i nie mogą zmieniać danych, które otrzymują jako parametry (argumenty). Funkcje nie mogą niczego zmieniać, muszą mieć co najmniej jeden parametr i muszą zwracać wartość. Przechowywane procy nie muszą mieć parametru, mogą zmieniać obiekty bazy danych i nie muszą zwracać wartości.
Sean Anderson

23
W rzeczywistości możesz mieć INSERT, UPDATE i DELETE w funkcji do modyfikowania lokalnych zmiennych tabeli.
Ani

14
@Ani - Możesz utworzyć i zmodyfikować dowolną liczbę zmiennych lokalnych w funkcji, jednak nie możesz modyfikować niczego poza zakresem funkcji.
MyItchyChin

40
@SeanKAnderson funkcja „musi mieć co najmniej jeden parametr” nie jest prawdą.
liang

623

Różnica między SP a 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.    |                                        |
+---------------------------------+----------------------------------------+

21
Funkcje muszą zwracać jedną wartość lub zestaw.
Rafareino

8
To przyszło 3 lata później, ale powinno być na topie, ponieważ jest zarówno czytelne, jak i obszerne.
DanteTheSmith

SP może używać zarówno tabel tymczasowych, jak i zmiennych tabel, podczas gdy UDF może używać tylko zmiennych tabel. Zmienne tabeli z kolei nie mogą używać indeksów. UDF można wywoływać w APLIKACJI KRZYŻOWEJ w przeciwieństwie do SP
Ludovic Aubert,

190

Funkcje i procedury przechowywane służą odrębnym celom. Chociaż nie jest to najlepsza analogia, funkcje można postrzegać dosłownie jak każdą inną funkcję, której można by użyć w dowolnym języku programowania, ale przechowywane procy przypominają raczej poszczególne programy lub skrypt wsadowy.

Funkcje zwykle mają wyjście i opcjonalnie wejścia. Dane wyjściowe można następnie wykorzystać jako dane wejściowe do innej funkcji (wbudowany SQL Server, taki jak DATEDIFF, LEN itp.) Lub jako predykat do zapytania SQL - np . SELECT a, b, dbo.MyFunction(c) FROM tableLub SELECT a, b, c FROM table WHERE a = dbo.MyFunc(c).

Przechowywane procy są używane do łączenia zapytań SQL w transakcji i interfejsu ze światem zewnętrznym. Frameworki takie jak ADO.NET itp. Nie mogą bezpośrednio wywoływać funkcji, ale mogą bezpośrednio wywoływać przechowywany proc.

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

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

Gdzie MyFunction jest zadeklarowane jako:

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

   SELECT localValue 
      FROM dbo.localToNationalMapTable
      WHERE nationalValue = @someValue

   RETURN @retval
END

Tutaj dzieje się tak, że funkcja MyFunction jest wywoływana dla każdego wiersza w tabeli MyTable. Jeśli MyTable ma 1000 wierszy, to kolejne 1000 zapytań ad-hoc względem bazy danych. Podobnie, jeśli funkcja jest wywoływana, gdy jest określona w specyfikacji kolumny, funkcja będzie wywoływana dla każdego wiersza zwróconego przez WYBÓR.

Musisz więc uważnie pisać funkcje. Jeśli wybierzesz opcję WYBIERZ z tabeli w funkcji, musisz zadać sobie pytanie, czy można ją lepiej wykonać za pomocą JOIN w nadrzędnym przechowywanym proc lub jakimś innym konstrukcie SQL (takim jak CASE ... WHEN ... ELSE ... KONIEC).


2
Czy możesz rozwinąć temat „Frameworki takie jak ADO.NET itp. Nie mogą bezpośrednio wywoływać funkcji”? Wykonałem funkcje z dostawcami danych ADO.NET bez problemów.
Ian Kemp

24
Musisz wywołać funkcję za pomocą instrukcji SELECT - funkcji nie można wywołać jako samodzielnego kawałka kodu - należy ją wywołać jako część większej instrukcji SQL, nawet jeśli ta instrukcja SQL jest niczym więcej niż SELECT * from dbo.MyTableValuedFunction(). Z drugiej strony Sprocs można wywoływać bezpośrednio za pomocą ADO.NET, ustawiając SqlCommand.CommandTypena CommandType.StoredProcedure.
Chris J

60

Różnice między procedurami przechowywanymi a funkcjami zdefiniowanymi przez użytkownika:

  • Procedur przechowywanych nie można używać w instrukcjach Select.
  • Procedury składowane obsługują rozpoznawanie nazw odroczonych.
  • Procedury przechowywane są zwykle używane do wykonywania logiki biznesowej.
  • Procedury przechowywane mogą zwrócić dowolny typ danych.
  • Procedury przechowywane mogą przyjmować większą liczbę parametrów wejściowych niż funkcje zdefiniowane przez użytkownika. Procedury składowane mogą mieć do 21 000 parametrów wejściowych.
  • Procedury przechowywane mogą wykonywać Dynamiczny SQL.
  • Procedury składowane obsługują obsługę błędów.
  • Funkcje niedeterministyczne mogą być używane w procedurach przechowywanych.

  • Funkcje zdefiniowane przez użytkownika mogą być używane w instrukcjach Select.
  • Funkcje zdefiniowane przez użytkownika nie obsługują rozpoznawania 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 przechowywane. UDF mogą mieć do 1023 parametrów wejściowych.
  • Tabel tymczasowych nie można używać 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. RAISEERRORLUB @@ERRORnie są dozwolone w UDF.
  • Funkcje niedeterministyczne nie mogą być używane w UDF. Na przykład GETDATE()nie można go używać w UDF.

1
Cytując @curiousBoy poniżej re. inna odpowiedź, która nie została przypisana (autor @Ankit) (<- zobacz, jak to zrobiłem?;)): „Powinieneś był podać źródło. To pochodzi z ( blogs.msdn.microsoft.com/pradeepsvs/2014/10 / 08 /… ). Szanuj pracę wykonaną przez innych! ”
Tom

7
Te blogi zostały napisane od 8 października 2014 r., A ta odpowiedź została napisana od 2 maja 2013 r. @Tom
Kumar Manish

1
@Code Rider: Ach, przepraszam! Nie mogę uwierzyć, że tego nie zauważyłem! Więc blog skopiował cię (lub kogoś, kto to zrobił) bez kredytu?
Tom

GETDATE()może być użyty w funkcji. Punkt zwrotny w przypadku niedeterministycznego nie jest dobry.
PerformanceDBA

56

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


18
istnieją różne typy funkcji zdefiniowanych przez użytkownika. Skalarne zwracają tylko wartości; inne typy zestawów wyników ponownego wyszukiwania.
AK

44
              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. 
                                       |
 * 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.

1
UDF można wywołać w aplikacji KRZYŻOWEJ, w przeciwieństwie do SP
Ludovic Aubert,

24

Podstawowa różnica

Funkcja musi zwracać wartość, ale w procedurze przechowywanej jest opcjonalna (procedura może zwracać zero lub n wartości).

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

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

Funkcje można wywoływać z procedury, podczas gdy procedury nie można wywoływać z funkcji.

Różnica zaliczek

Procedura pozwala na użycie instrukcji SELECT oraz DML (INSERT / UPDATE / DELETE), podczas gdy funkcja pozwala na użycie tylko instrukcji SELECT.

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

Procedur przechowywanych nie można używać w instrukcjach SQL w żadnym miejscu sekcji WHERE / HAVING / SELECT, podczas gdy funkcja może być.

Funkcje zwracające tabele można traktować jako inny zestaw wierszy. Można tego użyć w połączeniach z innymi tabelami.

Funkcja Inline może być traktowana jako widoki, które przyjmują parametry i mogą być używane w połączeniach JOIN 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żywany w funkcji.

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

źródło


25
Powinieneś podać źródło odniesienia. To jest z dotnet-tricks.com/Tutorial/sqlserver/ ... Szanuj pracę, którą wykonują inni!
ciekawyBoy

16
To nie jest powód, aby nie podawać źródła. Możesz wspomnieć na końcu!
ciekawyBoy

2
Re. „Funkcja musi zwracać wartość, ale w procedurze przechowywanej jest opcjonalna…”: Wyjaśnię, że: „Funkcje muszą zwracać jedną i tylko jedną wartość (co musi być wykonane za pomocą Returnssłowa kluczowego i musi być typu skalarnego lub tabelowego) , ale Procedury składowane mogą opcjonalnie zwrócić: a) IntKod wyniku 1 typu za pomocą Returninstrukcji i / lub b) Parametry 1+ (w tym Cursortyp) za pomocą Outputsłowa kluczowego i / lub c) Zestawy rzędu 1+ za pomocą Selectinstrukcji. jest zwracany, można go użyć jako argumentu „execute_statement” instrukcji „Insert Into”.
Tom

20

funkcja zdefiniowana przez użytkownika jest ważnym narzędziem dostępnym dla programisty serwera SQL. Możesz użyć go wbudowanego w takiej instrukcji SQL

SELECT a, lookupValue(b), c FROM customers 

gdzie lookupValuebędzie UDF. Tego rodzaju funkcjonalność nie jest możliwa przy użyciu procedury składowanej. Jednocześnie nie można wykonywać pewnych czynności w UDF. Podstawową rzeczą do zapamiętania jest to, że UDF:

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

procedura składowana może robić te rzeczy.

Dla mnie bezpośrednie użycie UDF jest najważniejszym użyciem UDF.


14

Procedury przechowywane są używane jako skrypty . Wykonują dla ciebie serię poleceń i możesz zaplanować ich uruchomienie w określonych godzinach. Zwykle uruchamia wiele instrukcji DML, takich jak INSERT, UPDATE, DELETE itp., A nawet SELECT.

Funkcje są używane jako metody. Podajesz coś, a zwraca wynik. Powinien być mały i szybki - robi to w locie. Zwykle używany w instrukcji SELECT.


2
To dobre podsumowanie tych dwóch, szybkich i nieprzyzwoitych sposobów myślenia o nich.
Eric Bishard,

2
Rzeczywiście dobre podsumowanie. Inne odpowiedzi koncentrują się na teoretycznej różnicy między tymi dwoma, jednocześnie pozostawiając mnie niepewnym, kiedy użyć której z nich w praktyce.
jf328,

8

Procedura składowana:

  • Jest jak miniaturowy program w SQL Server.
  • Może być tak prosty jak instrukcja select lub tak złożony jak długi skrypt, który dodaje, usuwa, aktualizuje i / lub odczytuje dane z wielu tabel w bazie danych.
  • (Może implementować pętle i kursory, które pozwalają pracować z mniejszymi wynikami lub operacjami na wierszach danych).
  • Powinny być wywoływane przy użyciu EXEClub EXECUTEinstrukcji.
  • Zwraca zmienne tabeli, ale nie możemy użyć OUTparametru.
  • Obsługuje transakcje.

Funkcjonować:

  • Nie można go używać do aktualizowania, usuwania ani dodawania rekordów do bazy danych.
  • Po prostu zwraca pojedynczą wartość lub wartość z tabeli.
  • Można go używać tylko do wybierania rekordów. Można go jednak bardzo łatwo wywołać ze standardowego SQL, na przykład:

    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. Uważaj tylko na użycie JOINklauzul w swoich funkcjach. Jeśli twoja funkcja ma JOINklauzulę i wywołujesz ją z innej instrukcji select, która zwraca wiele wyników, to wywołanie funkcji spowoduje utworzenie JOIN tych tabel razem dla każdej linii zwróconej w zestawie wyników. Więc chociaż mogą być pomocne w uproszczeniu logiki, mogą również stanowić wąskie gardło wydajności, jeśli nie są właściwie używane.

  • Zwraca wartości za pomocą OUTparametru.
  • Nie obsługuje transakcji.

8

Funkcja zdefiniowana przez użytkownika.

  1. Funkcja musi zwrócić wartość.
  2. Pozwoli tylko na instrukcje Select, nie pozwoli nam na korzystanie z instrukcji DML.
  3. Pozwoli tylko parametry wejściowe, nie obsługuje parametrów wyjściowych.
  4. Nie pozwoli nam to na użycie bloków try-catch.
  5. Transakcje nie są dozwolone w ramach funkcji.
  6. Możemy używać tylko zmiennych tabel, nie pozwoli to na używanie tabel tymczasowych.
  7. Procedur przechowywanych nie można wywoływać z funkcji.
  8. Funkcje można wywoływać z instrukcji select.
  9. UDF może być użyty w klauzuli łączenia jako zestaw wyników.

Procedura składowana

  1. Procedura składowana może zwracać lub nie zwracać wartości.
  2. Może mieć wybrane instrukcje, a także instrukcje DML, takie jak wstawianie, aktualizowanie, usuwanie i tak dalej
  3. Może mieć parametry wejściowe i wyjściowe.
  4. Do obsługi wyjątków możemy użyć bloków try catch.
  5. Może korzystać z transakcji w ramach procedur przechowywanych.
  6. Można w nim używać zarówno zmiennych tabeli, jak i tabeli tymczasowej.
  7. Procedury przechowywane mogą wywoływać funkcje.
  8. Procedury nie mogą być wywoływane z instrukcji Select / Where / Pos itd. Instrukcja Execute / Exec może być używana do wywoływania / wykonywania procedury składowanej.
  9. W klauzuli Join nie można stosować procedur

6

Aby zdecydować, kiedy użyć, co mogą pomóc następujące punkty:

  1. Procedury składowane nie mogą zwrócić zmiennej tabeli, w której funkcja może to zrobić.

  2. Możesz użyć procedur składowanych, aby zmienić parametry środowiska serwera, w przypadku których nie możesz używać funkcji.

Twoje zdrowie


6

Funkcje programu SQL Server, takie jak kursory, mają być używane jako ostatnia broń! Mają problemy z wydajnością, dlatego należy w jak największym stopniu unikać korzystania z funkcji wycenianej w tabeli. Mówienie o wydajności mówi o stole z ponad 1 000 000 rekordów hostowanych na serwerze na sprzęcie klasy średniej; w przeciwnym razie nie musisz się martwić spadkiem wydajności spowodowanym przez funkcje.

  1. Nigdy nie używaj funkcji do zwracania zestawu wyników do zewnętrznego kodu (takiego jak ADO.Net)
  2. Używaj kombinacji widoków / przechowywanych procs jak najwięcej. możesz wyzdrowieć z przyszłych problemów z wydajnością, korzystając z sugestii DTA (Doradca dostrajania bazy danych) (czasami indeksowanych widoków i statystyk) - 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


1
Dzięki. Napisałem dziś funkcję do wywołania w zapytaniu w celu wypełnienia wartości dla jednej kolumny. Wykonaj uruchomione przez ponad 3 minuty, zanim go zatrzymałem. Wymyślił JOIN sposób, aby to zrobić. Wykonanie zakończone w 15 sekund. (Zestaw danych miał 3456 wierszy). Duża różnica wydajności.
VISQL,

edycja: Wykonaj kończy od 15 do 50 sekund w zależności od tego, która kolumna I „ORDER BY” (zestaw danych miał 3456 wierszy). Duża różnica wydajności.
VISQL,

Różnica w wydajności może mieć korzenie w różnych typach kolumn, według których porządkujesz wynik. SQL Server działa znacznie lepiej z liczbami niż danymi znakowymi. Możesz użyć DTA dla tego 50-sekundowego zapytania i sprawdzić, czy może on zaproponować jakieś statystyki / sugestie dotyczące indeksów, aby przyspieszyć uruchamianie zapytania.
Achilles,

1
Nie jestem pewien, czy dostarczono wystarczających dowodów na to, że powinna to być ostateczność. Funkcję można traktować jako sparametryzowany widok, który można dalej obsługiwać. Np. Chcesz dołączyć klientów do zamówień, ale tylko dla Michigan. Tworzysz funkcję customerOrders (@StateCode), która połączy wartość klienta tylko z jednym stanem. Następnie mogę dalej operować na tym zestawie jako Select FirstName, LastName, OrderTotal, StoreName From CustomerOrders ('MI') INNER JOIN Stores ON Stores.StoreID = Orders.StoreID WHERE OrderTotal> 100; Byłoby to kłopotliwe z SP, ponieważ musisz tymczasowo skopiować.
MPavlak,

Ile rekordów masz w tej tabeli? Jeśli twój sprzęt odpowiednio sobie z tym radzi, nie musisz się martwić o wybór broni. Łyżka może wykonać zadanie, gdy jest wystarczająco trudno złamać miecz; ta twardość nazywa się SPRZĘT!
Achilles

3

Zacznij od funkcji, które zwracają jedną wartość. Zaletą 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”) Zwraca tabelę, która może być używana jako łączenie.

To sposób organizacji kodu. Wiedząc, kiedy coś można ponownie wykorzystać, a kiedy jest to strata czasu, uzyskuje się jedynie dzięki próbom, błędom i doświadczeniu.

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


3

Oto praktyczny powód, aby preferować funkcje nad procedurami przechowywanymi. 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ć execinstrukcji, aby wstawić wyniki procedury składowanej do tabeli tymczasowej. To jest niechlujne. Jednym z problemów jest to, że insert-execs nie mogą być zagnieżdżone .

Jeśli utkniesz w procedurach przechowywanych wywołujących inne procedury przechowywane, możesz na to natknąć. Jeśli zagnieżdżona procedura przechowywana po prostu zwraca zestaw danych, można go zastąpić funkcją o wartości tabeli i nie będzie już tego błędu.

( jest to kolejny powód, dla którego powinniśmy trzymać logikę biznesową poza bazą danych )


2
  • Funkcja musi zwracać wartość, gdy nie jest to procedura przechowywana.
  • Wybierz instrukcje akceptowane tylko w UDF, podczas gdy instrukcje DML nie są wymagane.
  • Procedura przechowywana akceptuje wszelkie instrukcje, a także instrukcje DML.
  • UDF dopuszcza tylko wejścia, a nie wyjścia.
  • Procedura przechowywana pozwala zarówno na wejścia, jak i wyjścia.
  • Bloków catch nie można używać w UDF, ale można je stosować w procedurze przechowywanej.
  • Niedozwolone są transakcje w funkcjach w UDF, ale w procedurze przechowywanej są dozwolone.
  • W UDF można używać tylko zmiennych tabel, a nie tabel tymczasowych.
  • Procedura składowana pozwala zarówno na zmienne tabel, jak i tabele tymczasowe.
  • UDF nie pozwala na wywoływanie procedur przechowywanych z funkcji, podczas gdy procedury przechowywane umożliwiają wywoływanie funkcji.
  • UDF jest używany w klauzuli Join, podczas gdy procedury składowane nie mogą być używane w klauzuli Join.
  • Procedura przechowywana zawsze pozwala na powrót do zera. Przeciwnie, UDF ma wartości, które muszą wrócić do z góry określonego punktu.

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

  • Procedura składowana przyjmuje parametry wejściowe i wyjściowe, ale Funkcje tylko parametry wejściowe.

  • Funkcje nie mogą zwracać wartości typu tekst, ntext, obraz i znaczniki czasu, o ile procedury mogą.

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

*** Np .: -tworzyć table <tablename>(name varchar(10),salary getsal(name))

W tym przypadku getsal jest funkcją zdefiniowaną przez użytkownika, która zwraca typ wynagrodzenia, gdy tworzona jest tabela, nie jest przydzielane miejsce na typ wynagrodzenia, a funkcja getsal również nie jest wykonywana, ale gdy pobieramy niektóre wartości z tej tabeli, funkcja getsal zostaje wykonana, a return Typ jest zwracany jako zestaw wyników.


1

Zdaję sobie sprawę, że to bardzo stare pytanie, ale nie widzę żadnego ważnego aspektu wspomnianego w żadnej z odpowiedzi: wpisania się w plan zapytań.

Funkcje mogą być ...

  1. Skalarny:

    CREATE FUNCTION ... RETURNS scalar_type AS BEGIN ... END

  2. Tabela z wieloma wyciągami:

    CREATE FUNCTION ... RETURNS @r TABLE(...) AS BEGIN ... END

  3. Inline wyceniany w tabeli:

    CREATE FUNCTION ... RETURNS TABLE AS RETURN SELECT ...

Trzeci rodzaj (wstawiany do tabeli) jest traktowany przez optymalizator zapytań zasadniczo jako widoki (sparametryzowane), co oznacza, że ​​odwoływanie się do funkcji z zapytania jest podobne do wklejania kopii kodu SQL funkcji (bez faktycznego wklejania kopii), prowadząc do następujących korzyści:

  • Planista zapytań może zoptymalizować wykonanie funkcji wbudowanej tak, jak każde inne zapytanie podrzędne (np. Wyeliminować nieużywane kolumny, przesunąć predykaty w dół, wybrać różne strategie JOIN itp.).
  • Łączenie kilku funkcji inline nie wymaga materializacji wyniku z pierwszego przed przekazaniem go do następnego.

Powyższe może prowadzić do potencjalnie znacznych oszczędności wydajności, szczególnie w przypadku łączenia wielu poziomów funkcji.


UWAGA: Wygląda na to, że SQL Server 2019 wprowadzi również pewną formę wbudowania funkcji skalarnej .


-2

W SQL Server funkcje i procedura przechowywana są dwoma różnymi typami jednostek.

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

  1. System zdefiniowany

  2. Zdefiniowane przez użytkownika

Procedury przechowywane: W SQL Server procedury przechowywane są przechowywane na serwerze i mogą być zwracane zero, pojedyncze i wielokrotne wartości. Procedury przechowywane są dwa typy:

  1. Procedury przechowywane w systemie
  2. Procedury zdefiniowane przez użytkownika
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.