Registrar tipos definidos por el usuario en 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 que haya registrado el ensamblado UDT y que haya creado el tipo, podrá usar el UDT en Transact-SQL y en el código del cliente. Para obtener más información, vea Tipos definidos por el usuario de CLR.
Usar Visual Studio para implementar tipos UDT
La forma más fácil de implementar un UDT consiste en usar Microsoft Visual Studio. Sin embargo, en escenarios de implementación más complejos y para obtener la máxima flexibilidad, es preferible que use Transact-SQL tal y como se describe más adelante en este tema.
Siga estos pasos para crear e implementar un UDT mediante Visual Studio:
Cree un nuevo proyecto de base de datos en los nodos de lenguaje Visual C# o Visual Basic.
Agregue una referencia a la base de datos de SQL Server que contenga el UDT.
Agregue una clase Tipo definido por el usuario.
Escriba código para implementar el UDT.
En el menú Generar, seleccione Implementar. De este modo, se registrará el ensamblado y se creará el tipo en la base de datos de SQL Server.
Usar Transact-SQL para implementar tipos UDT
La sintaxis de la instrucción CREATE ASSEMBLY de Transact-SQL se usa para registrar el ensamblado en la base de datos en la que se 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 de referencia cultural de un ensamblado mediante los distintos nombres 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 acerca de los conjuntos de permisos de ensamblado, vea Diseñar ensamblados.
Ejemplo
La instrucción Transact-SQL siguiente registra el ensamblado Point de SQL Server en la base de datos AdventureWorks2008R2, 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 AdventureWorks2008R2;
CREATE ASSEMBLY Point
FROM '\\ShareName\Projects\Point\bin\Point.dll'
WITH PERMISSION_SET = SAFE;
La instrucción Transact-SQL siguiente registra el ensamblado utilizando el argumento <assembly_bits> de la cláusula FROM. Este valor varbinary representa el archivo como una secuencia de bytes.
USE AdventureWorks2008R2;
CREATE ASSEMBLY Point
FROM 0xfeac4 … 21ac78;
Usar CREATE TYPE
Una vez que haya cargado el ensamblado en la base de datos, podrá 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 de la instrucción CREATE TYPE también se usa para crear tipos de datos de alias nativos de SQL Server y sirve para reemplazar sp_addtype como un medio de creación de tipos de datos de alias. Algunos de los argumentos opcionales de la sintaxis de CREATE TYPE hacen referencia a la creación de distintos UDT y no sirven para crear tipos de datos de alias (como el tipo base).
Nota
A partir de SQL Server 2005, en una base de datos SQL Server con un nivel de compatibilidad de "80", no pueden crearse tipos administrados definidos por el usuario, procedimientos almacenados, funciones, agregados o desencadenadores. Para aprovechar las características de integración CLR de SQL Server, debe usar el procedimiento almacenado sp_dbcmptlevel (Transact-SQL) a fin de establecer el nivel de compatibilidad de la base de datos en "100".
Para obtener más información, vea 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 nombreEnsamblado.nombreUDT.
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
La siguiente consulta Transact-SQL debe ejecutarse en el orden que se indica. En primer lugar, debe quitarse la tabla que hace referencia al UDT Point, después, 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 Transact-SQL busca todas las columnas y parámetros que usan un UDT en la base de datos 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;
Mantener los UDT
No es posible modificar un UDT una vez creado en una base de datos de SQL Server, pero puede modificar el ensamblado en el que se basa el tipo. En la mayoría de los casos, tendrá que quitar el UDT de la base de datos con la instrucción DROP TYPE de Transact-SQL, efectuar 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 Point.dll desde la ubicación del disco especificada.
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 la 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 en la que 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
Nombre del objeto.file_id
Número que identifica cada objeto, siendo 1 el valor del primer objeto asociado a un assembly_id determinado. Si hay varios objetos asociados al mismo assembly_id, el valor de cada file_id subsiguiente aumenta en una unidad.content
Representación hexadecimal del ensamblado o archivo.
Puede utilizar 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 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 planificar 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. Sólo 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 AdventureWorks2008R2 proporciona un ejemplo muy útil del modo recomendado de estructurar un UDT y sus funciones asociadas. El UDT Currency se usa para administrar valores de moneda basados en el sistema monetario de una referencia cultural determinada y permite almacenar distintos tipos de moneda, como dólares, euros, etc. La clase UDT expone un nombre de referencia cultural como una cadena y un importe monetario 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 monedas 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 recupera el tipo de cambio de una tabla de la base de datos AdventureWorks2008R2. Si el origen de los tipos de cambio tiene que cambiar en algún momento, o si es necesario efectuar algún otro cambio en el código existente, el ensamblado puede modificarse con facilidad sin que esto afecte al UDT Currency.
Tiene a su disposición la lista de código del UDT Currency y las funciones ConvertCurrency instalando los ejemplos de Common Language Runtime (CLR). Para obtener más información, vea Consideraciones para instalar ejemplos y bases de datos de ejemplo de SQL Server.
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 de forma explícita utilizando las funciones CAST o CONVERT de Transact-SQL.
Tenga en cuenta que no necesitará llevar a cabo ninguna acción para utilizar los UDT cuando SQL Server Database Engine (Motor de base de datos de SQL Server) cree tablas de trabajo en la base de datos del sistema tempdb. Esto incluye la administración de cursores, variables de tabla y funciones con valores de tabla definidas por el usuario que contienen UDT y que utilizan tempdb de un modo transparente. Sin embargo, si crea explícitamente una tabla temporal en tempdb que define una columna UDT, el UDT debe registrarse en tempdb del mismo modo que en el caso de una base de datos de usuario.