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

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 les différentes versions culturelles d'un assembly par des noms différents qui sont inscrits dans l'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

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

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

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.

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

Exemple

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

L'instruction Transact-SQL suivante doit s'exécuter dans l'ordre suivant. La table qui référence l'UDT Point doit être supprimée en premier, suivie du type et enfin de l'assembly.

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

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 tous les colonnes et paramètres qui utilisent un UDT dans la base de données 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;

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 connaître 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';

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 relatives à l'assembly sont stockées dans la table sys.assembly_files dans la base de données où l'assembly a été installé. La table 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
  Numéro identifiant chaque objet (le premier objet associé à un assembly_id ayant la valeur 1). Si plusieurs objets sont associés au même assembly_id, chaque valeur 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 content 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.

L'UDT Currency de l'exemple de base de données AdventureWorks2008R2 illustre un exemple de méthode recommandée pour structurer un UDT et ses fonctions associées. L'UDT Currency est utilisé pour traiter les données monétaires reposant sur le système monétaire d'une culture particulière et permet de stocker différents types de devises, comme les dollars ou les euros. La classe de l'UDT expose un nom de culture sous forme de chaîne, et une somme d'argent sous forme de type de données decimal. 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 de devise entre une culture et une autre est implémentée en tant que fonction externe nommée ConvertCurrency. Cette fonction se trouve dans un assembly distinct. La fonction ConvertCurrency extrait le taux de conversion d'une table de la base de données AdventureWorks2008R2. Si la source des taux de conversion est amenée à être modifiée ou si le code est susceptible d'être modifié de quelque autre manière, l'assembly peut facilement être modifié sans affecter l'UDT Currency.

Le code de l'UDT Currency et des fonctions ConvertCurrency peut être consulté en installant les exemples de CLR (Common Language Runtime). Pour plus d'informations, consultez Considérations relatives à l'installation d'exemples de bases de données et d'exemples de code SQL Server.

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 explicitement ces conversions à l'aide des fonctions Transact-SQL CAST ou CONVERT.

Notez qu'aucune mesure n'est nécessaire 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 tempdb de manière transparente. 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 manière que pour une base de données utilisateur.