Partager via

Inscrire 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 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. Les UDT sont limités à une base de données unique et ne peuvent pas être utilisés dans plusieurs bases de données, sauf si l’assembly identique et l’UDT sont inscrits auprès de 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.

Utiliser Visual Studio pour déployer des UDT

Le moyen le plus simple de déployer votre UDT consiste à utiliser Visual Studio. Pour les scénarios de déploiement plus complexes et la plus grande flexibilité, utilisez toutefois Transact-SQL comme indiqué plus loin dans cet article.

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 l’UDT.

  3. Ajoutez une classe de 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.

Utiliser 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. Il est stocké en interne dans les tables système de 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 elle doit être utilisée. Pour plus d’informations, consultez CREATE ASSEMBLY et CREATE TYPE.

Utiliser créer un assembly

La syntaxe CREATE ASSEMBLY inscrit 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 base de données donnée n’est pas autorisée. Toutefois, il est possible de créer plusieurs versions du même assembly en fonction de la culture dans une base de données donnée. SQL Server distingue plusieurs versions de culture d’un assembly par différents noms inscrits dans l’instance de SQL Server. Pour plus d’informations, consultez Créer et utiliser des assemblys avec nom fort.

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

Exemple

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

USE AdventureWorks2022;

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

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

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

Utiliser le type de création

Une fois l’assembly chargé dans la base de données, vous pouvez ensuite 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.

Remarque

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 alias. Certains des arguments facultatifs de la syntaxe CREATE TYPE font référence à la création d’UDTs et ne s’appliquent pas à la création de types de données alias (par exemple, le type de base).

Pour plus d’informations, consultez CREATE TYPE.

Exemple

L’instruction Transact-SQL suivante crée le type Point. La EXTERNAL NAME est spécifiée à l’aide de la syntaxe de nommage en deux parties de <assembly_name>.<udt_name>.

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

Supprimer un UDT de la base de données

L’instruction DROP TYPE supprime un UDT de la base de données active. Une fois qu’un UDT est 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.

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

Exemple

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;

Rechercher des dépendances UDT

S’il existe des objets dépendants, tels que des tables avec des définitions de colonnes UDT, l’instruction DROP TYPE échoue. Il échoue également s’il existe des fonctions, des procédures stockées ou des déclencheurs créés dans la base de données à l’aide de la clause WITH SCHEMABINDING, si ces routines utilisent des variables ou des paramètres du type défini par l’utilisateur. Vous devez d’abord supprimer tous les objets dépendants, puis exécuter l’instruction DROP TYPE.

La requête Transact-SQL suivante recherche toutes les colonnes et paramètres qui utilisent un UDT dans la base de données 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;

Gérer les UDT

Vous ne pouvez pas modifier un UDT une fois qu’il a été 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 après avoir apporté des modifications au code source dans votre assembly UDT et recompilée. Elle copie le fichier .dll sur le serveur et le lie au nouvel assembly. Pour obtenir la syntaxe complète, consultez ALTER ASSEMBLY.

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';

Utiliser alter assembly pour ajouter du code source

La clause ADD FILE dans 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. Copie le texte contenu dans le fichier Point.cs et le stocke 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 dans la base de données où l’assembly a été installé. Le tableau sys.assembly_files contient les colonnes suivantes.

Colonne Description
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, avec le premier objet associé à une assembly_id donnée étant donné la valeur de 1. S’il existe plusieurs objets associés au même assembly_id, chaque valeur de file_id suivante est incrémentée par 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 content en texte lisible. La requête suivante convertit le contenu du fichier Point.cs en texte lisible, en utilisant le nom dans la clause WHERE pour restreindre le jeu de résultats à une seule ligne.

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 voyez que les sauts de ligne et les espaces qui existaient dans l’original sont conservés.

Gérer les UDT et les 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. La séparation des méthodes en assemblys distincts vous permet de mettre à jour le code sans affecter les données qui peuvent être stockées dans une colonne UDT dans une table. Vous pouvez modifier des assemblys UDT sans supprimer des colonnes UDT et d’autres objets dépendants uniquement lorsque la nouvelle définition peut lire les anciennes valeurs et que la signature du type ne change pas.

La séparation du code procédural susceptible de changer du code requis pour implémenter l’UDT simplifie considérablement la maintenance. L’inclusion uniquement du code nécessaire à l’UDT pour fonctionner et la conservation de vos définitions UDT aussi simples que possible, réduit le risque que l’UDT lui-même puisse être supprimé de la base de données pour les révisions de code ou les correctifs de bogues.

Fonction de conversion monétaire et UDT monétaire

L’UDT Currency dans l’exemple de base de données AdventureWorks2022 fournit un exemple utile de la méthode recommandée pour structurer un UDT et ses fonctions associées. Le Currency l’UDT est utilisé pour gérer l’argent en fonction du système monétaire d’une culture particulière, et permet le 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 une somme d’argent en tant que 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 vers 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 d’une table dans la base de données AdventureWorks2022. Si la source des taux de conversion doit jamais changer ou s’il doit y avoir d’autres modifications apportées au code existant, l’assembly peut être facilement modifié sans affecter l’UDT Currency.

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

Utiliser des UDT entre les bases de données

Par définition, la portée des UDT se limite à une seule base de données. Par conséquent, un UDT défini dans une base de données ne peut pas être utilisé dans une définition de colonne dans 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.

Vous n’avez pas besoin d’effectuer d’action pour utiliser des UDT lorsque le moteur de base de données SQL Server 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 de manière transparente 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.

Contenu connexe