CREATE TYPE (Transact-SQL)
Crea un tipo de datos de alias o un tipo definido por el usuario en la base de datos actual de SQL Server o Base de datos SQL de Azure. La implementación de un tipo de datos de alias se basa en un tipo nativo del sistema de SQL Server. Un tipo definido por el usuario se implementa a través de una clase de un ensamblado de Common Language Runtime (CLR) de Microsoft .NET Framework. Para enlazar un tipo definido por el usuario a su implementación, el ensamblado CLR que contiene la implementación del tipo debe registrarse primero en SQL Server mediante CREATE ASSEMBLY.
De manera predeterminada, la posibilidad de ejecutar código de CLR está desactivada en SQL Server. Se pueden crear, modificar y quitar objetos de base de datos que hagan referencia a módulos de código administrado, pero estas referencias no se ejecutarán en SQL Server a menos que se habilite la opción clr enabled mediante sp_configure.
Se aplica a: SQL Server (desde SQL Server 2008 hasta la versión actual), Base de datos SQL de Azure (desde la versión inicial hasta la versión actual). |
.gif "Icono de vínculo a temas")
Convenciones de sintaxis de Transact-SQL
Sintaxis
Disk-Based Type Syntax
CREATE TYPE [ schema_name. ] type_name
{
FROM base_type
[ ( precision [ , scale ] ) ]
[ NULL | NOT NULL ]
| EXTERNAL NAME assembly_name [ .class_name ]
| AS TABLE ( { <column_definition> | <computed_column_definition> }
[ <table_constraint> ] [ ,...n ] )
} [ ; ]
<column_definition> ::=
column_name <data_type>
[ COLLATE collation_name ]
[ NULL | NOT NULL ]
[
DEFAULT constant_expression ]
| [ IDENTITY [ ( seed ,increment ) ]
]
[ ROWGUIDCOL ] [ <column_constraint> [ ...n ] ]
<data type> ::=
[ type_schema_name . ] type_name
[ ( precision [ , scale ] | max |
[ { CONTENT | DOCUMENT } ] xml_schema_collection ) ]
<column_constraint> ::=
{ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[
WITH ( <index_option> [ ,...n ] )
]
| CHECK ( logical_expression )
}
<computed_column_definition> ::=
column_name AS computed_column_expression
[ PERSISTED [ NOT NULL ] ]
[
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[
WITH ( <index_option> [ ,...n ] )
]
| CHECK ( logical_expression )
]
<table_constraint> ::=
{
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
( column [ ASC | DESC ] [ ,...n ] )
[
WITH ( <index_option> [ ,...n ] )
]
| CHECK ( logical_expression )
}
<index_option> ::=
{
IGNORE_DUP_KEY = { ON | OFF }
}
Memory-Optimized Table Type Syntax
CREATE TYPE [schema_name. ] type_name
AS TABLE ( { <column_definition> }
| [ <table_constraint> ] [ ,... n ]
| [ <table_index> ] [ ,... n ]
} )
[ WITH ( <table_option> [ ,... n ] ) ]
[ ; ]
<column_definition> ::=
column_name <data_type>
[ COLLATE collation_name ]
[ NULL | NOT NULL ]
[
[ IDENTITY [ (1 , 1) ]
]
[ <column_constraint> [ ... n ] ]
[ <column_index> ]
<data type> ::=
[type_schema_name . ] type_name [ (precision [ , scale ]) ]
<column_constraint> ::=
{ PRIMARY KEY { NONCLUSTERED HASH WITH (BUCKET_COUNT = bucket_count) | NONCLUSTERED } }
< table_constraint > ::=
{ PRIMARY KEY { NONCLUSTERED HASH (column [ ,... n ] ) WITH (BUCKET_COUNT = bucket_count) | NONCLUSTERED (column [ ASC | DESC ] [ ,... n ] ) } }
<column_index> ::=
INDEX index_name
{ { [ NONCLUSTERED ] HASH WITH (BUCKET_COUNT = bucket_count) | NONCLUSTERED } }
< table_index > ::=
INDEX constraint_name
{ { [ NONCLUSTERED ] HASH (column [ ,... n ] ) WITH (BUCKET_COUNT = bucket_count) | [NONCLUSTERED] (column [ ASC | DESC ] [ ,... n ] )} }
<table_option> ::=
{
[MEMORY_OPTIMIZED = {ON | OFF}]
}
Argumentos
schema_name
Es el nombre del esquema al que pertenece el tipo de datos de alias o el tipo definido por el usuario.type_name
Es el nombre del tipo de datos de alias o del tipo definido por el usuario. Los nombres de tipos deben cumplir las reglas de los identificadores.base_type
Es el tipo de datos suministrado por SQL Server en el que se basa el tipo de datos de alias. base_type es de tipo sysname, no tiene valor predeterminado y puede tener uno de los valores siguientes:bigint
binary(n)
bit
char(n)
date
datetime
datetime2
datetimeoffset
decimal
float
image
int
money
nchar(n)
ntext
numeric
nvarchar(n | max)
real
smalldatetime
smallint
smallmoney
sql_variant
text
time
tinyint
uniqueidentifier
varbinary(n | max)
varchar(n | max)
base_type también puede ser cualquier sinónimo de tipo de datos que esté asignado a uno de estos tipos de datos del sistema.
precision
Para decimal o numeric, es un entero no negativo que indica el número máximo de dígitos decimales que se pueden almacenar en total, tanto a la derecha como a la izquierda del separador decimal. Para obtener más información, vea decimal y numeric (Transact-SQL).scale
Para decimal o numeric, es un entero no negativo que indica el número máximo de dígitos decimales que se pueden almacenar a la derecha del separador decimal, y debe ser menor o igual que el valor de precisión. Para obtener más información, vea decimal y numeric (Transact-SQL).NULL | NOT NULL
Especifica si el tipo puede contener un valor NULL. Si no se especifica, el valor predeterminado es NULL.assembly_name
Se aplica a: SQL Server 2008 a SQL Server 2014.
Especifica el ensamblado de SQL Server que hace referencia a la implementación del tipo definido por el usuario en Common Language Runtime. assembly_name debe coincidir con un ensamblado existente en SQL Server en la base de datos actual.
Nota
EXTERNAL_NAME no está disponible en una base de datos independiente.
[. class_name ]
Se aplica a: SQL Server 2008 a SQL Server 2014.
Especifica la clase en el ensamblado que implementa el tipo definido por el usuario. class_name debe ser un identificador válido y debe existir como clase en el ensamblado con visibilidad de ensamblado. class_name distingue entre mayúsculas y minúsculas, independientemente de la intercalación de base de datos, y debe coincidir exactamente con el nombre de la clase del ensamblado correspondiente. El nombre de clase puede ser un nombre de espacio de nombres entre corchetes ([ ]) si el lenguaje de programación utilizado para escribir la clase usa el concepto de espacios de nombres, como es el caso de C#. Si no se especifica class_name, SQL Server considera que es el mismo que type_name.
<column_definition>
Define las columnas para un tipo de tabla definido por el usuario.<tipo de datos>
Define el tipo de datos en una columna para un tipo de tabla definido por el usuario. Para obtener más información acerca de los tipos de datos, vea Tipos de datos (Transact-SQL). Para obtener más información acerca de las tablas, vea CREATE TABLE (SQL Server).<column_constraint>
Define las restricciones de columna para un tipo de tabla definido por el usuario. Las restricciones compatibles incluyen PRIMARY KEY, UNIQUE y CHECK. Para obtener más información acerca de las tablas, vea CREATE TABLE (SQL Server).<computed_column_definition>
Define una expresión de columna calculada como una columna en un tipo de tabla definido por el usuario. Para obtener más información acerca de las tablas, vea CREATE TABLE (SQL Server).<table_constraint>
Define una restricción de tabla para un tipo de tabla definido por el usuario. Las restricciones compatibles incluyen PRIMARY KEY, UNIQUE y CHECK.<index_option>
Especifica la respuesta de error para valores de clave duplicados en una operación de inserción de varias filas en un índice clúster o no clúster único. Para obtener más información sobre las opciones de índice, vea CREATE INDEX (Transact-SQL).INDEX
Debe especificar los índices de columna y de tabla como parte de la instrucción CREATE TABLE. CREATE INDEX y DROP INDEX no se admiten en las tablas con optimización para memoria.MEMORY_OPTIMIZED
Se aplica a: SQL Server 2014 a SQL Server 2014.
Indica si el tipo de tabla está con optimización para memoria. Esta opción está desactivada de forma predeterminada; la tabla (tipo) no es una tabla con optimización para memoria (tipo). Los tipos de tablas con optimización para memoria son tablas de usuario cuyo esquema se conserva en el disco de forma similar al de otras tablas de usuario. Solo se puede tener acceso a los tipos de tablas con optimización para memoria desde procedimientos almacenados compilados de forma nativa.
BUCKET_COUNT
Se aplica a: SQL Server 2014 a SQL Server 2014.
Indica el número de cubos que se deben crear en el índice hash. El valor máximo para BUCKET_COUNT en índices hash es 1 073 741 824. Para obtener más información acerca de los números de depósitos, vea Determinar el número correcto de depósitos para los índices hash. bucket_count es un argumento requerido.
HASH
Se aplica a: de SQL Server 2014 a SQL Server 2014.
Indica que se ha creado un índice HASH. Los índices hash solo se admiten en las tablas con optimización para memoria.
Comentarios
La clase del ensamblado a la que se hace referencia en assembly_name, junto con sus métodos, debe cumplir todos los requisitos para implementar un tipo definido por el usuario en SQL Server. Para obtener más información acerca de estos requisitos, vea Tipos definidos por el usuario de CLR.
Existen algunas consideraciones adicionales, entre las que se pueden citar las siguientes:
La clase puede tener métodos sobrecargados, pero éstos solo se pueden llamar desde el código administrado, no desde Transact-SQL.
Los miembros estáticos se deben declarar como const o readonly si assembly_name es SAFE o EXTERNAL_ACCESS.
Dentro de una base de datos solo puede haber un tipo definido por el usuario registrado con cualquier tipo especificado que se haya cargado en SQL Server desde CLR. Si se crea un tipo definido por el usuario a partir de un tipo de CLR para el que ya existe un tipo definido por el usuario en la base de datos, CREATE TYPE genera un error. Esta restricción es necesaria para evitar la ambigüedad durante la resolución de tipos SQL si un tipo CLR se puede asignar a más de un tipo definido por el usuario.
Si un método mutador del tipo no devuelve void, la instrucción CREATE TYPE no se ejecuta.
Para modificar un tipo definido por el usuario, debe volver a crearlo después de haberlo quitado mediante una instrucción DROP TYPE.
A diferencia de los tipos definidos por el usuario y creados mediante sp_addtype, el rol de base de datos public no recibe automáticamente el permiso REFERENCES para los tipos creados mediante CREATE TYPE. Este permiso debe concederse por separado.
En los tipos de tabla definidos por el usuario, los tipos definidos por el usuario estructurados que se utilizan en column_name <tipo de datos> forman parte del ámbito del esquema de la base de datos en el que se define el tipo de tabla. Para tener acceso a los tipos definidos por el usuario estructurados en un ámbito diferente dentro de la base de datos, utilice nombres de dos partes.
En los tipos de tabla definidos por el usuario, la clave principal en las columnas calculadas debe ser PERSISTED y NOT NULL.
Tipos de tablas con optimización para memoria
A partir de SQL Server 2014, el proceso de los datos de un tipo de tabla se puede realizar en la memoria principal y no en el disco. Para obtener más información, vea OLTP en memoria (optimización en memoria). Para examinar ejemplos de código en los que se muestra cómo crear tipos de tablas con optimización para memoria, vea Crear una tabla con optimización para memoria y un procedimiento almacenado compilado de forma nativa. Para obtener más información acerca de los tipos de tablas con optimización para memoria, vea Variables de tabla con optimización para memoria.
Permisos
Requiere el permiso CREATE TYPE en la base de datos actual y el permiso ALTER en schema_name. Si no se especifica schema_name, se aplican las reglas de resolución de nombres predeterminadas para determinar el esquema del usuario actual. Si se especifica assembly_name, el usuario debe ser propietario del ensamblado o tener el permiso REFERENCES en él.
Ejemplos
A.Crear un tipo de alias basado en el tipo de datos varchar
En el ejemplo siguiente se crea un tipo de alias basado en el tipo de datos varchar suministrado por el sistema.
CREATE TYPE SSN
FROM varchar(11) NOT NULL ;
B.Crear un tipo definido por el usuario
En el ejemplo siguiente se crea un tipo Utf8String que hace referencia a la clase utf8string del ensamblado utf8string. Antes de crear el tipo, se registra el ensamblado utf8string en la base de datos local.
Se aplica a: SQL Server 2008 a SQL Server 2014. |
CREATE ASSEMBLY utf8string
FROM '\\ComputerName\utf8string\utf8string.dll' ;
GO
CREATE TYPE Utf8String
EXTERNAL NAME utf8string.[Microsoft.Samples.SqlServer.utf8string] ;
GO
C.Crear un tipo de tabla definido por el usuario
En el siguiente ejemplo se crea un tipo de tabla definido por el usuario con dos columnas. Para obtener más información sobre cómo crear y utilizar los parámetros con valores de tabla, vea Usar parámetros con valores de tabla (motor de base de datos).
/* Create a user-defined table type */
CREATE TYPE LocationTableType AS TABLE
( LocationName VARCHAR(50)
, CostRate INT );
GO