Registrar tipos definidos por el usuario en SQL Server
Se aplica a:
SQL Server
Para usar un tipo definido por el usuario (UDT) en Microsoft 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. El ámbito de los UDT es una sola base de datos, por lo que no pueden usarse en varias bases de datos a menos que se registren un ensamblado y UDT idénticos en 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, vea Tipos definidos por el usuario de CLR.
Usar Visual Studio para implementar tipos UDT
La manera más fácil de implementar el UDT es mediante Microsoft 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 tema.
Siga estos pasos para crear e implementar un UDT mediante Visual Studio:
Cree un proyecto de base de datos en los nodos del lenguaje Visual Basic o Visual C# .
Agregue una referencia a la base de datos SQL Server que contendrá el UDT.
Agregue una clase Type definida por el usuario .
Escriba código para implementar el UDT.
En el menú Compilar , seleccione Implementar. Esto registra el ensamblado y crea el tipo en la base de datos SQL Server.
Usar Transact-SQL para implementar tipos UDT
La sintaxis CREATE ASSEMBLY de Transact-SQL se usa para registrar el ensamblado en la base de datos en la que desea usar el UDT. Se almacena internamente en las tablas del sistema de la 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 va a utilizarse. Para obtener más información, vea CREATE ASSEMBLY (Transact-SQL) y CREATE TYPE (Transact-SQL).
Usar CREATE ASSEMBLY
La sintaxis de la instrucción CREATE ASSEMBLY registra el ensamblado en la base de datos en la que se desea usar el UDT. Cuando se registra el ensamblado, no tiene ninguna dependencia.
No está permitido crear varias versiones del mismo ensamblado en una base de datos determinada. Sin embargo, es posible crear varias versiones del mismo ensamblado basadas en la referencia cultural en 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 el tema sobre la forma de crear y utilizar ensamblados con nombres seguros en .NET Framework SDK.
Al ejecutar CREATE ASSEMBLY con los conjuntos de permisos SAFE o EXTERNAL_ACCESS, se comprueba el ensamblado para garantizar que sea comprobable y presente seguridad de tipos. Si no se especifica ningún conjunto de permisos, se usa el conjunto de permisos SAFE. El código con el conjunto de permisos UNSAFE no se comprueba. 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 AdventureWorks, con el conjunto de permisos SAFE. Si se omite la cláusula PERMISSION_SET, el ensamblado se registra con el conjunto de permisos SAFE.
USE AdventureWorks;
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 AdventureWorks;
CREATE ASSEMBLY Point
FROM 0xfeac4 ... 21ac78
Usar CREATE TYPE
Una vez cargado el ensamblado en la base de datos, puede crear el tipo mediante la instrucción CREATE TYPE de Transact-SQL. 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, la instrucción CREATE TYPE generará un error.
Nota
La sintaxis CREATE TYPE también se usa para crear tipos de datos de alias de SQL Server nativos 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 UDTs y no sirven para crear tipos de datos de alias (como el tipo base).
Para más información, consulte CREATE TYPE (Transact-SQL).
Ejemplo
La siguiente instrucción Transact-SQL crea el tipo Point . EXTERNAL NAME se especifica mediante la sintaxis de nomenclatura de dos partes de AssemblyName. UDTName.
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 quitado un UDT, puede utilizar 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 UDT de punto debe quitarse, luego el tipo y, por último, el ensamblado.
DROP TABLE dbo.Points;
DROP TYPE dbo.Point;
DROP ASSEMBLY Point;
Buscar 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 produce un error si hay funciones, procedimientos almacenados o desencadenadores creados en la base de datos con la cláusula WITH SCHEMABINDING, cuando estas rutinas utilizan variables o parámetros del tipo definido por el usuario. Debe quitar primero todos los objetos dependientes y, a continuación, ejecutar la instrucción DROP TYPE.
La siguiente consulta de Transact-SQL busca todas las columnas y parámetros que usan un UDT en la base de datos 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;
Mantener los 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 DROP TYPE de Transact-SQL, 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 después de haberlo compilado de nuevo. Esta instrucción copia el archivo .dll en el servidor y lo enlaza al nuevo ensamblado. Para obtener la sintaxis completa, vea ALTER ASSEMBLY (Transact-SQL).
La siguiente instrucción ALTER ASSEMBLY de Transact-SQL vuelve a cargar el ensamblado de Point.dll desde la ubicación especificada en el disco.
ALTER ASSEMBLY Point
FROM '\\Projects\Point\bin\Point.dll'
Usar 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 ALTER ASSEMBLY de Transact-SQL agrega el código fuente de clase Point.cs para el UDT point . De esta forma, el texto incluido en el archivo Point.cs se copia y se almacena en la base de datos con 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.
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
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 al mismo 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 de contenido en texto legible. La consulta siguiente convierte el contenido del archivo Point.cs en texto legible, utilizando el nombre de la cláusula WHERE para restringir el conjunto de resultados a una única 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 han conservado los saltos de línea y los espacios originales.
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. Si separa los métodos en ensamblados distintos, podrá actualizar el código sin que esto afecte a los datos almacenados en una columna UDT de una tabla. Solo podrá modificar los ensamblados UDT sin quitar las columnas UDT y otros objetos dependientes cuando la nueva definición pueda leer los valores anteriores y la firma del tipo no cambie.
Puede simplificar considerablemente el mantenimiento separando el código de los procedimientos que pueden cambiar del código necesario para implementar el UDT. Si solamente incluye el código necesario para que el UDT funcione y crea las definiciones UDT de la forma más simple posible, se reducirá el riesgo de que el propio UDT tenga quitarse de la base de datos a la hora de revisar el código o corregir errores.
El UDT Currency y la función de conversión de monedas
El UDT currency de la base de datos de ejemplo AdventureWorks proporciona un ejemplo útil de la manera recomendada de estructurar un UDT y sus funciones asociadas. El UDT de moneda se utiliza para controlar el 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 AdventureWorks . Si el origen de las tasas de conversión debe cambiar alguna vez, o si debe haber otros cambios en el código existente, el ensamblado se puede modificar fácilmente sin afectar al UDT de moneda .
Puede encontrar la lista de código de las funciones Currency UDT y ConvertCurrency mediante la instalación de los ejemplos de Common Language Runtime (CLR).
Usar tipos UDT en varias 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 puede usarse en una definición de columna de otra base de datos. Para usar los 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 CAST o CONVERT de Transact-SQL.
Tenga en cuenta que no es necesario realizar ninguna acción para usar udT cuando SQL Server motor de base de datos 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 de la misma manera que para una base de datos de usuario.
Consulte también
Comentarios
Próximamente: A lo largo de 2024 iremos eliminando gradualmente GitHub Issues como mecanismo de comentarios sobre el contenido y lo sustituiremos por un nuevo sistema de comentarios. Para más información, vea: https://aka.ms/ContentUserFeedback.
Enviar y ver comentarios de