Fonctions scalaires CLR

  • Article

S’applique à :SQL Server

Une fonction scalaire renvoie une valeur unique, telle qu'une chaîne, un entier ou une valeur binaire. Vous pouvez créer des fonctions scalaires définies par l’utilisateur dans du code managé à l’aide de n’importe quel langage de programmation .NET Framework. Ces fonctions sont accessibles à Transact-SQL ou un autre code managé. Pour plus d’informations sur les avantages de l’intégration du CLR et sur le choix entre le code managé et Transact-SQL, consultez Vue d’ensemble de l’intégration CLR.

Spécifications relatives aux fonctions scalaires CLR

Les fonctions scalaires .NET Framework sont implémentées en tant que méthodes d'une classe dans un assembly .NET Framework. Les paramètres d’entrée et le type retourné par un SVF peuvent être tous les types de données scalaires pris en charge par SQL Server, à l’exception de varchar, char, rowversion, text, ntext, image, timestamp, table ou cursor. Les SVM doivent garantir une correspondance entre le type de données SQL Server et le type de données de retour de la méthode d’implémentation. Pour plus d’informations sur les conversions de type, consultez Mappage des données de paramètres CLR.

Lors de l'implémentation d'une fonction scalaire .NET Framework dans un langage .NET Framework, l'attribut personnalisé SqlFunction peut être spécifié de façon à inclure des informations supplémentaires à propos de la fonction. L'attribut SqlFunction indique si la fonction accède ou modifie des données, si elle est déterministe et si elle implique des opérations de virgule flottante.

Les fonctions scalaires définies par l'utilisateur peuvent être déterministes ou non déterministes. Une fonction déterministe retourne toujours le même résultat lorsqu'elle est appelée avec un jeu de paramètres d'entrée spécifique. Une fonction non déterministe peut retourner des résultats différents lorsqu'elle est appelée avec un jeu de paramètres d'entrée spécifique.

Notes

Ne marquez pas une fonction comme étant déterministe si elle ne produit pas toujours les mêmes valeurs de sortie à partir des mêmes valeurs d'entrée et du même état de base de données. Le marquage d'une fonction comme étant déterministe alors que cette dernière ne l'est pas vraiment peut provoquer une altération des vues indexées et des colonnes calculées. Pour marquer une fonction comme déterministe, vous devez affecter la valeur « true » à la propriété IsDeterministic .

Paramètres table

Les paramètres table (types de tables définis par l'utilisateur et passés dans une procédure ou une fonction) offrent un moyen efficace pour passer plusieurs lignes de données au serveur. Les fournisseurs de services de sécurité fournissent des fonctionnalités similaires aux tableaux de paramètres, mais offrent une plus grande flexibilité et une intégration plus étroite avec Transact-SQL. Ils sont également susceptibles de générer de meilleures performances. Les paramètres table aident également à réduire le nombre d'allers-retours au serveur. Au lieu d'envoyer plusieurs demandes au serveur, comme avec une liste de paramètres scalaires, les données peuvent être envoyées au serveur en tant que paramètres table. Un type de table défini par l’utilisateur ne peut pas être passé en tant que paramètre table à une procédure stockée managée ou à partir d’une fonction s’exécutant dans le processus de SQL Server. Pour plus d’informations sur les fournisseurs de données, consultez Utiliser des paramètres de Table-Valued (moteur de base de données).

Exemple de fonction scalaire CLR

Voici une fonction scalaire simple qui accède à des données et renvoie une valeur entière :

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

La première ligne de code fait référence à Microsoft.SqlServer.Server afin d'accéder à des attributs et à System.Data.SqlClient afin d'accéder à l'espace de noms ADO.NET. (Cet espace de noms contient SqlClient, le fournisseur de données .NET Framework pour SQL Server.)

Ensuite, la fonction reçoit l'attribut personnalisé SqlFunction , qui se trouve dans l'espace de noms Microsoft.SqlServer.Server . L'attribut personnalisé indique si la fonction définie par l'utilisateur utilise le fournisseur in-process pour lire les données sur le serveur. SQL Server n’autorise pas les UDF à mettre à jour, insérer ou supprimer des données. SQL Server pouvez optimiser l’exécution d’une fonction UDF qui n’utilise pas le fournisseur in-process. Cela est indiqué en affectant la valeur DataAccessKind à DataAccessKind.None. Sur la ligne suivante, la méthode cible est une méthode statique publique (partagée dans Visual Basic .NET).

La classe SqlContext, située dans l’espace de noms Microsoft.SqlServer.Server, peut ensuite accéder à un objet SqlCommand avec une connexion au SQL Server instance déjà configuré. Bien qu'il ne soit pas utilisé ici, le contexte de transaction actuel est également disponible par le biais de l'API System.Transactions .

La plupart des lignes de code dans le corps de la fonction doivent sembler familières aux développeurs ayant écrit des applications clientes qui utilisent les types de l'espace de noms System.Data.SqlClient .

[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

Le texte de commande approprié est spécifié en initialisant l'objet SqlCommand . L'exemple précédent compte le nombre de lignes dans la table SalesOrderHeader. Ensuite, la méthode ExecuteScalar de l'objet cmd est appelée. Elle retourne une valeur de type int basée sur la requête. Pour finir, le nombre de commandes (« order count ») est retourné à l'appelant.

Si ce code est enregistré dans un fichier nommé FirstUdf.cs, il peut être compilé dans un assembly comme suit :

[C#]

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

[Visual Basic]

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

Notes

/t:library indique qu'une bibliothèque, plutôt qu'un fichier exécutable, doit être produite. Les exécutables ne peuvent pas être inscrits dans SQL Server.

Notes

Les objets de base de données Visual C++ compilés avec /clr:pure ne sont pas pris en charge pour l’exécution sur SQL Server. Il s'agit par exemple d'objets de base de données tels que des fonctions scalaires.

Voici la requête Transact-SQL et un exemple d'invocation pour enregistrer l'assembly et UDF :

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

Notez que le nom de fonction tel qu'exposé dans Transact-SQL ne doit pas correspondre au nom de la méthode statique du public cible.

Voir aussi

Mappage des données de paramètres CLR
Vue d'ensemble des attributs personnalisés de l'intégration du CLR
Fonctions définies par l'utilisateur
Accès aux données à partir d'objets de base de données CLR