Inscription des types définis par l'utilisateur dans SQL Server

  • Article

S’applique à :SQL Server

Pour utiliser un type défini par l’utilisateur (UDT) dans Microsoft SQL Server, vous devez l’inscrire. L'inscription d'un UDT comprend l'inscription de l'assembly et la création du type dans la base de données dans laquelle vous souhaitez l'utiliser. La portée des UDT se limite à une seule base de données. Ils ne peuvent pas être utilisés dans plusieurs bases de données à moins d'inscrire le même assembly et UDT dans chaque base de données. Une fois l’assembly UDT inscrit et le type créé, vous pouvez utiliser l’UDT dans Transact-SQL et dans le code client. Pour plus d’informations, consultez Types CLR définis par l’utilisateur.

Utilisation de Visual Studio pour déployer des UDT

Le moyen le plus simple de déployer votre UDT consiste à utiliser Microsoft Visual Studio. Toutefois, pour les scénarios de déploiement plus complexes et la plus grande flexibilité, utilisez Transact-SQL, comme indiqué plus loin dans cette rubrique.

Procédez comme suit pour créer et déployer un UDT à l'aide de Visual Studio :

  1. Créez un projet de base de données dans les nœuds de langage Visual Basic ou Visual C# .

  2. Ajoutez une référence à la base de données SQL Server qui contiendra le type défini par l’utilisateur.

  3. Ajoutez une classe Type défini par l’utilisateur .

  4. Écrivez le code d'implémentation de l'UDT.

  5. Dans le menu Générer , sélectionnez Déployer. Cela inscrit l’assembly et crée le type dans la base de données SQL Server.

Utilisation de Transact-SQL pour déployer des UDT

La syntaxe Transact-SQL CREATE ASSEMBLY est utilisée pour inscrire l’assembly dans la base de données dans laquelle vous souhaitez utiliser l’UDT. L'assembly est stocké en interne dans les tables système de la base de données, et non en externe dans le système de fichiers. Si l'UDT est dépendant d'assemblys externes, ces derniers doivent également être chargés dans la base de données. L'instruction CREATE TYPE est utilisée pour créer l'UDT dans la base de données dans laquelle il sera utilisé. Pour plus d’informations, consultez CREATE ASSEMBLY (Transact-SQL) et CREATE TYPE (Transact-SQL).

Utilisation de CREATE ASSEMBLY

Le syntaxe CREATE ASSEMBLY permet d'inscrire l'assembly dans la base de données dans laquelle vous souhaitez utiliser l'UDT. Une fois l'assembly inscrit, il n'a plus de dépendances.

La création de plusieurs versions du même assembly dans une même base de données n'est pas autorisée. Toutefois, il est possible de créer plusieurs versions du même assembly dans une même base de données en fonction de la culture. SQL Server distingue plusieurs versions de culture d’un assembly par différents noms inscrits dans le instance de SQL Server. Pour plus d'informations, consultez la section relative à la création et à l'utilisation d'assemblys avec nom fort dans le Kit de développement logiciel du .NET Framework.

Lorsque CREATE ASSEMBLY est exécuté avec le jeu d'autorisations SAFE ou EXTERNAL_ACCESS, l'assembly est vérifié pour s'assurer qu'il est vérifiable et sécurisé. Si vous omettez de spécifier un jeu d'autorisations, le jeu SAFE est utilisé. Le code associé au jeu d'autorisations UNSAFE n'est pas vérifié. Pour plus d’informations sur les jeux d’autorisations des assemblys, consultez Conception d’assemblys.

Exemple

L’instruction Transact-SQL suivante inscrit l’assembly Point dans SQL Server dans la base de données AdventureWorks, avec le jeu d’autorisations SAFE. Si la clause WITH PERMISSION_SET est omise, l'assembly est inscrit avec le jeu d'autorisations SAFE.

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

L’instruction Transact-SQL suivante inscrit l’assembly à l’aide <de assembly_bits> argument dans la clause FROM. Cette valeur varbinary représente le fichier sous la forme d’un flux d’octets.

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

Utilisation de CREATE TYPE

Une fois l’assembly chargé dans la base de données, vous pouvez créer le type à l’aide de l’instruction Transact-SQL CREATE TYPE. Le type est alors ajouté à la liste des types disponibles pour cette base de données. La portée du type se limite à la base de données ; il ne peut être utilisé que dans la base de données dans laquelle il a été créé. Si l'UDT existe déjà dans la base de données, l'instruction CREATE TYPE échoue avec une erreur.

Notes

