Condividi tramite

Registrare i tipi definiti dall'utente in SQL Server

  • Articolo

Si applica a:SQL Server

Per usare un tipo definito dall'utente (UDT) in SQL Server, è necessario registrarlo. La registrazione di un tipo definito dall'utente (UDT) comporta la registrazione dell'assembly e la creazione del tipo nel database in cui si desidera utilizzarlo. I tipi definiti dall'utente hanno come ambito un singolo database e non possono essere usati in più database, a meno che l'assembly identico e il tipo definito dall'utente non siano registrati in ogni database. Dopo aver registrato l'assembly UDT e aver creato il tipo, è possibile usare il tipo definito dall'utente in Transact-SQL e nel codice client. Per altre informazioni, vedere tipi CLR definiti dall'utente.

Usare Visual Studio per distribuire tipi definiti dall'utente

Il modo più semplice per distribuire il tipo definito dall'utente consiste nell'usare Visual Studio. Per scenari di distribuzione più complessi e la massima flessibilità, tuttavia, usare Transact-SQL come descritto più avanti in questo articolo.

Seguire questi passaggi per creare e distribuire un tipo definito dall'utente (UDT) mediante Visual Studio:

  1. Creare un nuovo progetto di database nei nodi del linguaggio Visual Basic o Visual C# .

  2. Aggiungere un riferimento al database di SQL Server che conterrà il tipo definito dall'utente.

  3. Aggiungere una classe di tipo definito dall'utente.

  4. Scrivere il codice per implementare il tipo definito dall'utente (UDT).

  5. Scegliere Distribuisci dal menu Compila. In questo modo viene registrato l'assembly e viene creato il tipo nel database di SQL Server.

Usare Transact-SQL per distribuire tipi definiti dall'utente

La sintassi Transact-SQL CREATE ASSEMBLY viene usata per registrare l'assembly nel database in cui si desidera utilizzare il tipo definito dall'utente. Viene archiviato internamente nelle tabelle di sistema del database, non esternamente nel file system. Se il tipo definito dall'utente (UDT) dipende dagli assembly esterni, questi dovranno essere caricati nel database. L'istruzione CREATE TYPE viene usata per creare il tipo definito dall'utente nel database in cui deve essere usato. Per altre informazioni, vedere CREATE ASSEMBLY e CREATE TYPE.

Usare l'assembly di creazione

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.

La creazione di più versioni dello stesso assembly in un determinato database non è consentita. Tuttavia, è possibile creare più versioni dello stesso assembly in base alle impostazioni cultura in un determinato database. SQL Server distingue più versioni delle impostazioni cultura di un assembly in base a nomi diversi registrati nell'istanza di SQL Server. Per altre informazioni, vedere Creare e usare assembly con nome sicuro.

Quando CREATE ASSEMBLY viene eseguito con i set di autorizzazioni SAFE o EXTERNAL_ACCESS, l'assembly viene controllato per assicurarsi che sia verificabile e indipendente dal tipo. Se si omette di specificare un set di autorizzazioni, si presuppone SAFE. Il codice con il set di autorizzazioni UNSAFE non è selezionato. Per altre informazioni sui set di autorizzazioni di assembly, vedere Progettare assembly.

Esempio

L'istruzione Transact-SQL seguente registra l'assembly Point in SQL Server nel database AdventureWorks2022, 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 AdventureWorks2022;

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

Nell'istruzione Transact-SQL seguente l'assembly viene registrato utilizzando <assembly_bits> argomento nella clausola FROM. Questo valore varbinary rappresenta il file come flusso di byte.

USE AdventureWorks2022;
CREATE ASSEMBLY Point
FROM 0xfeac4 ... 21ac78

Usare il tipo di creazione

Dopo aver caricato l'assembly nel database, è possibile creare il tipo usando 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 viene visualizzato un errore.

Nota

La sintassi CREATE TYPE viene usata anche per la creazione di tipi di dati alias di SQL Server nativi ed è destinata a sostituire sp_addtype come mezzo per la creazione di tipi di dati alias. Alcuni degli argomenti facoltativi nella sintassi CREATE TYPE fanno riferimento alla creazione di tipi definiti dall'utente e non sono applicabili alla creazione di tipi di dati alias (ad esempio il tipo di base).

Per altre informazioni, vedere CREATE TYPE.

Esempio

L'istruzione Transact-SQL seguente crea il tipo di Point. Il EXTERNAL NAME viene specificato utilizzando la sintassi di denominazione in due parti di <assembly_name>.<udt_name>.

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

Rimuovere un tipo definito dall'utente dal database

L'istruzione DROP TYPE rimuove un tipo definito dall'utente dal database corrente. Una volta eliminato un tipo definito dall'utente, è possibile usare 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 (UDT).

  • Funzioni, stored procedure o trigger che usano variabili o parametri del tipo definito dall'utente, creati nel database con la clausola WITH SCHEMABINDING.

Esempio

L'istruzione Transact-SQL seguente deve essere eseguita nell'ordine seguente. Prima di tutto la tabella che fa riferimento alla Point tipo definito dall'utente deve essere eliminata, quindi il tipo e infine l'assembly.

DROP TABLE dbo.Points;
DROP TYPE dbo.Point;
DROP ASSEMBLY Point;

Trovare le dipendenze del tipo definito dall'utente

Se sono presenti oggetti dipendenti, ad esempio tabelle con definizioni di colonna UDT, l'istruzione DROP TYPE ha esito negativo. Ha esito negativo anche se sono presenti funzioni, stored procedure o trigger creati nel database usando la clausola WITH SCHEMABINDING, se queste routine usano variabili o parametri del tipo definito dall'utente. È innanzitutto necessario eliminare tutti gli oggetti dipendenti e quindi eseguire l'istruzione DROP TYPE.

