Benutzerdefinierte Funktionen

Gilt für:SQL ServerAzure SQL-DatenbankVerwaltete Azure SQL-InstanzSQL-Analyseendpunkt in Microsoft FabricLagerhaus in Microsoft FabricSQL-Datenbank in Microsoft Fabric

Ebenso wie Funktionen in Programmiersprachen sind auch benutzerdefinierte Funktionen von SQL Server Routinen, die Parameter annehmen, eine Aktion ausführen (z. B. eine komplexe Berechnung) und das Ergebnis dieser Aktion als Wert zurückgeben. Der Rückgabewert kann ein einzelner Skalarwert oder ein Resultset sein.

Vorteile benutzerdefinierter Funktionen

Gründe für die Verwendung benutzerdefinierter Funktionen

  • Modulare Programmierung. Sie können die Funktion einmal erstellen, sie dann in der Datenbank speichern und beliebig oft in einem Programm aufrufen. Benutzerdefinierte Funktionen können unabhängig vom Programmquellcode geändert werden.

  • Schnellere Ausführung. Ähnlich wie gespeicherte Prozeduren verringern auch benutzerdefinierte Transact-SQL-Funktionen die Kompilierungskosten von Transact-SQL-Code, da die Pläne zwischengespeichert und für wiederholte Ausführungen erneut verwendet werden. Das bedeutet, dass die benutzerdefinierte Funktion nicht bei jeder Verwendung repariert und neu optimiert werden muss, was zu schnelleren Ausführungszeiten führt.

    Common Language Runtime (CLR)-Funktionen bieten einen erheblichen Leistungsvorteil gegenüber Transact-SQL-Funktionen für Berechnungsaufgaben, Zeichenfolgenmanipulation und Geschäftslogik. Transact-SQL-Funktionen sind besser für datenzugriffsintensive Programmlogik geeignet.

  • Verkürzen des Netzwerkdatenverkehrs. Ein Vorgang, der Daten basierend auf einer komplexen Einschränkung filtert, die nicht als einzelner Skalarausdruck ausgedrückt werden kann, lässt sich als Funktion ausdrücken. Diese Funktion kann anschließend in der WHERE-Klausel aufgerufen werden, um die Anzahl der an den Client gesendeten Zeilen zu verringern.

Wichtig

Benutzerdefinierte Transact-SQL-Funktionen in Abfragen können nur für einen einzelnen Thread (serieller Ausführungsplan) ausgeführt werden. Daher verhindert die Verwendung benutzerdefinierter Funktionen eine parallele Abfrageverarbeitung. Weitere Informationen zur parallelen Abfrageverarbeitung finden Sie im Handbuch zur Architektur der Abfrageverarbeitung.

Funktionstypen

Dieser Abschnitt beschreibt die Unterschiede zwischen skalaren Funktionen, tabellenwertigen Funktionen und Systemfunktionen.

Skalarfunktionen

Benutzerdefinierte Skalarfunktionen geben einen einzelnen Datenwert des definierten Datentyps in einer RETURNS-Klausel zurück. Bei einer Inlineskalarfunktion ist der zurückgegebene Skalarwert das Ergebnis einer einzelnen Anweisung. Bei einer aus mehreren Anweisungen bestehenden Skalarfunktion kann der Funktionstext eine Reihe von Transact-SQL-Anweisungen enthalten, die den einzelnen Wert zurückgeben. Der Rückgabetyp kann ein beliebiger Datentypen mit Ausnahme von text, ntext, image, cursorund timestampsein. Beispiele finden Sie unter Erstellen von benutzerdefinierten Funktionen (Datenbank-Engine).

Tabellenwertfunktionen

Benutzerdefinierte Tabellenwertfunktionen geben einen table-Datentyp zurück. Bei einer inline-tabellenwertigen Funktion gibt es keinen Body der Funktion; die Tabelle ist das Ergebnis-Set einer einzelnen SELECT-Anweisung. Beispiele finden Sie unter Erstellen von benutzerdefinierten Funktionen (Datenbank-Engine).

