Artikel
09/24/2010

CLR-Skalarwertfunktionen

Eine Skalarwertfunktion (SVF) gibt einen einzelnen Wert zurück, z. B. eine Zeichenfolge, ganze Zahl oder Bitwert. Ab SQL Server 2005 können Sie benutzerdefinierte Skalarwertfunktionen in jeder beliebigen .NET Framework-Programmiersprache in verwaltetem Code schreiben. Auf diese Funktionen kann über Transact-SQL oder anderen verwalteten Code zugegriffen werden. Informationen über die Vorteile der CLR-Integration und das Auswählen zwischen verwaltetem Code und Transact-SQL finden Sie unter Ü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 Rückgabetyp einer Skalarwertfunktion können einen beliebigen skalaren Datentyp haben, der von SQL Server unterstützt wird, mit Ausnahme von varchar, char, rowversion, text, ntext, image, timestamp, table und cursor. Skalarwertfunktionen müssen sicherstellen, dass der SQL Server-Datentyp und der Rückgabedatentyp der Implementierungsmethode übereinstimmen. Weitere Informationen zu Typkonvertierungen finden Sie unter Zuordnen von CLR-Parameterdaten.

Zur Implementierung einer .NET Framework-Skalarwertfunktion in einer .NET Framework-Sprache kann das benutzerdefinierte SqlFunction-Attribut angegeben werden, um zusätzliche Informationen über die Funktion aufzunehmen. Das SqlFunction-Attribut gibt an, ob die Funktion auf Daten zugreift oder Daten verändert, ob sie deterministisch ist und ob die Funktion Gleitkommaberechnungen beinhaltet.

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. Wenn Sie eine Funktion als deterministisch markieren, sie aber nicht wirklich deterministisch ist, kann dies zu beschädigten indizierten Sichten und berechneten Spalten führen. Sie markieren eine Funktion als deterministisch, indem Sie die IsDeterministic-Eigenschaft auf true festlegen.

Markieren Sie eine benutzerdefinierte Funktion nicht als deterministisch, wenn die Funktion bei denselben Eingabewerten und demselben Datenbankzustand nicht immer dieselben Ausgabewerte erzeugt. Wenn Sie eine Funktion als deterministisch markieren, sie aber nicht wirklich deterministisch ist, kann dies zu beschädigten indizierten Sichten 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 verfügen über eine ähnliche Funktionalität wie Parameterarrays, bieten aber größere Flexibilität und engere Integration mit Transact-SQL. Sie besitzen auch ein besseres Leistungspotzenzial. TVPs helfen auch, die Anzahl von Roundtrips zum Server zu reduzieren. Anstatt mehrere Anfragen 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, die im SQL Server-Prozess ausgeführt wird, übergeben werden oder von einer solchen Prozedur oder Funktion zurückgegeben werden. Weitere Informationen zu Tabellenwertparametern finden Sie unter Tabellenwertparameter (Datenbankmodul).

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 zuzugreifen, und auf System.Data.SqlClient, um auf den ADO.NET-Namespace zuzugreifen. (Dieser Namespace enthält SqlClient, den .NET Framework-Datenanbieter für SQL Server.)

Danach empfängt die Funktion das benutzerdefinierte SqlFunction-Attribut, das im Microsoft.SqlServer.Server-Namespace enthalten ist. Das benutzerdefinierte Attribut gibt an, ob die benutzerdefinierte Funktion den prozessinternen Anbieter zum Lesen der Daten auf dem Server verwendet. SQL Server lässt nicht zu, dass benutzerdefinierte Funktionen Daten aktualisieren, einfügen oder löschen. SQL Server kann die Ausführung einer benutzerdefinierten Funktion optimieren, die den prozessinternen Anbieter nicht verwendet. Dies wird angegeben, indem DataAccessKind auf DataAccessKind.None festgelegt wird. Die Zielmethode in der nächsten Zeile ist eine öffentliche statische Methode (shared in Visual Basic .NET).

Die SqlContext-Klasse, die im Microsoft.SqlServer.Server-Namespace enthalten ist, kann dann über die bereits eingerichtete Verbindung mit der SQL Server-Instanz auf ein SqlCommand-Objekt zugreifen. Hier wird zwar kein Gebrauch davon gemacht, aber auch der aktuelle Transaktionskontext ist über die System.Transactions-Anwendungsprogrammierschnittstelle (API) verfügbar.

Die meisten Codezeilen im Funktionsrumpf sollten Entwicklern bekannt vorkommen, die bereits Clientanwendungen mit Typen aus dem System.Data.SqlClient-Namespace geschrieben haben.

[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 angegeben, indem das SqlCommand-Objekt initialisiert wird. 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. Daraufhin wird ein Wert des Typs int zurückgegeben, der auf der Abfrage basiert. Abschließend wird die Anzahl der Bestellungen an den Aufrufer zurückgegeben.

Wenn dieser Code in einer Datei namens FirstUdf.cs gespeichert wird, kann sie 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: Bibliothek 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, können nicht in SQL Server ausgeführt werden. Zu solchen Datenbankobjekten gehören beispielsweise Skalarwertfunktionen.

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

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 angegeben wird, nicht mit dem Namen der öffentlichen statischen Zielmethode übereinstimmen muss.

Hinweis
Ab SQL Server 2005 können Sie in einer SQL Server-Datenbank mit einem Kompatibilitätsgrad von "80" keine verwalteten benutzerdefinierten Typen, gespeicherten Prozeduren, Funktionen, Aggregate oder Trigger erstellen. Um diese CLR-Integrationsfeatures von SQL Server nutzen zu können, müssen Sie mit der gespeicherten Prozedur sp_dbcmptlevel (Transact-SQL) den Kompatibilitätsgrad der Datenbank auf "100" festlegen.

Ab SQL Server 2005 können Sie in einer SQL Server-Datenbank mit einem Kompatibilitätsgrad von "80" keine verwalteten benutzerdefinierten Typen, gespeicherten Prozeduren, Funktionen, Aggregate oder Trigger erstellen. Um diese CLR-Integrationsfeatures von SQL Server nutzen zu können, müssen Sie mit der gespeicherten Prozedur sp_dbcmptlevel (Transact-SQL) den Kompatibilitätsgrad der Datenbank auf "100" festlegen.