La query di Transact-SQL seguente individua tutte le colonne e i parametri che usano un tipo definito dall'utente nel database AdventureWorks2022.

USE AdventureWorks2022;

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
     INNER JOIN sys.objects AS o
         ON o.object_id = c.object_id
     INNER JOIN sys.assembly_types AS at
         ON at.user_type_id = c.user_type_id;

Gestire i tipi definiti dall'utente

Non è possibile modificare un tipo definito dall'utente dopo la creazione in un database di SQL Server, anche se è possibile modificare l'assembly su cui 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 modifiche all'assembly sottostante e ricaricarlo usando l'istruzione ALTER ASSEMBLY. È necessario quindi ricreare il tipo definito dall'utente (UDT) e tutti gli oggetti dipendenti.

Esempio

L'istruzione ALTER ASSEMBLY viene usata dopo aver apportato modifiche al codice sorgente nell'assembly UDT e ricompilarla. Il file con estensione dll viene copiato nel server e riassociato al nuovo assembly. Per la sintassi completa, vedere ALTER ASSEMBLY.

L'istruzione Transact-SQL ALTER ASSEMBLY seguente ricarica l'assembly Point.dll dal percorso specificato sul disco.

ALTER ASSEMBLY Point
    FROM '\\Projects\Point\bin\Point.dll';

Usare alter assembly per aggiungere il 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 (UDT).

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

Colonna Descrizione
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, con il primo oggetto associato a un determinato assembly_id assegnato il valore di 1. Se sono presenti più oggetti associati allo stesso assembly_id, ogni successivo file_id valore incrementa di 1.
content Rappresentazione esadecimale dell'assembly o del file.

È possibile usare la funzione CAST o CONVERT per convertire il contenuto della colonna content in testo leggibile. La query seguente converte il contenuto del file Point.cs in testo leggibile, usando 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 si copiano e incollano i risultati in un editor di testo, si noterà che le interruzioni di riga e gli spazi esistenti nell'originale vengono mantenuti.

Gestire tipi definiti dall'utente e assembly

Quando si pianifica l'implementazione di tipi definiti dall'utente (UDT), considerare quali metodi sono necessari nell'assembly del tipo definito dall'utente (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 separati consente di aggiornare il codice senza influire sui dati che potrebbero essere archiviati in una colonna definito dall'utente in una tabella. È possibile modificare gli assembly definiti dall'utente senza eliminare colonne definite dall'utente e altri oggetti dipendenti solo quando la nuova definizione può leggere i valori precedenti e la firma del tipo non cambia.

La separazione del codice procedurale che potrebbe cambiare dal codice necessario per implementare il tipo definito dall'utente semplifica notevolmente la manutenzione. Includendo solo il codice necessario per il funzionamento del tipo definito dall'utente e mantenendo le definizioni definite dall'utente il più semplice possibile, riduce il rischio che il tipo definito dall'utente stesso debba essere eliminato dal database per le revisioni del codice o le correzioni di bug.

Funzione di conversione valuta definita dall'utente e valuta

Il Currency tipo definito dall'utente nel database di esempio AdventureWorks2022 fornisce un esempio utile del modo consigliato per strutturare un tipo definito dall'utente e le relative funzioni associate. Il Currency definito dall'utente viene usato per gestire i soldi in base al sistema monetario di una determinata cultura e consente l'archiviazione di tipi di valuta diversi, ad esempio dollari, euro e così via. La classe UDT espone un nome delle impostazioni cultura come stringa e una quantità di denaro come tipo di dati decimale . 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 impostazioni cultura a un'altra viene implementata come funzione esterna denominata ConvertCurrencye questa funzione si trova in un assembly separato. La funzione ConvertCurrency esegue il lavoro recuperando la frequenza di conversione da una tabella nel database AdventureWorks2022. Se l'origine dei tassi di conversione deve cambiare o se devono essere presenti altre modifiche al codice esistente, l'assembly può essere facilmente modificato senza influire sul Currency tipo definito dall'utente.

L'elenco di codice per le funzioni definite dall'utente di Currency e ConvertCurrency è reperibile installando gli esempi clr (Common Language Runtime).

Usare tipi definiti dall'utente tra database

I tipi definiti dall'utente (UDT) vengono definiti nell'ambito di un singolo database. Pertanto, un tipo definito dall'utente definito in un database non può essere usato in una definizione di colonna in un altro database. Per usare 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 (UDT) in entrambi database, è possibile convertire un valore UDT in un database per utilizzarlo nell'altro. I tipi definiti dall'utente (UDT) 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 del tipo definito dall'utente (UDT) da una colonna con tipo definito dall'utente (UDT) della tabella di database e inserimento di tali dati in un secondo database con una colonna con tipo definito dall'utente (UDT) identica.

In queste situazioni, la conversione richiesta dal server viene eseguita automaticamente. Non è possibile eseguire le conversioni in modo esplicito usando le funzioni di Transact-SQL CAST o CONVERT.

Non è necessario eseguire alcuna azione per l'uso di tipi definiti dall'utente quando il motore di database di SQL Server crea tabelle di lavoro nel database di sistema tempdb. Ciò include la gestione di cursori, variabili di tabella e funzioni con valori di tabella definiti dall'utente che includono tipi definiti dall'utente e che usano in modo trasparente tempdb. Tuttavia, se si crea in modo esplicito una tabella temporanea in tempdb che definisce una colonna definito dall'utente, il tipo definito dall'utente deve essere registrato in tempdb allo stesso modo di un database utente.

Contenuto correlato