La syntaxe CREATE TYPE est également utilisée pour créer des types de données d’alias SQL Server natifs et est destinée à remplacer sp_addtype comme moyen de créer des types de données d’alias. Certains arguments facultatifs de la syntaxe CREATE TYPE se rapportent à la création d'UDT et ne peuvent pas être appliqués à la création de types de données d'alias.

Pour plus d’informations, consultez CREATE TYPE (Transact-SQL).

Exemple

L’instruction Transact-SQL suivante crée le type Point . External NAME est spécifié à l’aide de la syntaxe de nommage en deux parties de AssemblyName. UDTName.

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

Suppression d'un UDT de la base de données

L'instruction DROP TYPE supprime un UDT de la base de données active. Une fois un UDT supprimé, vous pouvez utiliser l'instruction DROP ASSEMBLY pour supprimer l'assembly de la base de données.

L'instruction DROP TYPE ne s'exécute pas dans les situations suivantes :

  • Des tables de la base de données contiennent des colonnes définies à l'aide de l'UDT.

  • Des fonctions, procédures stockées ou déclencheurs créés dans la base de données avec la clause WITH SCHEMABINDING utilisent des variables ou des paramètres de l'UDT.

Exemple

Le transact-SQL suivant doit s’exécuter dans l’ordre suivant. Tout d’abord, la table qui fait référence à l’UDT point doit être supprimée, puis le type et enfin l’assembly.

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

Recherche des dépendances d'un UDT

En présence d'objets dépendants, comme des tables avec des définitions de colonne UDT, l'instruction DROP TYPE échoue. Elle échoue également si des fonctions, procédures stockées ou déclencheurs créés dans la base de données à l'aide de la clause WITH SCHEMABINDING utilisent des variables ou des paramètres du type défini par l'utilisateur. Vous devez commencer par supprimer tous les objets dépendants, puis exécuter l'instruction DROP TYPE.

La requête Transact-SQL suivante localise toutes les colonnes et paramètres qui utilisent un type défini par l’utilisateur dans la base de données 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;

Maintenance des UDT

Vous ne pouvez pas modifier un type défini par l’utilisateur une fois qu’il est créé dans une base de données SQL Server, bien que vous puissiez modifier l’assembly sur lequel le type est basé. Dans la plupart des cas, vous devez supprimer l’UDT de la base de données avec l’instruction Transact-SQL DROP TYPE, apporter des modifications à l’assembly sous-jacent et le recharger à l’aide de l’instruction ALTER ASSEMBLY. Vous devez ensuite recréer l'UDT et tout objet dépendant.

Exemple

L'instruction ALTER ASSEMBLY est utilisée une fois que vous avez modifié le code source dans votre assembly UDT et l'avez recompilé. Elle copie le fichier .dll sur le serveur et le lie au nouvel assembly. Pour obtenir la syntaxe complète, consultez ALTER ASSEMBLY (Transact-SQL).

L’instruction Transact-SQL ALTER ASSEMBLY suivante recharge l’assembly Point.dll à partir de l’emplacement spécifié sur le disque.

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

Utilisation de l'instruction ALTER ASSEMBLY pour ajouter le code source

La clause ADD FILE de la syntaxe ALTER ASSEMBLY n'est pas présente dans CREATE ASSEMBLY. Vous pouvez l'utiliser pour ajouter le code source ou tout autre fichier associé à un assembly. Les fichiers sont copiés depuis leur emplacement d'origine et stockés dans les tables système de la base de données. Le code source et autres fichiers est toujours à portée de main dans l'éventualité où vous deviez recréer ou documenter la version actuelle de l'UDT.

L’instruction Transact-SQL ALTER ASSEMBLY suivante ajoute le code source de la classe Point.cs pour l’UDT Point . Le texte contenu dans le fichier Point.cs est alors copié et stocké dans la base de données sous le nom PointSource.

ALTER ASSEMBLY Point  
ADD FILE FROM '\\Projects\Point\Point.cs' AS PointSource;

Les informations d’assembly sont stockées dans la table sys.assembly_files de la base de données où l’assembly a été installé. Le tableau sys.assembly_files contient les colonnes suivantes.

assembly_id
Identificateur défini pour l'assembly. Ce numéro est affecté à tous les objets se rapportant au même assembly.

name
Nom de l'objet.

file_id
Nombre identifiant chaque objet, le premier objet associé à un assembly_id donné ayant la valeur 1. Si plusieurs objets sont associés au même assembly_id, chaque valeur de file_id suivante est incrémentée de 1.

content
Représentation hexadécimale de l'assembly ou du fichier.

Vous pouvez utiliser la fonction CAST ou CONVERT pour convertir le contenu de la colonne de contenu en texte lisible. La requête suivante convertit le contenu du fichier Point.cs en texte lisible, en utilisant le nom indiqué dans la clause WHERE pour limiter le jeu de résultats à une ligne unique.