Systemfunktionen

SQL Server stellt viele Systemfunktionen bereit, mit denen Sie eine Vielzahl von Vorgängen ausführen können. Diese können nicht geändert werden. Weitere Informationen finden Sie unter Was sind die SQL-Datenbankfunktionen?, Systemfunktionen nach Kategorie für Transact-SQL und Systemansichten zur dynamischen Verwaltung.

Richtlinien

Transact-SQL-Fehler, die dazu führen, dass eine Anweisung abgebrochen und mit der nächsten Anweisung fortgefahren wird (z. B. Trigger oder gespeicherte Prozeduren), werden innerhalb einer Funktion unterschiedlich behandelt. In Funktionen bewirken solche Fehler, dass die Ausführung der Funktion beendet wird. Dies hat wiederum zur Folge, dass die Anweisung abgebrochen wird, die die Funktion aufgerufen hat.

Die Anweisungen in einem BEGIN...END-Block dürfen keine Nebeneffekte haben. Nebeneffekte von Funktionen sind dauerhafte Änderungen am Status einer Ressource, deren Gültigkeitsbereich außerhalb der Funktion liegt, wie z. B. Änderungen an einer Datenbanktabelle. Die einzigen Änderungen, die Anweisungen in der Funktion vornehmen können, sind Änderungen an funktionslokalen Objekten, wie z. B. lokalen Cursoren oder Variablen. Änderungen an Datenbanktabellen, Vorgänge auf Cursorn, die nicht lokal für die Funktion sind, z. B. senden von E-Mails, Versuch einer Katalogänderung und Generieren eines Resultsets, das an den Benutzer zurückgegeben wird, sind Beispiele für Aktionen, die nicht in einer Funktion ausgeführt werden können.

Wenn eine CREATE FUNCTION-Anweisung zu Nebeneffekten bei Ressourcen führt, die beim Ausgeben der CREATE FUNCTION-Anweisung nicht vorhanden sind, führt SQL Server die Anweisung aus. SQL Server führt die Funktion jedoch nicht aus, wenn sie aufgerufen wird.

Wie oft eine in einer Abfrage angegebene Funktion ausgeführt wird, kann bei den vom Abfrageoptimierer erstellten Ausführungsplänen variieren. Ein Beispiel hierfür ist eine Funktion, die von einer Unterabfrage in einer WHERE-Klausel aufgerufen wird. Wie oft die Unterabfrage und deren Funktion ausgeführt wird, kann bei den verschiedenen Zugriffsmethoden variieren, die der Abfrageoptimierer auswählt.

Deterministische Funktionen müssen schemagebunden sein. Verwenden Sie die SCHEMABINDING-Klausel beim Erstellen einer deterministischen Funktion.

Weitere Informationen und Leistungsüberlegungen zu benutzerdefinierten Funktionen finden Sie unter Erstellen von benutzerdefinierten Funktionen (Database Engine).

Gültige Anweisungen in einer Funktion

Die folgenden Anweisungstypen sind in einer Funktion zulässig:

  • DECLARE-Anweisungen zum Definieren von lokalen Datenvariablen und lokalen Cursorn für die Funktion.

  • Zuweisungen von Werten zu lokalen Objekten für die Funktion, wie z.B. das Zuweisen von Werten zu lokalen Skalar- und Tabellenwerten mithilfe von SET.

  • Cursorvorgänge, die auf lokale Cursor verweisen, die in der Funktion deklariert, geöffnet, geschlossen und deren Zuordnungen aufgehoben werden. FETCH-Anweisungen, die Daten an den Client zurückgeben, sind nicht zulässig. Nur FETCHAnweisungen, die lokalen Variablen unter Verwendung der INTOKlausel Werte zuweisen, sind möglich.

  • Anweisungen zur Ablaufsteuerung mit Ausnahme von TRY...CATCH-Anweisungen.

  • SELECT-Anweisungen, die Auswahllisten mit Ausdrücken enthalten, in denen Werte Variablen zugewiesen werden, die in der Funktion lokal gelten.

  • UPDATE-, INSERT- und DELETE-Anweisungen, die lokale Tabellenvariablen der Funktion ändern.

  • EXECUTE-Anweisungen, die eine erweiterte gespeicherte Prozedur aufrufen.

