Condividi tramite

Funzioni scalari CLR con valori scalari

  • Articolo

Si applica a:SQL Server

Una funzione con valori scalari (SVF) restituisce un singolo valore, ad esempio una stringa, un numero intero o un valore di bit. È possibile creare funzioni scalari definite dall'utente in codice gestito usando qualsiasi linguaggio di programmazione .NET Framework. Queste funzioni sono accessibili a Transact-SQL o ad altro codice gestito. Per informazioni sui vantaggi dell'integrazione CLR (Common Language Runtime) e sulla scelta tra codice gestito e Transact-SQL, vedere panoramica dell'integrazione con CLR.

Requisiti per le funzioni scalari CLR con valori

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 qualsiasi dei tipi di dati scalari supportati da SQL Server. except varchar, char, rowversion, text, ntext, image, timestamp, tabellao cursore. Le funzioni svfs devono garantire una corrispondenza tra il tipo di dati di 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.

Quando si implementa un SVF di .NET Framework in un linguaggio .NET Framework, è possibile specificare l'attributo personalizzato SqlFunction per includere informazioni aggiuntive sulla funzione. L'attributo SqlFunction indica se la funzione accede o non modifica i dati, se deterministica e se la funzione prevede operazioni a virgola mobile.

Le funzioni definite dall'utente con valori scalari possono essere deterministiche o non deterministiche. Una funzione deterministica restituisce sempre lo stesso risultato quando viene chiamato 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 la funzione non produce sempre gli stessi valori di output, in base agli stessi valori di input e allo 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. I provider di servizi di visualizzazione offrono funzionalità simili alle 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. Non è possibile passare un tipo di tabella definito dall'utente come parametro con valori di tabella a o essere restituito da una stored procedure gestita o una funzione in esecuzione nel processo di SQL Server. Per altre informazioni sui provider di servizi di configurazione, vedere Usare parametri con valori di tabella (motore di database).

Esempio di una funzione scalare-valued CLR

Ecco una semplice funzione SVF che accede ai dati e restituisce un valore intero:

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();
        }
    }
}

La prima riga di codice fa riferimento Microsoft.SqlServer.Server per accedere agli attributi e 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.

Successivamente, la funzione riceve l'attributo personalizzato SqlFunction, disponibile 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 funzioni definite dall'utente di aggiornare, inserire o eliminare dati. SQL Server può ottimizzare l'esecuzione di una funzione definita dall'utente che non usa il provider in-process. Ciò è indicato impostando DataAccessKind su DataAccessKind.None. Sulla riga successiva il metodo di destinazione è un metodo statico pubblico (condiviso in Visual Basic .NET).

La classe SqlContext, che si trova nello spazio dei nomi Microsoft.SqlServer.Server, può quindi accedere a un oggetto SqlCommand con una connessione all'istanza di SQL Server già configurata. Anche se non usato 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 dovrebbe avere un aspetto familiare agli sviluppatori che scrivono applicazioni client che usano i tipi presenti nello spazio dei nomi System.Data.SqlClient.

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();
}

Il testo del comando appropriato viene specificato inizializzando l'oggetto SqlCommand. Nell'esempio precedente viene conteggiato il numero di righe nella tabella SalesOrderHeader. Viene quindi chiamato il metodo ExecuteScalar dell'oggetto cmd. Viene restituito un valore di tipo int in base alla 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:

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

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

Gli oggetti di database di 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 registrare l'assembly e la funzione definita dall'utente sono:

CREATE ASSEMBLY FirstUdf
    FROM 'FirstUdf.dll';
GO

CREATE FUNCTION CountSalesOrderHeader()
RETURNS INT
AS EXTERNAL NAME FirstUdf.T.ReturnOrderCount;
GO

SELECT dbo.CountSalesOrderHeader();
GO

Il nome della funzione esposto in Transact-SQL non deve corrispondere al nome del metodo statico pubblico di destinazione.

Contenuto correlato