SELECT CAST(content AS varchar(8000))   
  FROM sys.assembly_files   
  WHERE name='PointSource';

Si vous copiez et collez les résultats dans un éditeur de texte, vous pouvez constater que les sauts de ligne et les espaces qui existaient dans l'original ont été conservés.

Gestion des UDT et des assemblys

Lors de la planification de votre implémentation d'UDT, identifiez les méthodes qui sont nécessaires dans l'assembly lui-même de l'UDT, ainsi que celles qui doivent être créées dans des assemblys distincts et implémentées en tant que fonctions définies par l'utilisateur ou procédures stockées. Le fait de séparer les méthodes dans des assemblys distincts vous permet de mettre à jour le code sans affecter les données qui peuvent être stockées dans une colonne UDT d'une table. Vous pouvez modifier les assemblys d'UDT sans supprimer de colonnes UDT et autres objets dépendants uniquement lorsque la nouvelle définition peut lire les valeurs précédentes et que la signature du type ne change pas.

Le fait de séparer le code de procédure susceptible de changer du code requis pour implémenter l'UDT simplifie considérablement la maintenance. Par ailleurs, le fait de n'inclure que le code qui est nécessaire au fonctionnement de l'UDT et de créer des définitions d'UDT aussi simples que possible réduit le risque que l'UDT lui-même ait besoin d'être supprimé de la base de données pour les révisions de code ou la résolution de bogues.

UDT Currency et fonction de conversion de devise

L’UDT Currency dans l’exemple de base de données AdventureWorks fournit un exemple utile de la méthode recommandée pour structurer un type défini par l’utilisateur et ses fonctions associées. L’UUDT monétaire est utilisé pour gérer l’argent en fonction du système monétaire d’une culture particulière, et permet de stockage de différents types de devises, tels que les dollars, les euros, etc. La classe UDT expose un nom de culture sous forme de chaîne et un montant d’argent sous forme de type de données décimal . Toutes les méthodes de sérialisation nécessaires sont contenues dans l'assembly qui définit la classe. La fonction qui implémente la conversion monétaire d’une culture à une autre est implémentée en tant que fonction externe nommée ConvertCurrency, et cette fonction se trouve dans un assembly distinct. La fonction ConvertCurrency effectue son travail en récupérant le taux de conversion à partir d’une table de la base de données AdventureWorks . Si la source des taux de conversion doit changer, ou si d’autres modifications doivent être apportées au code existant, l’assembly peut être facilement modifié sans affecter l’UDT monétaire .

Vous trouverez la liste de code pour les fonctions UDT currency et ConvertCurrency en installant les exemples CLR (Common Language Runtime).

Utilisation d'UDT dans plusieurs bases de données

Par définition, la portée des UDT se limite à une seule base de données. Autrement dit, un UDT défini dans une base de données ne peut pas être utilisé dans une définition de colonne d'une autre base de données. Pour utiliser des UDT dans plusieurs bases de données, vous devez exécuter les instructions CREATE ASSEMBLY et CREATE TYPE dans chaque base de données sur des assemblys identiques. Les assemblys sont considérés comme identiques s'ils partagent les mêmes nom, nom fort, culture, version, jeu d'autorisations et contenu binaire.

Une fois l'UDT inscrit et accessible dans les deux bases de données, vous pouvez convertir une valeur UDT d'une base de données en vue de l'utiliser dans une autre. Des UDT identiques peuvent être utilisés dans plusieurs bases de données dans les scénarios suivants :

  • appel de procédures stockées définies dans des base de données différentes ;

  • interrogation de tables définies dans des bases de données différentes ;

  • sélection de données UDT dans une colonne UDT de table de base de données et insertion dans une seconde base de données avec une colonne UDT identique.

Dans ces situations, toute conversion requise par le serveur se produit automatiquement. Vous ne pouvez pas effectuer les conversions explicitement à l’aide des fonctions Transact-SQL CAST ou CONVERT.

Notez que vous n’avez pas besoin d’effectuer d’action pour utiliser des types définis par l’utilisateur lorsque SQL Server moteur de base de données crée des tables de travail dans la base de données système tempdb. Cela inclut la gestion des curseurs, des variables de table et des fonctions table définies par l’utilisateur qui incluent des UDT et qui utilisent en toute transparence tempdb. Toutefois, si vous créez explicitement une table temporaire dans tempdb qui définit une colonne UDT, l’UDT doit être inscrit dans tempdb de la même façon que pour une base de données utilisateur.

Voir aussi

Types CLR définis par l’utilisateur