Integrierte Systemfunktionen

Die folgenden nicht deterministischen integrierten Funktionen können in benutzerdefinierten Transact-SQL-Funktionen verwendet werden.

  • CURRENT_TIMESTAMP
  • GET_TRANSMISSION_STATUS
  • GETDATE
  • GETUTCDATE
  • @@CONNECTIONS
  • @@CPU_BUSY
  • @@DBTS
  • @@IDLE
  • @@IO_BUSY
  • @@MAX_CONNECTIONS
  • @@PACK_RECEIVED
  • @@PACK_SENT
  • @@PACKET_ERRORS
  • @@TIMETICKS
  • @@TOTAL_ERRORS
  • @@TOTAL_READ
  • @@TOTAL_WRITE

Die folgenden nicht deterministischen integrierten Funktionen können nicht in einer Transact-SQL benutzerdefinierten Funktion (UDF) verwendet werden.

  • NEWID
  • NEWSEQUENTIALID
  • RAND
  • TEXTPTR

Wenn Sie auf eine dieser Funktionen in einer UDF verweisen, wird die folgende Fehlermeldung angezeigt:

Msg 443, Level 16, State 1
Invalid use of a side-effecting operator <operator> within a function.

Eine Liste der integrierten deterministischen und nicht-deterministischen Systemfunktionen finden Sie unter Deterministische und nicht-deterministische Funktionen.

Schemagebundene Funktionen

CREATE FUNCTION unterstützt eine SCHEMABINDING-Klausel, die die Funktion an das Schema von Objekten bindet, auf die verwiesen wird, wie z.B. Tabellen, Sichten und andere benutzerdefinierte Funktionen. Der Versuch, ein Objekt zu ändern oder zu löschen, auf das von einer schemagebundenen Funktion verwiesen wird, erzeugt einen Fehler.

Die folgenden Bedingungen müssen erfüllt sein, um SCHEMABINDING in CREATE FUNCTION angeben zu können:

  • Alle Sichten und benutzerdefinierten Funktionen, auf die die Funktion verweist, müssen schemagebunden sein.

  • Alle Objekte, auf die die Funktion verweist, müssen sich in derselben Datenbank wie die Funktion befinden. Auf die Objekte muss mit ein- oder zweiteiligen Namen verwiesen werden.

  • Sie benötigen die REFERENCES-Berechtigung für alle Objekte (Tabellen, Sichten und benutzerdefinierte Funktion), auf die in der Funktion verwiesen wird.

Mit ALTER FUNCTION können Sie die Schemabindung entfernen. Die ALTER FUNCTION-Anweisung sollte die Funktion neu definieren, ohne WITH SCHEMABINDING anzugeben.

Angeben von Parametern

Eine benutzerdefinierte Funktion verwendet null oder mehr Eingabeparameter und gibt einen Skalarwert oder eine Tabelle zurück. Eine Funktion kann maximal 1024 Eingabeparameter haben. Wenn ein Parameter der Funktion einen Standardwert hat, muss beim Aufruf der Funktion das Schlüsselwort DEFAULT angegeben werden, um den Standardwert zu erhalten. In diesem Punkt gibt es einen Unterschied zu den Parametern einer benutzerdefinierten gespeicherten Prozedur. Fehlt im Aufruf einer benutzerdefinierten gespeicherten Prozedur ein Parameter, der einen Standardwert hat, wird automatisch dieser Standardwert verwendet. Benutzerdefinierte Funktionen unterstützen keine Ausgabeparameter.

Zugehöriger Inhalt

  • Last updated on