⚙️ T-SQL benutzerdefinierte Skalarfunktionen
CREATE FUNCTION · SCHEMABINDING · Inlining · Best Practices
Wiederverwendbare Berechnungen als Einzelwert-Funktionen erstellen – sauber, modular, effizient einsetzbar.
Was sind benutzerdefinierte Skalarfunktionen?
GRUNDLAGEN
Eine benutzerdefinierte Skalarfunktion (User-Defined Function, UDF) gibt immer einen einzelnen Wert zurück – etwa einen String, eine Zahl oder ein Datum. Sie hilft dabei, wiederkehrende Berechnungen oder Geschäftslogik zentral zu definieren, einmal zu testen und dann immer wiederzuverwenden. So bleibt Code wartbarer und weniger fehleranfällig.
-- Ganz einfache Logik: Berechnung des Bruttopreises mit 19 % MwSt.
CREATE FUNCTION dbo.fn_Bruttopreis(@Netto DECIMAL(10,2))
RETURNS DECIMAL(10,2)
AS
BEGIN
RETURN @Netto * 1.19;
END;
GO
-- Einfacher Aufruf im SELECT
SELECT
Artikelname,
Preis,
dbo.fn_Bruttopreis(Preis) AS Bruttopreis
FROM Produkte;
💡 Erklärung: Die Funktion fn_Bruttopreis erwartet einen Nettopreis, multipliziert ihn mit 1,19 und gibt das Ergebnis zurück. Durch diese Kapselung spart man sich die Formel an jeder Stelle, was der Lesbarkeit enorm zugutekommt.
Syntax & grundlegender Aufbau
STRUKTUR
Der Aufbau einer benutzerdefinierten Skalarfunktion folgt einem einheitlichen Schema: CREATE FUNCTION Name, Parameter in Klammern, RETURNS mit dem Datentyp, AS BEGIN ... END als Funktionsrumpf und RETURN mit dem berechneten Wert.
-- Vollständige Syntax am Beispiel einer Funktion, die einen Kundenstatus ermittelt
CREATE FUNCTION dbo.fn_Kundenstatus (@Umsatz DECIMAL(10,2))
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @Status VARCHAR(20);
SET @Status =
CASE
WHEN @Umsatz > 10000 THEN 'Premium'
WHEN @Umsatz > 5000 THEN 'Gold'
ELSE 'Standard'
END;
RETURN @Status;
END;
GO
-- Optional: Ändern oder Anlegen mit CREATE OR ALTER
CREATE OR ALTER FUNCTION dbo.fn_Kundenstatus (@Umsatz DECIMAL(10,2))
RETURNS VARCHAR(20) AS BEGIN ... END;
📐 Erklärung: CREATE OR ALTER vereinheitlicht das Anlegen und Ändern – besonders nützlich in Skripten. Innerhalb des BEGIN...END-Blocks können beliebig viele T‑SQL-Anweisungen stehen, auch Variablen und komplexe Berechnungen. Am Ende steht zwingend RETURN mit dem Rückgabewert.
Praktische Beispiele ohne Datenbankzugriff
MATHEMATIK & LOGIK
Skalarfunktionen müssen nicht immer auf Tabellen zugreifen. Sie eignen sich auch hervorragend für reine Berechnungen, wenn diese zentral gepflegt werden sollen.
-- Rabattpreis berechnen
CREATE FUNCTION dbo.fn_DiscountPrice(@price DECIMAL(12,2), @discount DECIMAL(12,2))
RETURNS DECIMAL(12,2)
AS
BEGIN
RETURN @price * (1 - @discount);
END;
GO
-- Volljährigkeit prüfen (wahr/falsch) auf Basis eines Geburtsdatums
CREATE FUNCTION dbo.fn_IsAdult(@BirthDate DATE)
RETURNS BIT
AS
BEGIN
DECLARE @Age INT = DATEDIFF(YEAR, @BirthDate, GETDATE());
IF (MONTH(@BirthDate) > MONTH(GETDATE()))
SET @Age = @Age - 1;
ELSE IF (MONTH(@BirthDate) = MONTH(GETDATE())
AND DAY(@BirthDate) > DAY(GETDATE()))
SET @Age = @Age - 1;
RETURN IIF(@Age >= 18, 1, 0);
END;
🧮 Erklärung: fn_DiscountPrice demonstriert eine reine Rechenlogik, die überall konsistent verwendet werden kann. fn_IsAdult zeigt eine komplexere Berechnung mit bedingter Logik und Datumsvergleich. In Abfragen kann die Funktion dann für Filter oder abgeleitete Spalten genutzt werden.
Mit Datenbankzugriff – Werte aus Tabellen ermitteln
TABELLENZUGRIFF
Benutzerdefinierte Skalarfunktionen können lesend auf Tabellen zugreifen und Werte daraus ermitteln. Das ist etwa für wiederkehrende Lookup-Operationen interessant.
-- Anzahl der Bestellungen eines Kunden
CREATE FUNCTION dbo.fn_CountOrdersForCustomer(@CustomerID INT)
RETURNS INT
AS
BEGIN
DECLARE @OrderCount INT;
SELECT @OrderCount = COUNT(OrderID)
FROM Sales.Orders
WHERE CustomerID = @CustomerID;
RETURN @OrderCount;
END;
GO
-- Verfügbarer Lagerbestand nach Abzug von Reservierungen
CREATE FUNCTION dbo.fn_AvailableStock(@ProductID INT)
RETURNS INT
AS
BEGIN
DECLARE @Stock INT;
SELECT @Stock = p.UnitsInStock - ISNULL(SUM(r.ReservedQuantity), 0)
FROM Production.Products p
LEFT JOIN Sales.Reservations r ON p.ProductID = r.ProductID
WHERE p.ProductID = @ProductID
GROUP BY p.UnitsInStock;
RETURN @Stock;
END;
🗄️ Erklärung: In solchen Funktionen wird eine SELECT-Abfrage ausgeführt, deren Ergebnis in einer Variablen gespeichert und dann zurückgegeben wird. Das ist praktisch, allerdings ist Vorsicht geboten: Wird diese Funktion in einer großen Abfrage für jede Zeile aufgerufen, kann die Performance schnell dramatisch leiden. In neueren SQL-Versionen hilft hier das sogenannte Inlining, das die Funktion mehrfach in den Hauptquery einbettet.
⚠️ Wichtiger Hinweis: Skalarfunktionen mit Datenbankzugriff werden standardmäßig zeilenweise ausgeführt – das kann bei vielen Zeilen zu extrem langen Laufzeiten führen. Testen Sie solche Funktionen daher immer mit repräsentativen Datenmengen.
Erweiterte Optionen: SCHEMABINDING & NULL‑Behandlung
SCHEMABINDING · NULL‑Input
SCHEMABINDING verhindert, dass die von der Funktion referenzierten Tabellen oder Spalten ungewollt gelöscht oder geändert werden – das ist Voraussetzung für das Inlining. Mit RETURNS NULL ON NULL INPUT können wir außerdem angeben, dass die Funktion bei NULL‑Eingabe sofort NULL zurückgibt, ohne den Funktionstext auszuführen. Das spart Ressourcen.
-- Schema‑gebundene Funktion (schützt vor Änderungen der Basistabellen)
CREATE FUNCTION dbo.fn_GetCustomerCity_withBinding(@CustomerID INT)
RETURNS VARCHAR(50)
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS
BEGIN
DECLARE @City VARCHAR(50);
SELECT @City = City
FROM dbo.Customers
WHERE CustomerID = @CustomerID;
RETURN @City;
END;
GO
-- Explizite NULL‑Prüfung innerhalb der Logik
CREATE FUNCTION dbo.fn_SafeDivision(@Dividend DECIMAL(12,2), @Divisor DECIMAL(12,2))
RETURNS DECIMAL(12,2)
AS
BEGIN
RETURN
CASE
WHEN @Divisor IS NULL OR @Divisor = 0 THEN NULL
ELSE @Dividend / @Divisor
END;
END;
✨ Erklärung: SCHEMABINDING stellt die Grundlage für die inlining‑Optimierung dar. RETURNS NULL ON NULL INPUT führt dazu, dass bei NULL‑Argument die Funktion gar nicht erst ausgeführt wird – das reduziert CPU-Aufwand deutlich.
Performance & praxisnahe Alternativen
INLINING · Inline‑TVF
Skalarfunktionen sind elegant, aber in älteren SQL Server‑Versionen (oder bei nicht‑inline‑fähigen Funktionen) oft langsam, weil sie zeilenweise abgearbeitet werden. Seit SQL Server 2019 können viele Skalarfunktionen automatisch in den Hauptquery eingebettet werden – das reduziert den Overhead enorm. Zusätzlich lassen sich langsame Skalarfunktionen oft durch Inline- Tabellenwertfunktionen (iTVF) ersetzen, die sich set‑basiert optimieren lassen.
-- Beispiel einer iTVF, die dasselbe Ergebnis liefert wie eine Skalarfunktion
CREATE FUNCTION dbo.itvf_GetAvailableCredit(@CustomerId INT)
RETURNS TABLE
AS
RETURN
(
SELECT ISNULL(c.CreditLimit - SUM(o.OrderTotal), 0) AS AvailableCredit
FROM dbo.Customers c
LEFT JOIN dbo.Orders o ON c.CustomerId = o.CustomerId AND o.IsPaid = 0
WHERE c.CustomerId = @CustomerId
GROUP BY c.CreditLimit
);
GO
-- Aufruf mit CROSS APPLY (set‑basiert statt zeilenweise)
SELECT
c.CustomerId,
c.Name,
ac.AvailableCredit
FROM dbo.Customers c
CROSS APPLY dbo.itvf_GetAvailableCredit(c.CustomerId) ac;
🚀 Empfehlung für moderne Datenbanken: Nutzen Sie Inlining! Ab SQL Server 2019 bzw. Kompatibilitätsgrad 150 werden viele skalare UDFs automatisch inline eingebettet, was die Performance dramatisch verbessert. Nicht alle Funktionen sind jedoch inline‑fähig – insbesondere die Verwendung von Tabellenvariablen, TRY/CATCH oder dynamischem SQL blockiert das Inlining. Für maximale Performance ist die iTVF‑Alternative oft die beste Wahl.
💡 Fazit: Skalarfunktionen sind ein wertvolles Werkzeug für klar strukturierten Code. In Performance‑kritischen Bereichen sollten Sie entweder auf Inlining (SQL Server 2019+) vertrauen oder – noch besser – auf Inline‑Tabellenwertfunktionen umsteigen. Die Entscheidung hängt von der Komplexität der Logik und der erwarteten Datenmenge ab.
📌 Entscheidungshilfe: Wann Skalarfunktion, wann iTVF?
/* Skalarfunktion (traditionell) */
✅ sehr einfache, reine Rechenlogik ohne Datenbankzugriffe
✅ wiederverwendbare Formatierung / Umrechnung
✅ moderate Aufrufzahlen (wenige tausend Zeilen)
✅ Inlining‑fähige Funktion (MODIFIES SQL DATA vermeiden)
/* Inline‑Tabellenwertfunktion (iTVF) */
✅ komplexe Lookups mit Joins über mehrere Tabellen
✅ große Ergebnismengen (hunderttausende Zeilen)
✅ maximale Performance & Parallelität erwünscht
✅ einfaches Binden an vorhandene Indizes
⭐ Wichtigste Best Practices
- Verwenden Sie SCHEMABINDING – ermöglicht Inlining und schützt vor unbeabsichtigten Schema-Änderungen.
- Setzen Sie RETURNS NULL ON NULL INPUT, wenn die Funktion bei NULL‑Argumenten stets NULL liefern soll (das spart Ressourcen).
- Vermeiden Sie datenzugreifende Skalarfunktionen innerhalb großer WHERE‑Klauseln – das erzwingt row‑by‑row‑Ausführung.
- Prüfen Sie, ob die Funktion für Inlining geeignet ist: Nutzen Sie nicht nicht-inlineable Elemente wie Tabellenvariablen, dynamisches SQL oder TRY/CATCH.
- Erwägen Sie bei Performanceproblemen den Umbau in eine Inline‑Tabellenwertfunktion – oft der einfachste Weg zu drastisch besseren Laufzeiten.
✅ Fazit: Benutzerdefinierte Skalarfunktionen sind eine ausgezeichnete Wahl für modulare, wiederverwendbare Logik. Mit den modernen Optimierungen (Inlining) und der bewussten Wahl der Optionen behalten Sie sowohl sauberen Code als auch gute Performance.