Registrazione dei tipi definiti dall'utente in SQL Server

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

USE AdventureWorks;
CREATE ASSEMBLY Point
FROM '\\ShareName\Projects\Point\bin\Point.dll' 
WITH PERMISSION_SET = SAFE;

USE AdventureWorks;
CREATE ASSEMBLY Point
FROM 0xfeac4 … 21ac78

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.

Per ulteriori informazioni, vedere CREATE TYPE (Transact-SQL).

Esempio

CREATE TYPE dbo.Point 
EXTERNAL NAME Point.[Point];

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;

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 AdventureWorks.

USE Adventureworks;
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;

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'

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.

Il tipo definito dall'utente Currency nel database di esempio AdventureWorks 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 AdventureWorks. 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.

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.