Funzioni a valori scalari CLR
Una funzione a valori scalari restituisce un solo valore, ad esempio un valore string, integer o bit. A partire da SQL Server 2005, è possibile creare le funzioni definite dall'utente a valori scalari nel codice gestito utilizzando qualsiasi linguaggio di programmazione di .NET Framework. Queste funzioni sono accessibili a Transact-SQL o ad altro codice gestito. Per informazioni sui vantaggi dell'integrazione con CLR e sulla scelta tra codice gestito e Transact-SQL, vedere Panoramica dell'integrazione con CLR.
Requisiti per le funzioni a valori scalari CLR
Le funzioni a valori scalari di .NET Framework (.NET SVF) vengono implementate come metodi su una classe in un assembly .NET Framework. I parametri di input e il tipo restituito da una funzione a valori scalari possono appartenere a uno qualsiasi dei tipi di dati scalari supportati da SQL Server, ad eccezione di varchar, char, rowversion, text, ntext, image, timestamp, table o cursor. Le funzioni a valori scalari devono assicurare una corrispondenza tra il tipo di dati di SQL Server e il tipo dati restituito del metodo di implementazione. Per ulteriori 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 i 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. Pur essendo caratterizzati da funzionalità simili alle matrici di parametri, i parametri con valori di tabella offrono più flessibilità e una maggiore integrazione con Transact-SQL e consentono 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 una stored procedure gestita o a una funzione in esecuzione nel processo SQL Server o restituito dalle stesse. Per ulteriori informazioni sui parametri con valori di tabella, vedere Parametri con valori di tabella (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 i 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 utilizza 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 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. 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 denominato 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 produrre una libreria anziché un eseguibile. I file eseguibili non possono essere registrati in SQL Server.
Nota
L'esecuzione degli oggetti di database Visual C++ compilati con /clr:pure non è più supportata in SQL Server. Questo tipo di oggetti di database include, ad esempio, funzioni a valori scalari.
Di seguito sono riportate la query Transact-SQL e una chiamata di esempio per registrare l'assembly e la funzione definita dall'utente.
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 non dovere corrispondere il nome del metodo statico pubblico di destinazione.
Nota
A partire da SQL Server 2005, su un database SQL Server con un livello di compatibilità di "80", non è possibile creare tipi definiti dall'utente, stored procedure, funzioni, aggregazioni o trigger gestiti. Per sfruttare al meglio queste caratteristiche di integrazione CLR di SQL Server, è necessario utilizzare le stored procedure sp_dbcmptlevel (Transact-SQL) per impostare il livello di compatibilità del database a "100."
Vedere anche