Erstellen Sie Tabellenwertfunktionen
Mithilfe von Tabellenwertfunktionen können Sie komplexe Abfragelogik in wiederverwendbare Komponenten kapseln, die Resultsets zurückgeben. Sie können diese Funktionen direkt in Abfragen aufrufen, genau wie Tabellen oder Ansichten, wodurch Ihr Code modularer und wartungsfähiger wird.
Beim Erstellen von Datenbankanwendungen müssen Sie häufig gefilterte oder berechnete Datasets basierend auf Eingabeparametern abrufen. Tabellenwertfunktionen lösen dieses Problem, indem Abfragelogik in Funktionen verpackt wird, die Parameter akzeptieren und Tabellen zurückgeben. Im Gegensatz zu gespeicherten Prozeduren können Sie tabellenwertige Funktionen in JOIN Klauseln und SELECT Anweisungen verwenden, sodass Sie die Flexibilität haben, Funktionsergebnisse als Datenquellen zu behandeln.
Grundlegendes zu Funktionstypen mit Tabellenwerten
SQL Server bietet zwei Typen von Tabellenwertfunktionen, die jeweils für unterschiedliche Szenarien geeignet sind.
Inline-Tabellenwertfunktion
Enthält eine einzelne SELECT Anweisung und gibt Ergebnisse direkt zurück. Mit Inlinefunktionen definieren Sie die Tabellenstruktur nicht – SQL Server leitet sie aus Ihrer SELECT Anweisung ab. Der Abfrageoptimierer behandelt Inline-Tabellenwertfunktionen ähnlich wie parametrisierte Ansichten und erzeugt oft bessere Ausführungspläne.
Tabellenwertfunktion mit mehreren Anweisungen
Verwendet einen BEGIN...END Block und deklariert explizit die Struktur der zurückgegebenen Tabelle. Dieser Typ bietet Ihnen mehr Kontrolle, wenn Sie mehrere Anweisungen ausführen, komplexe Berechnungen ausführen oder das Resultset iterativ erstellen müssen. Diese Flexibilität kommt jedoch mit einem Leistungsausgleich zusammen, da der Optimierer diese Funktionen anders behandelt.
Die Wahl zwischen diesen Funktionen hängt von Ihren spezifischen Anforderungen ab. Für einfache Abfragen mit Parametern bieten Inlinefunktionen eine bessere Leistung. Wenn Sie prozedurale Logik oder mehrere Schritte benötigen, um Ihren Ergebnissatz zu erstellen, werden Funktionen mit mehreren Anweisungen erforderlich.
Inline-Tabellenwertfunktionen erstellen
Inline-Tabellenwertfunktionen bieten eine knappe Methode zum Parametrisieren von Abfragen. Sie definieren sie mit einer einzelnen RETURN-Anweisung, gefolgt von einer SELECT-Abfrage.
Das folgende Beispiel zeigt eine Inlinefunktion, die Bestellungen für einen bestimmten Kunden abruft:
CREATE FUNCTION dbo.GetCustomerOrders
(
@CustomerID INT
)
RETURNS TABLE
AS
RETURN
(
SELECT
OrderID,
OrderDate,
TotalAmount,
Status
FROM Sales.Orders
WHERE CustomerID = @CustomerID
);
Sie können diese Funktion jetzt wie eine Tabelle in Abfragen verwenden:
SELECT OrderID, OrderDate, TotalAmount
FROM dbo.GetCustomerOrders(1001)
WHERE OrderDate >= '2024-01-01';
Die Funktion akzeptiert den Parameter "Kunden-ID" und gibt nur die Bestellungen dieses Kunden zurück. Sie können die Ergebnisse nach Bedarf weiter filtern JOINoder aggregieren. Dieser Ansatz sorgt dafür, dass die Hauptabfrage sauber bleibt, während die Kundenfilterlogik eingekapselt wird.
Erstellen von Tabellenwertfunktionen mit mehreren Anweisungen
Tabellenwertfunktionen mit mehreren Anweisungen bieten mehr Flexibilität, wenn Sie mehrere Vorgänge ausführen müssen, um Ihren Ergebnissatz zu erstellen.
Erwägen Sie eine Funktion, die Produktumsatzzusammenfassungen mit mehreren Aggregationen berechnet:
CREATE FUNCTION dbo.GetProductSalesSummary
(
@StartDate DATE,
@EndDate DATE
)
RETURNS @SalesSummary TABLE
(
ProductID INT,
ProductName NVARCHAR(100),
TotalQuantity INT,
TotalRevenue DECIMAL(18,2),
AveragePrice DECIMAL(18,2)
)
AS
BEGIN
INSERT INTO @SalesSummary
SELECT
p.ProductID,
p.ProductName,
SUM(od.Quantity) AS TotalQuantity,
SUM(od.Quantity * od.UnitPrice) AS TotalRevenue,
AVG(od.UnitPrice) AS AveragePrice
FROM Production.Products p
INNER JOIN Sales.OrderDetails od ON p.ProductID = od.ProductID
INNER JOIN Sales.Orders o ON od.OrderID = o.OrderID
WHERE o.OrderDate BETWEEN @StartDate AND @EndDate
GROUP BY p.ProductID, p.ProductName;
RETURN;
END;
Beachten Sie, wie Sie die Tabellenvariable @SalesSummary explizit mit bestimmten Spalten und Datentypen deklarieren. Der Funktionskörper fügt Daten in diese Tabellenvariable ein und gibt sie zurück. Mit dieser Struktur können Sie bei Bedarf zusätzliche Verarbeitungslogik, Fehlerbehandlung oder bedingte Anweisungen hinzufügen.
Verwenden von Tabellenwertfunktionen in Abfragen
Tabellenwertfunktionen lassen sich nahtlos in Ihre Abfragen integrieren und ermöglichen leistungsstarke Datenabrufmuster.
Sie können Funktionsergebnisse mit anderen Tabellen verknüpfen:
SELECT
c.CustomerName,
s.ProductName,
s.TotalRevenue
FROM Customers c
CROSS APPLY dbo.GetProductSalesSummary('2024-01-01', '2024-12-31') s
WHERE s.TotalRevenue > 10000
ORDER BY s.TotalRevenue DESC;
Der CROSS APPLY Operator ruft die Funktion für jede Zeile aus der Tabelle "Customers" auf, obwohl in diesem Beispiel die Funktionsparameter Konstanten sind. Wenn Sie Spaltenwerte als Parameter übergeben, wird CROSS APPLY besonders nützlich.
SELECT
c.CustomerName,
o.OrderID,
o.TotalAmount
FROM Customers c
CROSS APPLY dbo.GetCustomerOrders(c.CustomerID) o
WHERE o.Status = 'Completed';
Diese Abfrage ruft alle abgeschlossenen Bestellungen für jeden Kunden ab und veranschaulicht, wie tabellenwertige Funktionen die Zeilen-nach-Zeilen-Verarbeitung in Ihren Abfragen ermöglichen. Die Funktion fungiert als korrelierte Unterabfrage, aber mit besserer Lesbarkeit und Wiederverwendbarkeit.
Für Inline-Tabellenwertfunktionen, die keine Zeilen-nach-Zeilen-Auswertung erfordern, können Sie auch Syntax verwenden INNER JOIN :
SELECT
c.CustomerName,
o.OrderDate,
o.TotalAmount
FROM Customers c
INNER JOIN dbo.GetCustomerOrders(c.CustomerID) o ON 1=1
WHERE YEAR(o.OrderDate) = 2024;
Mit diesen Techniken können Sie komplexe Abfragen aus einfacheren, getesteten Funktionskomponenten erstellen und sowohl die Code-Wartung als auch die Entwicklungseffizienz verbessern.