Compartir a través de

Registro de tipos definidos por el usuario en SQL Server

Se aplica a:SQL Server

Para usar un tipo definido por el usuario (UDT) en SQL Server, debe registrarlo. El registro de un UDT implica el registro del ensamblado y la creación del tipo en la base de datos en la que desea usarlo. Los UDT se limitan a una base de datos única y no se pueden usar en varias bases de datos a menos que el ensamblado idéntico y el UDT estén registrados con cada base de datos. Una vez registrado el ensamblado UDT y el tipo creado, puede usar el UDT en Transact-SQL y en el código de cliente. Para obtener más información, consulte tipos definidos por el usuario clR.

Uso de Visual Studio para implementar UDT

La manera más fácil de implementar el UDT es mediante Visual Studio. Sin embargo, para escenarios de implementación más complejos y la mayor flexibilidad, use Transact-SQL como se describe más adelante en este artículo.

Siga estos pasos para crear e implementar un UDT mediante Visual Studio:

  1. Cree un proyecto de base de datos en los nodos del lenguaje Visual Basic o Visual C# .

  2. Agregue una referencia a la base de datos de SQL Server que contendrá el UDT.

  3. Agregue una clase type definida por el usuario.

  4. Escriba código para implementar el UDT.

  5. En el menú Compilar , seleccione Implementar. Esto registra el ensamblado y crea el tipo en la base de datos de SQL Server.

Uso de Transact-SQL para implementar UDT

La sintaxis Transact-SQL CREATE ASSEMBLY se usa para registrar el ensamblado en la base de datos en la que desea usar el UDT. Se almacena internamente en tablas del sistema de base de datos, no externamente en el sistema de archivos. Si el UDT depende de ensamblados externos, éstos también deben cargarse en la base de datos. La instrucción CREATE TYPE se usa para crear el UDT en la base de datos en la que se va a usar. Para obtener más información, vea CREATE ASSEMBLY y CREATE TYPE.

Uso de create assembly

La sintaxis CREATE ASSEMBLY registra el ensamblado en la base de datos en la que desea usar el UDT. Cuando se registra el ensamblado, no tiene ninguna dependencia.

No se permite crear varias versiones del mismo ensamblado en una base de datos determinada. Sin embargo, es posible crear varias versiones del mismo ensamblado en función de la referencia cultural de una base de datos determinada. SQL Server distingue varias versiones culturales de un ensamblado por nombres diferentes registrados en la instancia de SQL Server. Para obtener más información, vea Crear y usar ensamblados con nombre seguro.

Cuando se ejecuta CREATE ASSEMBLY con los conjuntos de permisos de SAFE o EXTERNAL_ACCESS, se comprueba el ensamblado para asegurarse de que es verificable y seguro de tipos. Si omite especificar un conjunto de permisos, se supone SAFE. No se comprueba el código con el conjunto de permisos UNSAFE. Para obtener más información sobre los conjuntos de permisos de ensamblado, vea Diseño de ensamblados.

Ejemplo

La siguiente instrucción Transact-SQL registra el ensamblado Point en SQL Server en la base de datos AdventureWorks2022, con el conjunto de permisos SAFE. Si se omite la cláusula WITH PERMISSION_SET, el ensamblado se registra con el conjunto de permisos SAFE.

USE AdventureWorks2022;

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

La siguiente instrucción Transact-SQL registra el ensamblado mediante <assembly_bits> argumento en la cláusula FROM. Este valor varbinary representa el archivo como una secuencia de bytes.

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

Usar el tipo de creación

Una vez cargado el ensamblado en la base de datos, puede crear el tipo mediante la instrucción Transact-SQL CREATE TYPE. De esta forma, el tipo se agregará a la lista de tipos disponibles para esa base de datos. El tipo tiene como ámbito la base de datos y solamente puede usarse en la base de datos en la que se creó. Si el UDT ya existe en la base de datos, se produce un error en la instrucción CREATE TYPE.

Nota:

La sintaxis CREATE TYPE también se usa para crear tipos de datos de alias nativos de SQL Server y está pensado para reemplazar sp_addtype como medio para crear tipos de datos de alias. Algunos de los argumentos opcionales de la sintaxis de CREATE TYPE hacen referencia a la creación de UDT y no son aplicables a la creación de tipos de datos de alias (como el tipo base).

