Funktion vs. Gespeicherte Prozedur in SQL Server

Question

Ich lerne schon seit einiger Zeit Funktionen und Stored Procedure, aber ich weiß nicht, warum und wann ich eine Funktion oder eine Stored Procedure verwenden sollte. Für mich sehen sie gleich aus, vielleicht weil ich in diesem Bereich ein Neuling bin.

Kann mir jemand sagen, warum?

Answer 1

Mssql gespeicherte Prozedur vs Funktion:

Answer 2

Mir ist klar, dass dies eine sehr alte Frage ist, aber ich sehe einen entscheidenden Aspekt in keiner der Antworten erwähnt: Inlining in den Abfrageplan.

Funktionen können sein...

1. Skalar:

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

2. Mehrsätzige Tabelle-bewertet:

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

3. Inline-Tabelle-bewertet:

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

Die dritte Art (inline table-valued) wird vom Abfrageoptimierer im Wesentlichen wie (parametrisierte) Views behandelt, was bedeutet, dass der Verweis auf die Funktion in Ihrer Abfrage dem Kopieren des SQL-Körpers der Funktion (ohne tatsächliches Kopieren) ähnelt, was zu den folgenden Vorteilen führt:

• Der Abfrageplaner kann die Ausführung der Inline-Funktion genauso optimieren wie jede andere Unterabfrage (z. B. unbenutzte Spalten eliminieren, Prädikate nach unten schieben, andere JOIN-Strategien wählen usw.).
• Bei der Kombination mehrerer Inline-Funktionen ist es nicht erforderlich, das Ergebnis der ersten Funktion zu materialisieren, bevor es an die nächste weitergegeben wird.

Dies kann zu erheblichen Leistungseinsparungen führen, insbesondere bei der Kombination mehrerer Funktionsebenen.

---

HINWEIS: Es sieht so aus, als würde SQL Server 2019 eine Form von Inlining skalarer Funktionen auch.

Answer 3

• Für Function ist es obligatorisch, einen Wert zurückzugeben, während dies für Stored Procedure nicht der Fall ist.
• Select-Anweisungen werden nur in UDF akzeptiert, DML-Anweisungen sind nicht erforderlich.
• Die gespeicherte Prozedur akzeptiert sowohl beliebige Anweisungen als auch DML-Anweisungen.
• UDF erlaubt nur Eingaben und keine Ausgaben.
• Gespeicherte Prozeduren erlauben sowohl Eingaben als auch Ausgaben.
• Catch-Blöcke können nicht in UDF verwendet werden, wohl aber in gespeicherten Prozeduren.
• In UDF-Funktionen sind keine Transaktionen erlaubt, aber in gespeicherten Prozeduren sind sie erlaubt.
• In UDF können nur Tabellenvariablen und keine temporären Tabellen verwendet werden.
• Stored Procedure erlaubt sowohl Tabellenvariablen als auch temporäre Tabellen.
• UDF erlaubt nicht, dass gespeicherte Prozeduren von Funktionen aus aufgerufen werden, während gespeicherte Prozeduren den Aufruf von Funktionen erlauben.
• UDF wird in der Join-Klausel verwendet, während Stored Procedures nicht in der Join-Klausel verwendet werden können.
• Die gespeicherte Prozedur ermöglicht immer eine Rückkehr zu Null. UDF, im Gegenteil, hat Werte, die kommen müssen - zurück zu einem vorbestimmten Punkt.

Answer 4

• Funktionen können in einer Select-Anweisung verwendet werden, Prozeduren hingegen nicht.

• Eine gespeicherte Prozedur nimmt sowohl Eingabe- als auch Ausgabeparameter an, aber Funktionen nehmen nur Eingabeparameter an.

• Funktionen können keine Werte vom Typ text, ntext, image & timestamps zurückgeben, während Prozeduren dies können.

• Funktionen können als benutzerdefinierte Datentypen in create table verwendet werden, Prozeduren jedoch nicht.

***Eg:-erstellen table <tablename>(name varchar(10),salary getsal(name))

Hier ist getsal eine benutzerdefinierte Funktion, die eine Gehaltsart zurückgibt. Wenn die Tabelle erstellt wird, wird kein Speicherplatz für die Gehaltsart zugewiesen, und die getsal-Funktion wird auch nicht ausgeführt. Typ wird als Ergebnismenge zurückgegeben.

Answer 5

Im Allgemeinen ist die Verwendung von gespeicherten Prozeduren besser für die Leistung. Wenn Sie zum Beispiel in früheren Versionen von SQL Server die Funktion in eine JOIN-Bedingung setzen, ist die Kardinalitätsschätzung 1 (vor SQL 2012) und 100 (nach SQL 2012 und vor SQL 2017) und die Engine kann einen schlechten Ausführungsplan generieren.

Auch wenn Sie es in die WHERE-Klausel setzen, kann die SQL-Engine einen schlechten Ausführungsplan erstellen.

Mit SQL 2017 hat Microsoft die Funktion "Interleaved Execution" eingeführt, um eine genauere Schätzung zu erhalten, aber die gespeicherte Prozedur bleibt die beste Lösung.

Weitere Einzelheiten finden Sie in folgendem Artikel von Joe Sack https://techcommunity.microsoft.com/t5/sql-server/introducing-interleaved-execution-for-multi-statement-table/ba-p/385417