Teilen über

CLR-Skalarwertfunktionen

  • Artikel

Gilt für: SQL Server

Eine skalare Funktion (SVF) gibt einen einzelnen Wert zurück, z. B. eine Zeichenfolge, eine ganze Zahl oder einen Bitwert. Sie können skalare benutzerdefinierte Funktionen in verwaltetem Code mithilfe einer beliebigen .NET Framework-Programmiersprache erstellen. Auf diese Funktionen kann über Transact-SQL oder anderen verwalteten Code zugegriffen werden. Informationen zu den Vorteilen der CLR-Integration und der Auswahl zwischen verwaltetem Code und Transact-SQL finden Sie in der Übersicht über die CLR-Integration.

Anforderungen für CLR-Skalarwertfunktionen

.NET Framework-Skalarwertfunktionen werden als Methoden einer Klasse in einer .NET Framework-Assembly implementiert. Die eingabeparameter und der typ, der von einem SVF zurückgegeben wird, kann jeder der skalaren Datentypen sein, die von SQL Server unterstützt werden, mit Ausnahme von Varchar, char, rowversion, text, ntext, image, timestamp, table, or cursor. SVFs müssen eine Übereinstimmung zwischen dem SQL Server-Datentyp und dem Rückgabedatentyp der Implementierungsmethode sicherstellen. Weitere Informationen zu Typkonvertierungen finden Sie unter Zuordnung von CLR-Parameterdaten.

Beim Implementieren eines .NET Framework SVF in einer .NET Framework-Sprache kann das benutzerdefinierte SqlFunction-Attribut angegeben werden, um zusätzliche Informationen zur Funktion einzuschließen. Das SqlFunction-Attribut gibt an, ob die Funktion auf Daten zugreift oder ändert, wenn sie deterministisch ist und ob die Funktion Gleitkommavorgänge umfasst.

Benutzerdefinierte Skalarwertfunktionen können deterministisch oder nicht deterministisch sein. Eine deterministische Funktion gibt immer dieselben Ergebnisse zurück, wenn sie mit einem bestimmten Satz an Eingabeparametern aufgerufen wird. Eine nicht deterministische Funktion kann unterschiedliche Ergebnisse zurückgeben, wenn sie mit einem bestimmten Satz an Eingabeparametern aufgerufen wird.

Hinweis

Markieren Sie eine benutzerdefinierte Funktion nicht als deterministisch, wenn die Funktion bei denselben Eingabewerten und demselben Datenbankzustand nicht immer dieselben Ausgabewerte erzeugt. Das Markieren einer Funktion als deterministisch, wenn die Funktion nicht wirklich deterministisch ist, kann zu beschädigten indizierten Ansichten und berechneten Spalten führen. Sie markieren eine Funktion als deterministisch, indem Sie die IsDeterministic-Eigenschaft auf "true" festlegen.

Tabellenwertparameter

Tabellenwertparameter (Table Valued Parameters, TVPs), benutzerdefinierte Tabellentypen, die an eine Prozedur oder Funktion übergeben werden, bieten eine effiziente Methode zum Übergeben mehrerer Datenzeilen an den Server. TVPs bieten ähnliche Funktionen wie Parameterarrays, bieten jedoch größere Flexibilität und engere Integration in Transact-SQL. Außerdem verfügen sie auch über ein besseres Leistungspotenzial. TVPs helfen auch, die Anzahl von Roundtrips zum Server zu reduzieren. Anstatt mehrere Anforderungen an den Server zu senden, z. B. mit einer Liste von skalaren Parametern, können Daten als TVP an den Server gesendet werden. Ein benutzerdefinierter Tabellentyp kann nicht als Tabellenwertparameter an eine verwaltete gespeicherte Prozedur oder Funktion übergeben werden, die im SQL Server-Prozess ausgeführt wird. Weitere Informationen zu TVPs finden Sie unter Use Table-Valued Parameters (Datenbank-Engine).For more information about TVPs, see Use Table-Valued Parameters (Datenbank-Engine).

Beispiel für eine CLR-Skalarwertfunktion

Es folgt eine einfache Skalarwertfunktion, die auf Daten zugreift und einen ganzzahligen Wert zurückgibt:

using Microsoft.SqlServer.Server;  
using System.Data.SqlClient;  
  
