CREATE TYPE (Transact-SQL)

Artículo
12/11/2023

Se aplica a:SQL Server Azure SQL Database Azure SQL Managed Instance

Crea un tipo de datos de alias o un tipo definido por el usuario en la base de datos actual de SQL Server o Azure SQL Database. La implementación de un tipo de datos de alias se basa en un tipo de sistema nativo Motor de base de datos. 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 el Motor de base de datos mediante CREATE ASSEMBLY.

De manera predeterminada, la posibilidad de ejecutar código de CLR está desactivada en SQL Server. Puede crear, modificar y quitar objetos de base de datos que hacen referencia a módulos de código administrado. Sin embargo, estas referencias no se ejecutan en SQL Server a menos que la opción clr enabled esté habilitada mediante sp_configure.

Nota:

En este tema se describe la integración de CLR de .NET Framework en SQL Server. La integración clR no se aplica a Azure SQL Database.

Convenciones de sintaxis de Transact-SQL

Sintaxis

Sintaxis de tipo de datos definida por el usuario:

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> [ , ...n ]
      [ <table_constraint> ] [ , ...n ]
      [ <table_index> ] [ , ...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 }
}

< table_index > ::=
  INDEX index_name
     [ CLUSTERED | NONCLUSTERED ] (column [ ASC | DESC ] [ , ...n ] )

Sintaxis de tipos de tabla optimizada para memoria definidas por el usuario:

