Funzioni a valori scalari CLR

Una funzione a valori scalari restituisce un valore singolo, come una stringa un Integer o un valore di bit. È possibile creare funzioni a valori scalari definite dall'utente nel codice gestito utilizzando qualsiasi linguaggio di programmazione di .NET Framework. Transact-SQL o un altro codice gestito può eccedere a queste funzioni. Per informazioni sui vantaggi dell'integrazione CLR e sulla scelta tra codice gestito e Transact-SQL, vedere Panoramica dell'integrazione CLR.

Requisiti per le funzioni a valori scalari CLR

Le funzioni a valori scalari di .NET Framework vengono implementate come metodi in una classe di un assembly .NET Framework. I parametri di input e il tipo restituito da un SVF possono essere uno dei tipi di dati scalari supportati da SQL Server, ad eccezione varcharntextrowversioncharimagetimestamptexttabledi , o .cursor Le svfs devono garantire una corrispondenza tra il tipo di dati SQL Server e il tipo di dati restituito del metodo di implementazione. Per altre informazioni sulle conversioni dei tipi, vedere Mapping dei dati dei parametri CLR.

In caso di implementazione di una funzione a valore scalare di .NET Framework in un linguaggio di .NET Framework, è possibile specificare l'attributo personalizzato SqlFunction per includere informazioni aggiuntive sulla funzione. L'attributo SqlFunction indica se la funzione accede ai dati o li modifica, se è deterministica e se comporta operazioni a virgola mobile.

Le funzioni definite dall'utente a valori scalari possono essere deterministiche o non deterministiche. Una funzione deterministica restituisce sempre lo stesso risultato quando viene chiamata con un set specifico di parametri di input. Una funzione non deterministica può restituire risultati diversi quando viene chiamata con un set specifico di parametri di input.

Nota

Non contrassegnare una funzione come deterministica se non produce sempre gli stessi valori di output, dati gli stessi valori di input e lo stesso stato del database. Una funzione non propriamente deterministica ma contrassegnata come tale può causare danni a viste indicizzate e colonne calcolate. Per contrassegnare una funzione come deterministica, impostare la proprietà IsDeterministic su true.

Parametri con valori di tabella

I parametri con valori di tabella, ovvero tipi di tabella definiti dall'utente passati in una procedura o in una funzione, consentono di passare in modo efficiente più righe di dati al server. Le TVP offrono funzionalità simili a matrici di parametri, ma offrono maggiore flessibilità e maggiore integrazione con Transact-SQL. Consentono inoltre di ottenere prestazioni potenzialmente migliori. I parametri con valori di tabella consentono inoltre di ridurre il numero di round trip al server. Anziché inviare più richieste al server, ad esempio con un elenco di parametri scalari, è possibile inviare i dati al server sotto forma di parametro con valori di tabella. Un tipo di tabella definito dall'utente non può essere passato come parametro con valori di tabella a o restituito da, una stored procedure gestita o una funzione in esecuzione nel processo di SQL Server. Per altre informazioni sugli indirizzi TV, vedere Usare parametri Table-Valued (motore di database).

Esempio di una funzione a valori scalari CLR

Di seguito è riportata una semplice funzione a valori scalari che accede ai dati e restituisce un valore integer:

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 prima riga di codice fa riferimento a Microsoft.SqlServer.Server per accedere ad attributi e a System.Data.SqlClient per accedere allo spazio dei nomi ADO.NET. Questo spazio dei nomi contiene SqlClient, il provider di dati .NET Framework per SQL Server.

Dopodiché, la funzione riceve l'attributo personalizzato SqlFunction, presente nello spazio dei nomi Microsoft.SqlServer.Server. L'attributo personalizzato indica se la funzione definita dall'utente utilizza il provider in-process per leggere dati nel server. SQL Server non consente alle UDF di aggiornare, inserire o eliminare dati. SQL Server può ottimizzare l'esecuzione di una funzione UDF che non usa il provider in-process. Questa possibilità viene indicata impostando DataAccessKind su DataAccessKind.None. Sulla riga successiva il metodo di destinazione è un metodo statico pubblico (condiviso in Visual Basic .NET).

La SqlContext classe, situata nello spazio dei nomi, può quindi accedere a un SqlCommand oggetto con una connessione all'istanza Microsoft.SqlServer.Server di SQL Server già configurata. Sebbene non venga utilizzato qui, il contesto della transazione corrente è disponibile anche tramite l'API (Application Programming Interface) di System.Transactions.

La maggior parte delle righe di codice nel corpo della funzione è già nota agli sviluppatori che hanno scritto applicazioni client che utilizzano i tipi presenti nello spazio dei nomi 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  

Il testo del comando appropriato viene specificato inizializzando l'oggetto SqlCommand. Nell'esempio precedente viene contato il numero di righe nella tabella SalesOrderHeader. Dopodiché, viene chiamato il metodo ExecuteScalar dell'oggetto cmd. Viene quindi restituito un valore di tipo int basato sulla query. Infine, il numero di ordini viene restituito al chiamante.

Se questo codice viene salvato in un file chiamato FirstUdf.cs, può essere compilato come assembly nel modo seguente:

[C#]

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

[Visual Basic]

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

Nota

/t:library indica che è necessario generare una libreria anziché un file eseguibile. I file eseguibili non possono essere registrati in SQL Server.

Nota

Gli oggetti di database visual C++ compilati con /clr:pure non sono supportati per l'esecuzione in SQL Server. Questo tipo di oggetti di database include, ad esempio, funzioni a valori scalari.

La query Transact-SQL e una chiamata di esempio per la registrazione dell'assembly e di UDF sono le seguenti:

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

Notare che il nome della funzione esposto in Transact-SQL può anche non corrispondere al nome del metodo statico pubblico di destinazione.

Vedere anche

Mapping dei dati dei parametri CLR
Panoramica degli attributi personalizzati dell'integrazione con CLR
Funzioni definite dall'utente
Accesso ai dati da oggetti di database CLR