Was ist der Unterschied zwischen Inline-Tabellenwerten und Funktionen mit mehreren Anweisungen in SQL?


Antwort 1:

Arten von benutzerdefinierten Funktionen:

  1. Skalarfunktionstabelle Wertfunktion
  • Inline Table Valued FunctionMulti-Anweisung Table Valued Function
  1. Skalarfunktion

Skalarfunktionen geben nur Skalar- / Einzelwerte zurück. Wir können die Skalarfunktion in der SELECT-Anweisung WHERE, GROUP BY, HAVING verwenden. Wir müssen den Datentyp dieses einzelnen Datenwerts in der RETURNS-Klausel der Funktionsdefinition erwähnen.

Beispiel: Funktionsdefinition -

CREATE FUNCTION GetDiseasedPatientsNumber (@DiseaseCode INT) RETURNS INT AS BEGIN DECLARE @Number_of_Patients INT SELECT @Number_of_Patients = COUNT (PatientID) FROM [dbo]. [Patienten] WHERE DiseaseCode = @DiseaseCode RETURN_N

Verwendung der obigen Skalarfunktion -

- Verwendung in der Select-Anweisung SELECT ProviderID, [Provider Name], [Clinic Code], [dbo]. [GetDiseasedPatientsNumber] (DCode) AS 'Anzahl der Diabetes-Patienten' FROM [dbo]. [Provider] - Verwendung in der Where-Klausel SELECT ProviderID, [Providername], [Klinikcode] FROM [dbo]. [Provider] WHERE [dbo]. [GetDiseasedPatientsNumber] (DiseaseCode)> = 150

  1. TABELLE BEWERTETE FUNKTION

Eine benutzerdefinierte Funktion mit Tabellenwert gibt eine Ergebnismenge / Zeilensatz anstelle eines Einzel- / Skalarwerts zurück. Es kann in der FROM- oder JOIN-Klausel einer SELECT-Abfrage aufgerufen werden. Tabellenwertfunktionen können eine gute Alternative zu VIEW sein, da VIEW keine Parameter zulässt, während Tabellenwertfunktionen Parameter zulassen. Manchmal werden sie als "parametrisierte Ansichten" bezeichnet.

  • INLINE TABLE VALUED FUNCTION

Eine Inline-Tabellenwert-Funktionsdefinition gibt nur RETURNS TABLE und nicht die Tabellendefinition an. Der gesamte Abfragestapel oder Code oder die ausgewählte Abfrage innerhalb der Funktion ist eine einzelne RETURN-Anweisung.

Beispiel: Funktionsdefinition -

FUNKTION ERSTELLEN CustomerProductDetails (@CustomerID NCHAR (5)) RÜCKGABETABELLE ALS RÜCKGABE (SELECT C.CustomerID, C.CompanyName, C.City, O.OrderID, O.OrderDate, P.ProductName, OD.UnitPrice, OD.Quantity OD Rabatt von [Produkte] P INNER JOIN [Bestelldetails] OD ON P.ProductID = OD.ProductID INNER JOIN Bestellungen O ON O.OrderID = OD.OrderID INNER JOIN Kunden C ON C.CustomerID = O.CustomerID WHERE C.CustomerID = @CustomerID) GO

Verwendung der obigen Funktion in der FROM-Klausel -

- Einfache Select-Anweisung SELECT * FROM dbo.CustomerProductDetails ('VINET') - VERBINDEN mit Employee TABLE SELECT E.EmployeeID, E.LastName, E.FirstName, E.Title, CP.CustomerID, CP.CompanyName, CP.City , CP.OrderID, CP.OrderDate, CP.EmployeeID, CP.ProductName, CP.UnitPrice, CP.Quantity, CP.Discount FROM [Employees] E JOIN dbo.CustomerProductDetails ('VINET') CP ON CP.EmployeeID = E. Mitarbeiter-ID

  • MULTI STATEMENT TABLE VALUED FUNCTION

Die Funktionsdefinition für Tabellen mit mehreren Anweisungen gibt RETURNS zusammen mit der Definition von TABLE VARIABLE an. Der Funktionskörper kann aus mehreren Anweisungen bestehen, von denen eine diese TABLE VARIABLE auffüllt. Und der Umfang / die Lebensdauer dieser TABELLE VARIABLE ist nur auf diese Funktion beschränkt - außerhalb dieser Funktion ist sie nicht verfügbar.

Beispiel: Funktionsdefinition -