CREATE TYPE [ schema_name. ] type_name
AS TABLE ( { <column_definition> [ , ...n ] }
    | [ <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 (column [ , ...n ] ) WITH ( BUCKET_COUNT = bucket_count ) 
      | NONCLUSTERED ( column [ ASC | DESC ] [ , ...n ] )
}

< table_index > ::=
  INDEX index_name
{ [ NONCLUSTERED ] HASH (column [ , ...n ] ) WITH ( BUCKET_COUNT = bucket_count )
    | [ NONCLUSTERED ] ( column [ ASC | DESC ] [ , ...n ] )
}

<table_option> ::=
{
    [ MEMORY_OPTIMIZED = { ON | OFF } ]
}

Nota:

Para ver la sintaxis de Transact-SQL para SQL Server 2014 (12.x) y versiones anteriores, consulte Versiones anteriores de la documentación.

Argumentos

schema_name

Nombre del esquema al que pertenece el tipo de datos de alias o el tipo definido por el usuario.

type_name

Nombre del tipo de datos de alias o tipo definido por el usuario. Los nombres de tipos deben cumplir las reglas de los identificadores.

base_type

El Motor de base de datos tipo de datos proporcionado en el que se basa el tipo de datos de alias. base_type es de tipo sysname, no tiene ningún valor predeterminado y puede tener uno de los valores siguientes:

bigint, int, smallint y tinyint
binary(n), varbinary(n)y varbinary(max)
bit
char(n), nchar(n), nvarchar(n), nvarchar(max), varchar(n)y varchar(max)
date, datetime, datetime2, datetimeoffset, smalldatetime y time
decimal y numeric
float y real
image
money y smallmoney
sql_variant
text y ntext
uniqueidentifier

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.

precisión

Para decimal o numérico, la precisión es un entero no negativo que indica el número total máximo de dígitos decimales que se pueden almacenar, tanto a la izquierda como a la derecha del separador decimal. Para más información, vea decimal y numeric (Transact-SQL).

scale

Para decimal o numérico, la escala 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 la precisión. Para 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

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

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 usado 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 supone 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 más información sobre los tipos de datos, vea Tipos de datos (Transact-SQL). Para más información sobre las tablas, vea CREATE TABLE (Transact-SQL).

<column_constraint>

Define las restricciones de columna para un tipo de tabla definido por el usuario. Las restricciones admitidas incluyen PRIMARY KEY, UNIQUEy CHECK. Para más información sobre las tablas, vea CREATE TABLE (Transact-SQL).

<computed_column_definition>

Define una expresión de columna calculada como una columna en un tipo de tabla definido por el usuario. Para más información sobre las tablas, vea CREATE TABLE (Transact-SQL).

<table_constraint>

Define una restricción de tabla para un tipo de tabla definido por el usuario. Las restricciones admitidas incluyen PRIMARY KEY, UNIQUEy 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 más información sobre las opciones de índice, vea CREATE INDEX (Transact-SQL).

INDEX index_name [ CLUSTERED | NONCLUSTERED ] ( column_name [ ASC | DESC ] [ , ... n ] )

Se aplica a: SQL Server 2014 (12.x) y versiones posteriores, Azure SQL Database y Azure SQL Instancia administrada.

Especifica que se creará un índice en la tabla. Puede tratarse de un índice agrupado o un índice que no esté en clúster. El índice contiene las columnas enumeradas y ordena los datos en orden ascendente o descendente.

INDEX

Debe especificar índices de columna y tabla como parte de la CREATE TABLE instrucción . CREATE INDEX y DROP INDEX no se admiten para tablas optimizadas para memoria.

MEMORY_OPTIMIZED

Se aplica a: SQL Server 2014 (12.x) y versiones posteriores, Azure SQL Database y Azure SQL Instancia administrada. Azure SQL Instancia administrada no admite tablas optimizadas para memoria en el nivel De uso general.

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 optimizada para memoria (tipo). Los tipos de tablas optimizadas para memoria son tablas de usuario cuyo esquema se conserva en el disco de forma similar al de otras tablas de usuario.

BUCKET_COUNT

Se aplica a: SQL Server 2014 (12.x) y versiones posteriores, Azure SQL Database, Azure SQL Database y Azure SQL Instancia administrada.

Indica el número de cubos que se deben crear en el índice hash. El valor máximo de BUCKET_COUNT en índices hash es 1073 741 824. Para obtener más información sobre los recuentos de cubos, consulte Índices en tablas optimizadas para memoria. bucket_count es un argumento obligatorio.

HASH

Se aplica a: SQL Server 2014 (12.x) y versiones posteriores, Azure SQL Database, Azure SQL Database y Azure SQL Instancia administrada.

Indica que se crea un HASH índice. Los índices hash solo se admiten en las tablas optimizadas para memoria.

Observaciones

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 más información sobre estos requisitos, vea CLR User-Defined Types (Tipos definidos por el usuario CLR).

Existen algunas consideraciones adicionales, entre las que se pueden citar las siguientes:

La clase puede contener métodos sobrecargados, pero estos métodos solo se pueden llamar desde el código administrado, no desde Transact-SQL.
Los miembros estáticos deben declararse 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 en un tipo CLR para el que ya existe un tipo definido por el usuario en la base de datos, CREATE TYPE se produce 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 algún método mutador del tipo no devuelve void, la CREATE TYPE instrucción no se ejecuta.

Para modificar un tipo definido por el usuario, debe quitar el tipo mediante una DROP TYPE instrucción y volver a crearlo.

A diferencia de los tipos definidos por el usuario creados mediante sp_addtype, el rol de base de datos pública no concede REFERENCES permiso automáticamente a 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 usan en column_name<data type> 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 de las columnas calculadas debe ser PERSISTED y NOT NULL.

Tipos de tablas con optimizadas para memoria

A partir de SQL Server 2014 (12.x), 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, consulte Introducción a OLTP en memoria y escenarios de uso. Para ver códigos de ejemplo que ilustran cómo crear tablas optimizadas para memoria, vea Crear una tabla con optimización para memoria y un procedimiento almacenado compilado de forma nativa.

Permisos

Requiere CREATE TYPE permiso en la base de datos actual y ALTER el permiso 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 , un usuario debe poseer el ensamblado o tener REFERENCES permiso en él.

Si las columnas de la instrucción CREATE TABLE se definen como un tipo definido por el usuario, se necesita el permiso REFERENCES en el tipo definido por el usuario.

Un usuario que crea una tabla con una columna que usa un tipo definido por el usuario necesita el REFERENCES permiso en el tipo definido por el usuario. Si se debe crear esta tabla en tempdb, es necesario conceder explícitamente el REFERENCES permiso cada vez antes de crear la tabla, o bien este tipo de datos y REFERENCES permiso deben agregarse a la model base de datos. Por ejemplo:

CREATE TYPE dbo.udt_money FROM varchar(11) NOT NULL;
GO
GRANT REFERENCES ON TYPE::dbo.udt_money TO public

Si esto se hace, este tipo de datos y REFERENCES permiso estarán disponibles de tempdb forma permanente. De lo contrario, los permisos y el tipo de datos definidos por el usuario desaparecerán cuando se reinicie SQL Server. Para obtener más información, vea CREATE TABLE.

Si no desea que todas las bases de datos nuevas hereden la definición y los permisos de este tipo de datos definido por el usuario del modelo, puede usar un procedimiento almacenado de inicio para crear y asignar los permisos adecuados solo en tempdb la base de datos. Por ejemplo:

USE master
GO
CREATE PROCEDURE setup_udt_in_tempdb
AS
EXEC ( 'USE tempdb;
CREATE TYPE dbo.udt_money FROM varchar(11) NOT NULL;
GRANT REFERENCES ON TYPE::dbo.udt_money TO public;')
GO
EXEC sp_procoption 'setup_udt_in_tempdb' , 'startup' , 'on'
GO

Como alternativa, en lugar de usar tablas temporales, considere la posibilidad de usar variables de tabla cuando necesite hacer referencia a tipos de datos definidos por el usuario para las necesidades de almacenamiento temporales. Para que las variables de tabla hagan referencia a tipos de datos definidos por el usuario, no es necesario conceder explícitamente permisos para el tipo de datos definido por el usuario.

Ejemplos

A Creación de 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. Creación de un tipo definido por el usuario

Se aplica a: SQL Server

En el siguiente ejemplo 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. Reemplace la parte binaria de la CREATE ASSEMBLY instrucción por una descripción válida.

CREATE ASSEMBLY utf8string
AUTHORIZATION [dbi]
FROM 0x4D... ;
GO

CREATE TYPE Utf8String
EXTERNAL NAME utf8string.[Microsoft.Samples.SqlServer.utf8string];
GO

C. Creación de 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 más información sobre cómo crear y usar parámetros con valores de tabla, veaUsar parámetros con valores de tabla (motor de base de datos).

CREATE TYPE LocationTableType AS TABLE (
    LocationName VARCHAR(50),
    CostRate INT
);
GO

D. Creación de un tipo de tabla definido por el usuario con clave principal e índice

En el ejemplo siguiente se crea un tipo de tabla definido por el usuario que tiene tres columnas, una de las cuales (Name) es la clave principal y la otra (Price) tiene un índice no agrupado. Para más información sobre cómo crear y usar parámetros con valores de tabla, veaUsar parámetros con valores de tabla (motor de base de datos).

CREATE TYPE InventoryItem AS TABLE (
    [Name] NVARCHAR(50) NOT NULL,
    SupplierId BIGINT NOT NULL,
    Price DECIMAL(18, 4) NULL,
    PRIMARY KEY (Name),
    INDEX IX_InventoryItem_Price(Price)
);
GO

CREATE TYPE (Transact-SQL)

Sintaxis

Argumentos

schema_name

type_name

base_type

precisión

scale

NULL | NOT NULL

assembly_name

[ . class_name ]

<column_definition>

<tipo de datos>

<column_constraint>

<computed_column_definition>

<table_constraint>

<index_option>

INDEX index_name [ CLUSTERED | NONCLUSTERED ] ( column_name [ ASC | DESC ] [ , ... n ] )

INDEX

MEMORY_OPTIMIZED

BUCKET_COUNT

HASH

Observaciones

Tipos de tablas con optimizadas para memoria

Permisos

Ejemplos

A Creación de un tipo de alias basado en el tipo de datos varchar

B. Creación de un tipo definido por el usuario

C. Creación de un tipo de tabla definido por el usuario

D. Creación de un tipo de tabla definido por el usuario con clave principal e índice

Comentarios

Comentarios

Recursos adicionales

CREATE TYPE (Transact-SQL)

Sintaxis

Argumentos

schema_name

type_name

base_type

precisión

scale

NULL | NOT NULL

assembly_name

[ . class_name ]

<column_definition>

<tipo de datos>

<column_constraint>

<computed_column_definition>

<table_constraint>

<index_option>

INDEX index_name [ CLUSTERED | NONCLUSTERED ] ( column_name [ ASC | DESC ] [ , ... n ] )

INDEX

MEMORY_OPTIMIZED

BUCKET_COUNT

HASH

Observaciones

Tipos de tablas con optimizadas para memoria

Permisos

Ejemplos

A Creación de un tipo de alias basado en el tipo de datos varchar

B. Creación de un tipo definido por el usuario

C. Creación de un tipo de tabla definido por el usuario

D. Creación de un tipo de tabla definido por el usuario con clave principal e índice

Contenido relacionado

Comentarios

Comentarios

Recursos adicionales