Para obtener más información, vea CREATE TYPE.

Ejemplo

La siguiente instrucción Transact-SQL crea el tipo de Point. El EXTERNAL NAME se especifica mediante la sintaxis de nomenclatura de dos partes de <assembly_name>.<udt_name>.

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

Quitar un UDT de la base de datos

La instrucción DROP TYPE quita un UDT de la base de datos actual. Una vez que se quita un UDT, puede usar la instrucción DROP ASSEMBLY para quitar el ensamblado de la base de datos.

La instrucción DROP TYPE no se ejecuta en las situaciones siguientes:

  • Tablas de la base de datos que contienen columnas definidas mediante el UDT.

  • Funciones, procedimientos almacenados o desencadenadores que usan variables o parámetros del UDT, creados en la base de datos con la cláusula WITH SCHEMABINDING.

Ejemplo

El siguiente Transact-SQL debe ejecutarse en el orden siguiente. En primer lugar, la tabla que hace referencia al Point UDT debe quitarse, después el tipo y, por último, el ensamblado.

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

Búsqueda de dependencias udT

Si hay objetos dependientes, como tablas con definiciones de columna UDT, se produce un error en la instrucción DROP TYPE. También se produce un error si hay funciones, procedimientos almacenados o desencadenadores creados en la base de datos mediante la cláusula WITH SCHEMABINDING, si estas rutinas usan variables o parámetros del tipo definido por el usuario. Primero debe quitar todos los objetos dependientes y, a continuación, ejecutar la instrucción DROP TYPE.

La siguiente consulta Transact-SQL busca todas las columnas y parámetros que usan un UDT en la base de datos de 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;

Mantenimiento de udT

No se puede modificar un UDT una vez creado en una base de datos de SQL Server, aunque puede modificar el ensamblado en el que se basa el tipo. En la mayoría de los casos, debe quitar el UDT de la base de datos con la instrucción Transact-SQL DROP TYPE, realizar cambios en el ensamblado subyacente y volver a cargarlo mediante la instrucción ALTER ASSEMBLY. A continuación, tendrá que volver a crear el UDT y todos los objetos dependientes.

Ejemplo

La instrucción ALTER ASSEMBLY se usa después de haber realizado cambios en el código fuente del ensamblado UDT y volver a compilarla. Esta instrucción copia el archivo .dll en el servidor y lo enlaza al nuevo ensamblado. Para obtener la sintaxis completa, vea ALTER ASSEMBLY.

La siguiente instrucción Transact-SQL ALTER ASSEMBLY vuelve a cargar el ensamblado Point.dll desde la ubicación especificada en el disco.

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

Uso de alter assembly para agregar código fuente

La cláusula ADD FILE de la sintaxis ALTER ASSEMBLY no está presente en CREATE ASSEMBLY. Puede usarla para agregar código fuente o cualquier otro archivo asociado a un ensamblado. Los archivos se copian desde sus ubicaciones originales y se almacenan en tablas del sistema en la base de datos. De esta forma, se garantiza que el código fuente u otros archivos estén disponibles siempre que sea necesario volver a crear o documentar la versión actual del UDT.

La siguiente instrucción Transact-SQL ALTER ASSEMBLY agrega el código fuente de la clase Point.cs para el UDT de Point. Esto copia el texto incluido en el archivo Point.cs y lo almacena en la base de datos bajo el nombre PointSource.

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

La información del ensamblado se almacena en la tabla sys.assembly_files de la base de datos donde se ha instalado el ensamblado. La tabla sys.assembly_files contiene las columnas siguientes.

Columna Descripción
assembly_id Identificador definido para el ensamblado. Este número se asigna a todos los objetos relacionados con el mismo ensamblado.
name El nombre del objeto.
file_id Un número que identifica cada objeto, con el primer objeto asociado a un assembly_id dado que se le asigna el valor de 1. Si hay varios objetos asociados con la misma assembly_id, cada valor de file_id posterior se incrementa en 1.
content Representación hexadecimal del ensamblado o archivo.