public class T  
{  
    [SqlFunction(DataAccess = DataAccessKind.Read)]  
    public static int ReturnOrderCount()  
    {  
        using (SqlConnection conn   
            = new SqlConnection("context connection=true"))  
        {  
            conn.Open();  
            SqlCommand cmd = new SqlCommand(  
                "SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn);  
            return (int)cmd.ExecuteScalar();  
        }  
    }  
}  

Imports Microsoft.SqlServer.Server  
Imports System.Data.SqlClient  
  
Public Class T  
    <SqlFunction(DataAccess:=DataAccessKind.Read)> _  
    Public Shared Function ReturnOrderCount() As Integer  
        Using conn As New SqlConnection("context connection=true")  
            conn.Open()  
            Dim cmd As New SqlCommand("SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn)  
            Return CType(cmd.ExecuteScalar(), Integer)  
        End Using  
    End Function  
End Class

Die erste Codezeile verweist auf Microsoft.SqlServer.Server , um auf Attribute und System.Data.SqlClient zuzugreifen, um auf den ADO.NET Namespace zuzugreifen. (Dieser Namespace enthält SqlClient, das .NET Framework Datenanbieter für SQL Server.)

Als Nächstes empfängt die Funktion das benutzerdefinierte SqlFunction-Attribut , das im Microsoft.SqlServer.Server-Namespace gefunden wird. Das benutzerdefinierte Attribut gibt an, ob die benutzerdefinierte Funktion (UDF) den prozessinternen Anbieter verwendet, um Daten im Server zu lesen. SQL Server lässt UDFs nicht zu, Daten zu aktualisieren, einzufügen oder zu löschen. SQL Server kann die Ausführung einer UDF optimieren, die den In-Process-Anbieter nicht verwendet. Dies wird durch Festlegen von DataAccessKind auf DataAccessKind.None angegeben. Die Zielmethode in der nächsten Zeile ist eine öffentliche statische Methode (shared in Visual Basic .NET).

Die SqlContext-Klasse , die sich im Microsoft.SqlServer.Server-Namespace befindet, kann dann auf ein SqlCommand-Objekt mit einer Verbindung mit der bereits eingerichteten SQL Server-Instanz zugreifen. Obwohl hier nicht verwendet wird, ist der aktuelle Transaktionskontext auch über die Anwendungsprogrammierschnittstelle System.Transactions (API) verfügbar.

Die meisten Codezeilen im Funktionstext sollten Entwicklern vertraut sein, die Clientanwendungen geschrieben haben, die die im System.Data.SqlClient-Namespace gefundenen Typen verwenden.

[C#]

using(SqlConnection conn = new SqlConnection("context connection=true"))   
{  
   conn.Open();  
   SqlCommand cmd = new SqlCommand(  
        "SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn);  
   return (int) cmd.ExecuteScalar();  
}

[Visual Basic]

Using conn As New SqlConnection("context connection=true")  
   conn.Open()  
   Dim cmd As New SqlCommand( _  
        "SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn)  
   Return CType(cmd.ExecuteScalar(), Integer)  
End Using

Der entsprechende Befehlstext wird durch Initialisieren des SqlCommand-Objekts angegeben. Im vorherigen Beispiel wird die Anzahl der Zeilen in der Tabelle "SalesOrderHeader" gezählt. Als Nächstes wird die ExecuteScalar-Methode des cmd-Objekts aufgerufen. Dadurch wird ein Wert vom Typ "int " basierend auf der Abfrage zurückgegeben. Abschließend wird die Anzahl der Bestellungen an den Aufrufer zurückgegeben.

Wenn dieser Code in einer Datei namens FirstUdf.cs gespeichert wird, kann er wie folgt als Assembly kompiliert werden:

[C#]

csc.exe /t:library /out:FirstUdf.dll FirstUdf.cs

[Visual Basic]

vbc.exe /t:library /out:FirstUdf.dll FirstUdf.vb

Hinweis

/t:library gibt an, dass eine Bibliothek und keine ausführbare Datei erzeugt werden soll. Ausführbare Dateien können nicht in SQL Server registriert werden.

Hinweis

Visual C++-Datenbankobjekte, die mit /clr:pure kompiliert wurden, werden für die Ausführung auf SQL Server nicht unterstützt. Zu solchen Datenbankobjekten gehören beispielsweise Skalarwertfunktionen.

Die Transact-SQL-Abfrage und ein Beispielaufruf zum Registrieren der Assembly und der UDF sind:

CREATE ASSEMBLY FirstUdf FROM 'FirstUdf.dll';  
GO  
  
CREATE FUNCTION CountSalesOrderHeader() RETURNS INT   
AS EXTERNAL NAME FirstUdf.T.ReturnOrderCount;   
GO  
  
SELECT dbo.CountSalesOrderHeader();  
GO

Beachten Sie, dass der Funktionsname, der in Transact-SQL verfügbar gemacht wird, nicht mit dem Namen der öffentlichen Zielmethode übereinstimmen muss.

Weitere Informationen

Zuordnen von CLR-Parameterdaten
Übersicht über benutzerdefinierte Attribute der CLR-Integration
Benutzerdefinierte Funktionen
Datenzugriff von CLR-Datenbankobjekten aus