Benutzerdefinierte Funktionen

Artikel
05/23/2023

Gilt für:SQL Server Azure SQL-Datenbank Azure SQL Managed Instance SQL Analytics-Endpunkt in Microsoft Fabric Warehouse 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. Dies bedeutet, dass die benutzerdefinierte Funktion nicht für jede Verwendung neu geparst und optimiert werden muss; aus diesem Umstand ergeben sich wesentlich schnellere Ausführungszeiten.

CLR-Funktionen bieten gegenüber Transact-SQL-Funktionen für Berechnungsaufgaben, Zeichenfolgebearbeitung und Geschäftslogik erhebliche Leistungsvorteile. 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

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 Inlinefunktion mit Tabellenrückgabe gibt es keinen Funktionshauptteil; die Tabelle ist das Resultset 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 Integrierte Funktionen (Transact-SQL), Gespeicherte Systemfunktionen (Transact-SQL) und Dynamische Verwaltungssichten und -funktionen (Transact-SQL).

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 von den Anweisungen in der Funktion vorgenommen werden dürfen, sind Änderungen an lokalen Objekten der Funktion, wie z. B. lokale Cursor oder Variablen. Änderungen an Datenbanktabellen, Cursorvorgänge außerhalb der Funktion, das Senden von E-Mails, das Ausführen einer Katalogänderung und das Generieren eines Resultsets, das an den Benutzer zurückgegeben wird, sind Beispiele für Aktionen, die in einer Funktion nicht 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 beim Aufrufen nicht aus.

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 (Datenbank-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 FETCH-Anweisungen, die lokalen Variablen Werte mithilfe der INTO-Klausel zuweisen, sind zulässig.
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 in benutzerdefinierten Transact-SQL-Funktionen nicht verwendet werden.

NEWID
NEWSEQUENTIALID
RAND
TEXTPTR

Eine Liste der deterministischen und nicht deterministischen integrierten 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 über einen Standardwert verfügt, muss beim Aufrufen der Funktion das DEFAULT-Schlüsselwort 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.