Puede usar la función CAST o CONVERT para convertir el contenido de la columna content en texto legible. La consulta siguiente convierte el contenido del archivo de Point.cs en texto legible mediante el nombre de la cláusula WHERE para restringir el conjunto de resultados a una sola fila.

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

Si copia y pega los resultados en un editor de texto, verá que se conservan los saltos de línea y los espacios que existían en el original.

Administrar UDT y ensamblados

A la hora de planear la implementación de los UDT, tenga en cuenta qué métodos se necesitan en el propio ensamblado UDT y qué métodos deberían crearse en ensamblados independientes e implementarse como funciones o procedimientos almacenados definidos por el usuario. La separación de métodos en ensamblados independientes permite actualizar el código sin afectar a los datos que podrían almacenarse en una columna UDT de una tabla. Puede modificar ensamblados UDT sin quitar columnas UDT y otros objetos dependientes solo cuando la nueva definición pueda leer los valores anteriores y la firma del tipo no cambia.

Separar el código de procedimientos que podría cambiar del código necesario para implementar el UDT simplifica considerablemente el mantenimiento. Incluir solo el código necesario para que el UDT funcione y mantener las definiciones de UDT lo más simples posible, reduce el riesgo de que el UDT en sí tenga que quitarse de la base de datos para revisiones de código o correcciones de errores.

Función udT de moneda y conversión de moneda

El Currency UDT de la base de datos de ejemplo de AdventureWorks2022 proporciona un ejemplo útil de la manera recomendada de estructurar un UDT y sus funciones asociadas. La Currency UDT se utiliza para manejar dinero basado en el sistema monetario de una referencia cultural determinada, y permite el almacenamiento de diferentes tipos de moneda, como dólares, euros, etc. La clase UDT expone un nombre de referencia cultural como una cadena y una cantidad de dinero como un tipo de datos decimal . Todos los métodos de serialización necesarios están incluidos en el ensamblado que define la clase. La función que implementa la conversión de moneda de una referencia cultural a otra se implementa como una función externa denominada ConvertCurrency, y esta función se encuentra en un ensamblado independiente. La función ConvertCurrency realiza su trabajo recuperando la tasa de conversión de una tabla de la base de datos AdventureWorks2022. Si el origen de las tasas de conversión debe cambiar alguna vez, o si debe haber algún otro cambio en el código existente, el ensamblado se puede modificar fácilmente sin afectar al UDT de Currency.

Puede encontrar la lista de código de las funciones UDT y ConvertCurrencyCurrency mediante la instalación de los ejemplos de Common Language Runtime (CLR).

Uso de udT entre bases de datos

El ámbito de los UDT es, por definición, una sola base de datos. Por lo tanto, un UDT definido en una base de datos no se puede usar en una definición de columna en otra base de datos. Para usar udT en varias bases de datos, debe ejecutar las instrucciones CREATE ASSEMBLY y CREATE TYPE en cada base de datos en ensamblados idénticos. Los ensamblados se consideran idénticos si tienen el mismo nombre, nombre seguro, referencia cultural, versión, conjunto de permisos y contenido binario.

Cuando el UDT se haya registrado y esté accesible en ambas bases de datos, podrá convertir un valor UDT de una base de datos para utilizarlo en la otra. Pueden usarse UDT idénticos en varias bases de datos en los escenarios siguientes:

  • Al llamar a un procedimiento almacenado definido en bases de datos diferentes.

  • Al consultar tablas definidas en bases de datos diferentes.

  • Al seleccionar datos UDT de una columna UDT de una tabla de base de datos e insertarlos en una segunda base de datos con una columna UDT idéntica.

En estas situaciones, todas las conversiones que requiere el servidor se realizan automáticamente. No puede realizar las conversiones explícitamente mediante las funciones Transact-SQL CAST o CONVERT.

No es necesario realizar ninguna acción para usar udT cuando el motor de base de datos de SQL Server crea tablas de trabajo en la base de datos del sistema tempdb. Esto incluye el control de cursores, variables de tabla y funciones con valores de tabla definidos por el usuario que incluyen UDT y que hacen uso transparente de tempdb. Sin embargo, si crea explícitamente una tabla temporal en tempdb que define una columna UDT, el UDT debe registrarse en tempdb igual que para una base de datos de usuario.

Contenido relacionado