Registrazione dei tipi definiti dall'utente in SQL Server
Per utilizzare un tipo definito dall'utente (UDT) in Microsoft SQL Server, è necessario registrarlo. La registrazione di un tipo definito dall'utente comporta la registrazione dell'assembly e la creazione del tipo nel database in cui si desidera utilizzarlo. I tipi definiti dall'utente vengono definiti nell'ambito di un singolo database e non possono essere utilizzati in più database a meno che lo stesso assembly e lo stesso tipo definito dall'utente non vengano registrati con ogni database. Dopo la registrazione dell'assembly UDT e la creazione del tipo, è possibile utilizzare il tipo definito dall'utente in Transact-SQL e nel codice client. Per ulteriori informazioni, vedere Tipi CLR definiti dall'utente.
Utilizzo di Visual Studio per distribuire i tipi definiti dall'utente
Il modo più semplice per distribuire il tipo definito dall'utente è utilizzare Microsoft Visual Studio. Per scenari di distribuzione più complessi e una maggiore flessibilità utilizzare tuttavia Transact-SQL come illustrato più avanti in questo argomento.
Seguire questi passaggi per creare e distribuire un tipo definito dall'utente mediante Visual Studio:
Creare un nuovo progetto Database nei nodi relativi ai linguaggi Visual Basic o Visual C#.
Aggiungere un riferimento al database di SQL Server che conterrà il tipo definito dall'utente.
Aggiungere una classe Tipo definito dall'utente.
Scrivere il codice per implementare il tipo definito dall'utente.
Selezionare Distribuisci dal menu Compila per registrare l'assembly e creare il tipo nel database di SQL Server.
Utilizzo di Transact-SQL per distribuire i tipi definiti dall'utente
La sintassi Transact-SQL CREATE ASSEMBLY viene utilizzata per registrare l'assembly nel database in cui si desidera utilizzare il tipo definito dall'utente. Viene archiviata internamente nelle tabelle di sistema del database e non esternamente nel file system. Se il tipo definito dall'utente dipende dagli assembly esterni, questi dovranno essere caricati nel database. L'istruzione CREATE TYPE viene utilizzata per creare il tipo definito dall'utente nel database in cui deve essere utilizzato. Per ulteriori informazioni, vedere CREATE ASSEMBLY (Transact-SQL) e CREATE TYPE (Transact-SQL).
Utilizzo di CREATE ASSEMBLY
La sintassi CREATE ASSEMBLY registra l'assembly nel database in cui si desidera utilizzare il tipo definito dall'utente. Dopo avere registrato l'assembly, non saranno presenti dipendenze.
Non è possibile creare più versioni dello stesso assembly in uno specifico database. Tuttavia, è possibile creare più versioni dello stesso assembly in base alla lingua in uno specifico database. SQL Server distingue più versioni della lingua di un assembly in base ai diversi nomi registrati nell'istanza di SQL Server. Per ulteriori informazioni, vedere la sezione relativa alla creazione e all'utilizzo di assembly con nome sicuro in .NET Framework SDK.
Quando CREATE ASSEMBLY viene eseguito con il set di autorizzazioni SAFE o EXTERNAL_ACCESS, l'assembly viene controllato per garantire che sia verificabile e indipendente dai tipi. Se non si specifica un set di autorizzazioni, viene utilizzato SAFE. Il codice con il set di autorizzazioni UNSAFE non viene controllato. Per ulteriori informazioni sui set di autorizzazioni per gli assembly, vedere Progettazione di assembly.
Esempio
Nell'istruzione Transact-SQL seguente viene registrato l'assembly Point in SQL Server nel database AdventureWorks2008R2, con il set di autorizzazioni SAFE. Se la clausola WITH PERMISSION_SET viene omessa, l'assembly viene registrato con il set di autorizzazioni SAFE.
USE AdventureWorks2008R2;
CREATE ASSEMBLY Point
FROM '\\ShareName\Projects\Point\bin\Point.dll'
WITH PERMISSION_SET = SAFE;
Nell'istruzione Transact-SQL seguente viene registrato l'assembly utilizzando l'argomento <assembly_bits> nella clausola FROM. Il valore varbinary rappresenta il file come flusso di byte.
USE AdventureWorks2008R2;
CREATE ASSEMBLY Point
FROM 0xfeac4 … 21ac78;
Utilizzo di CREATE TYPE
Dopo avere caricato l'assembly nel database, è possibile creare il tipo utilizzando l'istruzione Transact-SQL CREATE TYPE. In questo modo il tipo viene aggiunto all'elenco di tipi disponibili per il database. Il tipo viene definito nell'ambito del database e può essere utilizzato solo nel database in cui è stato creato. Se il tipo definito dall'utente esiste già nel database, l'istruzione CREATE TYPE ha esito negativo e restituisce un errore.
Nota
La sintassi CREATE TYPE viene utilizzata anche per la creazione dei tipi di dati alias SQL Server nativi e deve sostituire sp_addtype per creare i tipi di dati alias. Alcuni argomenti facoltativi nella sintassi CREATE TYPE si riferiscono alla creazione dei tipi definiti dall'utente e non sono applicabili alla creazione dei tipi di dati alias, ad esempio il tipo di base.
Nota
A partire da SQL Server 2005, in un database di SQL Server con livello di compatibilità pari a "80" non è possibile creare tipi definiti dall'utente, stored procedure, funzioni, aggregazioni o trigger gestiti. Per sfruttare le caratteristiche di integrazione con CLR di SQL Server, è necessario utilizzare la stored procedure sp_dbcmptlevel (Transact-SQL) per impostare il livello di compatibilità del database su "100".
Per ulteriori informazioni, vedere CREATE TYPE (Transact-SQL).
Esempio
Nell'istruzione Transact-SQL seguente viene creato il tipo Point. EXTERNAL NAME viene specificato utilizzando la sintassi di denominazione in due parti NomeAssembly.NomeUDT.
CREATE TYPE dbo.Point
EXTERNAL NAME Point.[Point];
Rimozione di un tipo definito dall'utente dal database
L'istruzione DROP TYPE rimuove un tipo definito dall'utente dal database corrente. Dopo avere eliminato un tipo definito dall'utente, è possibile utilizzare l'istruzione DROP ASSEMBLY per eliminare l'assembly dal database.
L'istruzione DROP TYPE non viene eseguita nelle situazioni seguenti:
Tabelle del database che contengono colonne definite mediante il tipo definito dall'utente.
Funzioni, stored procedure o trigger che utilizzano variabili o parametri del tipo definito dall'utente, creati nel database con la clausola WITH SCHEMABINDING.
Esempio
La seguente istruzione Transact-SQL deve essere eseguita nell'ordine indicato. È necessario eliminare prima la tabella a cui fa riferimento il tipo definito dall'utente Point, quindi il tipo e infine l'assembly.
DROP TABLE dbo.Points;
DROP TYPE dbo.Point;
DROP ASSEMBLY Point;
Ricerca di dipendenze di tipi definiti dall'utente
Se sono presenti oggetti dipendenti, ad esempio tabelle con definizioni di colonne con tipo definito dall'utente, l'istruzione DROP TYPE avrà esito negativo. L'istruzione avrà esito negativo anche se nel database sono stati creati trigger, funzioni o stored procedure mediante la clausola WITH SCHEMABINDING, e tali routine utilizzano variabili o parametri del tipo definito dall'utente. È necessario innanzitutto eliminare tutti gli oggetti dipendenti, quindi eseguire l'istruzione DROP TYPE.
Nella query Transact-SQL seguente vengono individuate tutte le colonne e tutti i parametri che utilizzano un tipo definito dall'utente nel database AdventureWorks2008R2.
USE Adventureworks2008R2;
SELECT o.name AS major_name, o.type_desc AS major_type_desc
, c.name AS minor_name, c.type_desc AS minor_type_desc
, at.assembly_class
FROM (
SELECT object_id, name, user_type_id, 'SQL_COLUMN' AS type_desc
FROM sys.columns
UNION ALL
SELECT object_id, name, user_type_id, 'SQL_PROCEDURE_PARAMETER'
FROM sys.parameters
) AS c;
JOIN sys.objects AS o
ON o.object_id = c.object_id;
JOIN sys.assembly_types AS at
ON at.user_type_id = c.user_type_id;
Gestione di tipi definiti dall'utente
Non è possibile modificare un tipo definito dall'utente una volta creato in un database di SQL Server, sebbene sia possibile modificare l'assembly sul quale si basa il tipo. Nella maggior parte dei casi, è necessario rimuovere il tipo definito dall'utente dal database con l'istruzione Transact-SQL DROP TYPE, apportare le modifiche all'assembly sottostante e ricaricarlo mediante l'istruzione ALTER ASSEMBLY. È necessario quindi ricreare il tipo definito dall'utente e tutti gli oggetti dipendenti.
Esempio
L'istruzione ALTER ASSEMBLY viene utilizzata dopo avere apportato le modifiche al codice sorgente nell'assembly UDT e averlo ricompilato. Il file con estensione dll viene copiato nel server e riassociato al nuovo assembly. Per la sintassi completa, vedere ALTER ASSEMBLY (Transact-SQL).
L'istruzione Transact-SQL ALTER ASSEMBLY seguente ricarica l'assembly Point.dll dal percorso specificato su disco.
ALTER ASSEMBLY Point
FROM '\\Projects\Point\bin\Point.dll';
Utilizzo di ALTER ASSEMBLY per aggiungere codice sorgente
La clausola ADD FILE nella sintassi ALTER ASSEMBLY non è presente in CREATE ASSEMBLY. È possibile utilizzarla per aggiungere codice sorgente o altri file associati a un assembly. I file vengono copiati dai percorsi originali e vengono archiviati nelle tabelle di sistema del database. In questo modo il codice sorgente o gli altri file saranno sempre disponibili nel caso in cui sia necessario ricreare o documentare la versione corrente del tipo definito dall'utente.
L'istruzione Transact-SQL ALTER ASSEMBLY seguente aggiunge il codice sorgente della classe Point.cs per il tipo definito dall'utente Point. Il testo contenuto nel file Point.cs viene copiato e archiviato nel database con il nome "PointSource".
ALTER ASSEMBLY Point
ADD FILE FROM '\\Projects\Point\Point.cs' AS PointSource;
Le informazioni sull'assembly vengono archiviate nella tabella sys.assembly_files del database in cui è stato installato l'assembly. La tabella sys.assembly_files contiene le colonne seguenti.
assembly_id
Identificatore definito per l'assembly. Questo numero viene assegnato a tutti gli oggetti relativi allo stesso assembly.name
Nome dell'oggetto.file_id
Numero che identifica ogni oggetto. Al primo oggetto associato a un valore assembly_id specifico viene assegnato il valore 1. Se esistono più oggetti associati allo stesso valore assembly_id, ogni valore file_id successivo verrà incrementato di 1.content
Rappresentazione esadecimale dell'assembly o del file.
È possibile utilizzare la funzione CAST o CONVERT per convertire il contenuto della colonna content in testo leggibile. Nella query seguente il contenuto del file Point.cs viene convertito in testo leggibile, utilizzando il nome nella clausola WHERE per limitare il set di risultati a una singola riga.
SELECT CAST(content AS varchar(8000))
FROM sys.assembly_files
WHERE name='PointSource';
Se i risultati vengono copiati e incollati in un editor di testo, si noterà come le interruzioni di riga e gli spazi presenti nell'originale sono stati conservati.
Gestione di assembly e di tipi definiti dall'utente
Quando si pianifica l'implementazione di tipi definiti dall'utente, considerare quali metodi sono necessari nell'assembly UDT stesso e quali metodi devono essere creati in assembly separati e implementati come stored procedure o funzioni definite dall'utente. La separazione dei metodi in assembly distinti consente di aggiornare il codice senza influire sui dati che possono essere archiviati in una colonna con tipo definito dall'utente di una tabella. È possibile modificare gli assembly UDT senza eliminare le colonne con tipo definito dall'utente e gli altri oggetti dipendenti solo quando la nuova definizione può leggere i valori precedenti e la firma del tipo non viene modificata.
Separando il codice procedurale che può cambiare dal codice necessario per implementare il tipo definito dall'utente si semplifica notevolmente la manutenzione. Includendo solo il codice necessario per l'utilizzo del tipo definito dall'utente e mantenendo le definizioni UDT il più semplice possibile, si riduce il rischio di dovere eliminare il tipo definito dall'utente dal database per revisioni del codice o correzioni di errori.
Funzione di conversione valuta e tipo definito dall'utente Currency
Il tipo definito dall'utente Currency nel database di esempio AdventureWorks2008R2 fornisce un esempio utile della modalità consigliata per strutturare un tipo definito dall'utente e le funzioni associate. Il tipo definito dall'utente Currency viene utilizzato per la gestione degli importi in base al sistema monetario di una lingua specifica e consente l'archiviazione di tipi di valuta diversi, ad esempio dollari, euro e così via. La classe UDT espone il nome della lingua come stringa e l'importo come tipo di dati decimal. Tutti i metodi di serializzazione necessari sono contenuti all'interno dell'assembly che definisce la classe. La funzione che implementa la conversione di valuta da una lingua all'altra viene implementata come funzione esterna denominata ConvertCurrency. Tale funzione risiede in un assembly separato. La funzione ConvertCurrency recupera il tasso di conversione da una tabella del database AdventureWorks2008R2. Se l'origine dei tassi di conversione dovesse cambiare o se dovessero esserci altre modifiche al codice esistente, l'assembly può essere modificato facilmente senza influire sul tipo definito dall'utente Currency.
È possibile trovare il listato di codice per il tipo definito dall'utente Currency e per le funzioni ConvertCurrency installando gli esempi di Common Language Runtime (CLR). Per ulteriori informazioni, vedere Considerazioni per l'installazione di esempi e di database di esempio di SQL Server.
Utilizzo di tipi definiti dall'utente tra database
I tipi definiti dall'utente vengono definiti nell'ambito di un singolo database. Un tipo definito dall'utente in un database non può pertanto essere utilizzato in una definizione di colonna di un altro database. Per utilizzare i tipi definiti dall'utente in più database, è necessario eseguire le istruzioni CREATE ASSEMBLY e CREATE TYPE in ogni database in assembly identici. Gli assembly sono considerati identici se hanno nome, nome sicuro, lingua, versione, set di autorizzazioni e contenuto binario identici.
Dopo avere registrato e reso accessibile il tipo definito dall'utente in entrambi database, è possibile convertire un valore UDT in un database per utilizzarlo nell'altro. I tipi definiti dall'utente identici possono essere utilizzati tra i database negli scenari seguenti:
Chiamata a una stored procedure definita in database diversi.
Esecuzione di una query su tabelle definite in database diversi.
Selezione di dati UDT da una colonna con tipo definito dall'utente della tabella di database e inserimento di tali dati in un secondo database con una colonna con tipo definito dall'utente identica.
In queste situazioni, la conversione richiesta dal server viene eseguita automaticamente. Non è possibile eseguire le conversioni in modo esplicito mediante le funzioni Transact-SQL CAST o CONVERT.
Non è necessaria alcuna azione per utilizzare i tipi definiti dall'utente quando il Motore di database di SQL Server crea le tabelle di lavoro nel database di sistema tempdb. Questo è valido anche per la gestione di cursori, variabili di tabella e funzioni valutate a livello di tabella definite dall'utente che includono i tipi definiti dall'utente e che utilizzano tempdb in modo trasparente. Tuttavia, se si crea in modo esplicito una tabella temporanea in tempdb che definisce una colonna con tipo definito dall'utente, sarà necessario registrare il tipo definito dall'utente in tempdb nello stesso modo previsto per un database utente.