FUNKTION ERSTELLEN CustomerOrderDetails (@CustomerID NCHAR (5)) RETURNS @CustomerOrders TABELLE (CustomerID NCHAR (5), Firmenname NVARCHAR (40), OrderID INT, Bestelldatum DATETIME) AS BEGIN INSERT IN @CustomerOrders SELECT C.Comp .OrderID, O.OrderDate FROM Customers C INNER JOIN Orders O ON C.CustomerID = C.CustomerID WHERE C.CustomerID = @CustomerID IF @@ ROWCOUNT = 0 BEGIN INSERT IN @CustomerOrders VALUES ('', 'No Orders Found', 0, GETDATE ()) END RETURN END GO

Unterschied zwischen Inline- und Multi-Statement-Tabellenwertfunktionen:

Hinweis:

  1. Wenn wir zusätzliche Datenmanipulationen an SELECT-Daten wie INSERT, UPDATE, DELETE oder einer Filterung durchführen müssen, bevor wir die endgültige Ausgabe in die Tabellenvariable einfügen, wählen wir die Funktion MULTI-STATEMENT Table Valued. Wenn wir eine komplexe Logik ausführen müssen Geben Sie den Parameter ein, bevor Sie ihn in der WHERE-Klausel verwenden. Wir entscheiden uns für die Funktion MULTI-STATEMENT Table Valued. Wenn wir eine Logik vom Typ VIEW implementieren, aber auch einen Parameter übergeben möchten - Parametrisierte Ansicht - wählen wir die Funktion Inline Table Valued .

Einschränkungen für benutzerdefinierte SQL-Funktionen:

  1. Nicht deterministische eingebaute Funktionen können nicht in benutzerdefinierten Funktionen verwendet werden. Beispiel: GETDATE () oder RAND (). XML-Datentyp wird nicht unterstützt. Dynamische SQL-Abfragen sind nicht zulässig. Benutzerdefinierte Funktionen unterstützen keine DML-Anweisungen (INSERT, UPDATE, DELETE), es sei denn, sie werden für Tabellenvariablen ausgeführt Ein Aufruf der gespeicherten Prozedur. Wir können keine temporären Tabellen in UDFs erstellen. Die Fehlerbehandlung in UDF wird nicht unterstützt. Wir können jedoch Fehler (RAISEERROR, TRY-CATCH) für die Anweisungen behandeln, die diese Funktion verwenden.

Das war `s für heute. Hoffe das ist hilfreich. :) :)

Viel Glück. :) :)


Antwort 2:

Zunächst einmal glaube ich, dass diese Frage MS SQL Server-spezifisch ist. Ich weiß, dass Oracle eine Tabellenfunktionsunterstützung bietet, aber das ist völlig anders, und ich konnte mich in den sechs Jahren, in denen ich Oracle verwendet habe, nie darum kümmern.

Sachins Antwort ist alles richtig, übersieht jedoch die Auswirkungen auf die Leistung, was der Hauptgrund für die Wahl einer Inline-Tabellenwertfunktion (ITVF) ist. Ein ITVF ist wirklich nur eine Ansicht mit Parametern. Es ist ein Ausdruck, keine Reihe von Schritten, die ausgeführt werden müssen. Daher kombiniert SQL Server ihn mit der Abfrage, die ihn verwendet, und wertet das Ganze auf einmal aus. Wenn Ihre ITVF 200 Suchvorgänge enthält, die aufrufende Abfrage jedoch nur eine davon wünscht, kann der Optimierer alle nicht angeforderten Suchvorgänge vollständig ignorieren, wenn Sie sorgfältig darauf geachtet haben, wie Sie die ITVF geschrieben haben. Das ist wirklich gut, um den Code an einer Vielzahl von Orten wiederverwendbar zu machen.

Dinge, die Sie beim Schreiben eines ITVF für die Leistung beachten sollten:

  • Übergeben Sie die Schlüssel nicht als Parameter. Sie erhalten einen schönen, leicht zu verfolgenden Code, der jedoch alles zu Loop-Joins zwingt, und die Leistung ist schrecklich. Speichern Sie die Parameter für Dinge wie effektive Datumsbereiche und treten Sie der ITVF bei, um Ihre Population zu begrenzen. Versuchen Sie, die ITVF um eine Kerntabelle herum zu schreiben, auf der darauf basierende Suchvorgänge basieren. Die Suchvorgänge sollten entweder "äußerlich anwenden (oben 1 auswählen)" oder links mit einer Primärschlüsselspalte verknüpft sein. In diesen Fällen kann die Suche die Anzahl der zurückgegebenen Zeilen weder erhöhen noch verringern, und der Optimierer kann dies beweisen, sodass der Optimierer sie entfernen kann, wenn sie nicht verwendet wurden. Dies gilt auch für Ansichten.