Partager via

  • Article

Implémentation de types définis par l'utilisateur

Cette rubrique explique comment créer et supprimer des types définis par l'utilisateur CLR (Common Language Runtime) dans SQL Server.

Création de types définis par l'utilisateur

Pour créer un type défini par l'utilisateur dans SQL Server, exécutez la procédure suivante :

  • Définissez le type défini par l'utilisateur en tant que classe ou structure dans un langage pris en charge par Microsoft.NET Framework. Pour plus d'informations sur la programmation de types dans le CLR, consultez Types CLR définis par l'utilisateur. Compilez ensuite la classe ou la structure afin de créer un assembly dans le .NET Framework à l'aide du compilateur de langage adéquat.

  • Inscrivez l'assembly dans SQL Server à l'aide de l'instruction CREATE ASSEMBLY. Pour plus d'informations sur les assemblys dans SQL Server, consultez Assemblys (moteur de base de données).

  • Créez le type qui fait référence à l'assembly inscrit.

[!REMARQUE]

Le déploiement d'un projet SQL Server dans MicrosoftVisual Studio enregistre un assembly dans la base de données spécifiée pour le projet. Le déploiement du projet crée aussi les types CLR définis par l'utilisateur dans la base de données pour toutes les définitions de classe annotées par l'attribut SqlUserDefinedType. Pour plus d'informations, consultez Déploiement d'objets de base de données CLR.

[!REMARQUE]

Par défaut, la capacité de SQL Server à exécuter du code CLR est définie à la valeur OFF. Vous pouvez créer, modifier ou supprimer des objets de base de données qui référencent des modules de code managé, mais ces références ne seront pas exécutées dans SQL Server à moins que l'option clr enabled ne soit activée en utilisant sp_configure.

Pour créer, modifier ou supprimer un assembly

Pour créer un type défini par l'utilisateur

Suppression de types définis par l'utilisateur

Pour supprimer un type défini par l'utilisateur

[!REMARQUE]

Les types définis par l'utilisateur ne peuvent pas être modifiés après leur création, car toute modification rendrait les données des tables ou des index non valides. Pour modifier un type, vous devez soit le supprimer puis le recréer, soit émettre une instruction ALTER ASSEMBLY à l'aide de la clause WITH UNCHECKED DATA.

Vous ne pouvez pas supprimer un type défini par l'utilisateur tant que toutes les références à ce type n'ont pas été supprimées. Elles incluent notamment :

  • les colonnes définies sur ce type ;

  • les colonnes calculées et les contraintes CHECK dont les expressions font référence au type ;

  • les vues et les fonctions liées au schéma et dont les définitions contiennent des expressions qui font référence au type ;

  • les paramètres des fonctions et des procédures stockées.

Pour rechercher des colonnes dépendant d'un type d'utilisateur défini

L'exemple suivant extrait des métadonnées sur les colonnes définies sur le type défini par l'utilisateur ComplexNumber.

SELECT * FROM sys.columns 
WHERE user_type_id = TYPE_ID('ComplexNumber');

L'exemple suivant extrait des métadonnées limitées pour les utilisateurs détenant des privilèges minimaux sur les colonnes définies sur le type défini par l'utilisateur ComplexNumber.

SELECT * FROM sys.column_type_usages 
WHERE user_type_id = TYPE_ID('ComplexNumber');

Pour rechercher des expressions de colonne calculée, des expressions de contrainte CHECK et des expressions de module dépendant d'un type défini par l'utilisateur

L'exemple suivant extrait les noms des colonnes calculées (et leurs tables) dépendant d'un type défini par l'utilisateur SimpleUdt.

SELECT OBJECT_SCHEMA_NAME (referencing_id) AS referencing_schema_name,
    OBJECT_NAME(referencing_id) AS referencing_name,
    COL_NAME(referencing_id, referencing_minor_id) AS column_name,
    is_caller_dependent,
    is_ambiguous
FROM sys.sql_expression_dependencies
WHERE referenced_id = TYPE_ID('SimpleUdt')
    AND referenced_class = 6 
    AND OBJECTPROPERTY(referencing_id, 'IsTable')=1;

L'exemple suivant extrait les noms des contraintes CHECK (et les objets sur lesquels ils sont définis) dépendant d'un type défini par l'utilisateur SimpleUdt.

SELECT SCHEMA_NAME(o.schema_id) AS schema_name,
    OBJECT_NAME(o.parent_object_id) AS table_name,
    OBJECT_NAME(o.object_id) AS constraint_name
FROM sys.sql_expression_dependencies AS d
JOIN sys.objects AS o
ON o.object_id = d.referencing_id
WHERE referenced_id = TYPE_ID('SimpleUdt')
AND referenced_class = 6 
AND OBJECTPROPERTY(o.object_id, 'IsCheckCnst')=1;

L'exemple suivant extrait les noms des modules dont les définitions font référence au type défini par l'utilisateur SimpleUdt.

USE AdventureWorks;
GO
SELECT referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('SimpleUdt', 'TYPE');
GO

Pour rechercher des paramètres dépendant d'un type défini par l'utilisateur

L'exemple suivant extrait les noms des paramètres (et les objets auxquels ils appartiennent) définis sur un type défini par l'utilisateur ComplexNumber.

SELECT OBJECT_NAME(object_id) AS object_name,
    NULL AS procedure_number,
    name AS param_name,
    parameter_id AS param_num,
    TYPE_NAME(p.user_type_id) AS type_name
FROM sys.parameters AS p
WHERE p.user_type_id = TYPE_ID('ComplexNumber')
ORDER BY object_name, procedure_number, param_num;

L'exemple suivant extrait des métadonnées limitées pour les utilisateurs détenant des privilèges minimaux sur les paramètres définis sur le type défini par l'utilisateur ComplexNumber.

SELECT * FROM sys.parameter_type_usages 
WHERE user_type_id = TYPE_ID('ComplexNumber');