CREATE TYPE (Transact-SQL)

Artigo
01/30/2023
11 minutos para o fim da leitura

Aplica-se a:SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

Cria um tipo de dados de alias ou um tipo definido pelo usuário no banco de dados atual no SQL Server ou no Banco de Dados SQL do Azure. A implementação de um tipo de dados de alias é baseada em um tipo de sistema nativo do SQL Server. Um tipo definido pelo usuário é implementado por meio de uma classe de um assembly no CLR (Common Language Runtime) do Microsoft .NET Framework. Para associar um tipo definido pelo usuário à sua implementação, o assembly CLR que contém a implementação do tipo deve primeiro ser registrado no SQL Server com CREATE ASSEMBLY.

A capacidade de executar código CLR é desativada por padrão no SQL Server. Você pode criar, modificar e remover objetos do banco de dados que referenciam módulos de código gerenciado, mas essas referências não serão executadas no SQL Server a menos que a clr enabled Option seja habilitada usando sp_configure.

Observação

A integração do CLR do .NET Framework ao SQL Server é discutida neste tópico. A integração CLR não se aplica ao Banco de Dados SQL do Azure.

Convenções de sintaxe de Transact-SQL

Sintaxe

-- User-defined Data 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> [ ,... 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 constraint_name  
     [ CLUSTERED | NONCLUSTERED ] (column [ ASC | DESC ] [ ,... n ] )} }

-- User-defined Memory Optimized Table Types syntax  
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 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 } ]  
}

Observação

Para ver a sintaxe do Transact-SQL para o SQL Server 2014 e versões anteriores, confira a Documentação das versões anteriores.

Argumentos

schema_name
É o nome do esquema ao qual o tipo de dados de alias ou tipo definido pelo usuário pertence.

type_name
É o nome do tipo de dados de alias ou tipo definido pelo usuário. Os nomes de tipos devem obedecer às regras de identificadores.

base_type
É o tipo de dados fornecido pelo SQL Server no qual o tipo de dados de alias se baseia. base_type é sysname, sem padrão, e pode ter um dos seguintes valores:

bigint
binary(n)
bit
char(n)

date
datetime
datetime2
datetimeoffset

decimal
float
imagem
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 também pode ser qualquer sinônimo de tipo de dados mapeado para um desses tipos de dados de sistema.

precisão
Para decimal ou numeric, é um inteiro não negativo que indica o número total máximo de dígitos decimais que podem ser armazenados à esquerda e à direita do ponto decimal. Para obter mais informações, confira decimal e numeric (Transact-SQL).

scale
Para decimal ou numeric, é um inteiro não negativo que indica o número máximo de dígitos decimais que podem ser armazenados à direita do ponto decimal e ele deve ser menor ou igual à precisão. Para obter mais informações, confira decimal e numeric (Transact-SQL).

NULL | NOT NULL
Especifica se o tipo pode ter um valor nulo. Se não for especificado, NULL é o padrão.

assembly_name
Aplica-se a: SQL Server [a partir do SQL Server 2008 (10.0.x)].

Especifica o assembly do SQL Server que referencia a implementação do tipo definido pelo usuário no common language runtime. assembly_name deve corresponder a um assembly existente no SQL Server no banco de dados atual.

Observação

EXTERNAL_NAME não está disponível em um banco de dados independente.

[.class_name]
Aplica-se a: SQL Server [a partir do SQL Server 2008 (10.0.x)].

Especifica a classe dentro do assembly que implementa o tipo definido pelo usuário. class_name deve ser um identificador válido e deve existir como uma classe no assembly com visibilidade do assembly. class_name diferencia maiúsculas de minúsculas, independentemente da ordenação de banco de dados, e deve corresponder exatamente ao nome de classe no assembly correspondente. O nome de classe poderá ser um nome qualificado de namespace entre colchetes ( [ ] ) se a linguagem de programação usada para gravar a classe usar o conceito de namespaces, como C#. Se class_name não for especificado, o SQL Server assumirá que ele é o mesmo que type_name.

<column_definition>
Define as colunas para um tipo de tabela definido pelo usuário.

<tipo de dados>
Define o tipo de dados em uma coluna para um tipo de tabela definido pelo usuário. Para obter mais informações sobre tipos de dados, confira Tipos de dados (Transact-SQL). Para obter mais informações sobre tabelas, confira CREATE TABLE (Transact-SQL).

<column_constraint>
Define as restrições de coluna para um tipo de tabela definido pelo usuário. As restrições com suporte incluem PRIMARY KEY, UNIQUE e CHECK. Para obter mais informações sobre tabelas, confira CREATE TABLE (Transact-SQL).

<computed_column_definition>
Define uma expressão de coluna computada como uma coluna em um tipo de tabela definido pelo usuário. Para obter mais informações sobre tabelas, confira CREATE TABLE (Transact-SQL).

<table_constraint>
Define uma restrição de tabela em um tipo de tabela definido pelo usuário. As restrições com suporte incluem PRIMARY KEY, UNIQUE e CHECK.

<index_option>
Especifica a resposta de erro para duplicar valores de chave em uma operação de inserção de várias linhas em um índice exclusivo clusterizado ou não clusterizado. Para obter mais informações sobre opções de índice, confira CREATE INDEX (Transact-SQL).

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

Aplica-se a: SQL Server (começando com SQL Server 2014 (12.x)), Banco de Dados SQL do Azure e Instância Gerenciada do Azure SQL.

Especifica a criação de um índice na tabela. Isso pode ser um índice clusterizado ou um índice não clusterizado. O índice conterá as colunas listadas e classificará os dados em ordem crescente ou decrescente.

INDEX
Você deve especificar os índices de tabela e coluna como parte da instrução CREATE TABLE. CREATE INDEX e DROP INDEX não têm suporte para tabelas com otimização de memória.

MEMORY_OPTIMIZED
Aplica-se a: SQL Server (começando com SQL Server 2014 (12.x)), Banco de Dados SQL do Azure e Instância Gerenciada do Azure SQL. Instância Gerenciada do Azure SQL não oferece suporte a tabelas otimizadas para memória na camada de Uso Geral.

Indica se o tipo de tabela tem otimização de memória. Essa opção é desabilitada por padrão; a tabela (tipo) não é uma tabela (tipo) com otimização de memória. Os tipos de tabela com otimização de memória são tabelas de usuário com otimização de memória, o esquema que é mantido no disco, semelhante a outras tabelas de usuário.

BUCKET_COUNT
Aplica-se a: SQL Server (a partir do SQL Server 2014 (12.x)) e Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure.

Indica o número de buckets que devem ser criados no índice de hash. O valor máximo para BUCKET_COUNT em índices de hash é 1.073.741.824. Para obter mais informações sobre o número de buckets, veja Índices para tabelas com otimização de memória. bucket_count é um argumento obrigatório.

HASH
Aplica-se a: SQL Server (a partir do SQL Server 2014 (12.x)) e Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure.

Indica que um índice de HASH foi criado. Há suporte para índices de hash apenas em tabelas com otimização de memória.

Comentários

A classe do assembly que é referenciado em assembly_name, incluindo seus métodos, deve atender a todos os requisitos de implementação de um tipo definido pelo usuário no SQL Server. Para obter mais informações sobre esses requisitos, consulte Tipos CLR definidos pelo usuário.

Considerações adicionais incluem o seguinte:

A classe pode ter métodos sobrecarregados, mas eles podem ser chamados somente de dentro do código gerenciado, e não do Transact-SQL.
Os membros estáticos devem ser declarados como const ou readonly se assembly_name é SAFE ou EXTERNAL_ACCESS.

Dentro de um banco de dados, pode haver apenas um único tipo definido pelo usuário registrado em qualquer tipo especificado que seja carregado no SQL Server do CLR. Se um tipo definido pelo usuário for criado em um tipo CLR para o qual um tipo definido pelo usuário já exista no banco de dados, CREATE TYPE falhará com um erro. Essa restrição é necessária para evitar ambiguidade durante a resolução de Tipo SQL se um tipo CLR puder ser mapeado para mais de um tipo definido pelo usuário.

Se um método modificador no tipo não retornar void, a instrução CREATE TYPE não será executada.

Para modificar um tipo definido pelo usuário, você deve descartar o tipo usando uma instrução DROP TYPE e, em seguida, recriá-lo.

Ao contrário dos tipos definidos pelo usuário que são criados com sp_addtype, a função de banco de dados public, não recebe automaticamente a permissão REFERENCES em tipos que são criados com CREATE TYPE. Essa permissão deve ser concedida separadamente.

Em tipos de tabela definidos pelo usuário, os tipos estruturados definidos pelo usuário que são usados em column_name<data type> fazem parte do escopo de esquema do banco de dados no qual o tipo de tabela é definido. Para acessar os tipos estruturados definidos pelo usuário em um escopo diferente dentro do banco de dados, use nomes de duas partes.

Em tipos de tabela definidos pelo usuário, a chave primária em colunas computadas deve ser PERSISTED e NOT NULL.

Tipos de tabela com otimização de memória

Desde o SQL Server 2014 (12.x), o processamento de dados em um tipo de tabela pode ser feito na memória principal, e não no disco. Para obter mais informações, veja OLTP in-memory (otimização na memória). Para obter exemplos de código que mostram como criar tipos de tabela com otimização de memória, consulte Criando uma tabela com otimização de memória e um procedimento armazenado compilado nativamente.

Permissões

Exige a permissão CREATE TYPE no banco de dados atual e a permissão ALTER no schema_name. Se schema_name não for especificado, serão aplicadas as regras de resolução de nome padrão para determinar o esquema do usuário atual. Se assembly_name for especificado, um usuário deverá ter o assembly ou ter a permissão REFERENCES nele.

Se qualquer coluna na instrução CREATE TABLE for definida como sendo de um tipo definido pelo usuário, a permissão REFERENCES no tipo definido pelo usuário será necessária.

Observação

Um usuário que cria uma tabela com uma coluna que usa um tipo definido pelo usuário precisa da permissão REFERENCES no tipo definido pelo usuário. Se essa tabela tiver que ser criada no TempDB, a permissão REFERENCES precisa ser concedida explicitamente toda vez antes de a tabela ser criada, ou este tipo de dados e as permissões REFERENCES precisam ser adicionados ao modelo de banco de dados. Se isso for feito, esse tipo de dados e permissões estarão disponíveis em TempDB permanentemente. Caso contrário, o tipo de dados e as permissões definidos pelo usuário desaparecerão quando o SQL Server for reiniciado. Para saber mais, veja CREATE TABLE

Exemplos

a. Criando um tipo de alias com base no tipo de dados varchar

O exemplo a seguir cria um tipo de alias com base no tipo de dados varchar fornecido pelo sistema.

CREATE TYPE SSN  
FROM varchar(11) NOT NULL ;

B. Criando um tipo definido pelo usuário

O exemplo a seguir cria um tipo Utf8String que referencia a classe utf8string no assembly utf8string. Antes de criar o tipo, o assembly utf8string é registrado no banco de dados local. Substitua a parte binária da instrução CREATE ASSEMBLY por uma descrição válida.

Aplica-se a: SQL Server [a partir do SQL Server 2008 (10.0.x)].

CREATE ASSEMBLY utf8string  
AUTHORIZATION [dbi]   
FROM 0x4D... ;  
GO  
CREATE TYPE Utf8String   
EXTERNAL NAME utf8string.[Microsoft.Samples.SqlServer.utf8string] ;  
GO

C. Criando um tipo de tabela definido pelo usuário

O exemplo a seguir cria um tipo de tabela definido pelo usuário que tem duas colunas. Para obter mais informações sobre como criar e usar parâmetros com valor de tabela, confira Usar parâmetros com valor de tabela (Mecanismo de Banco de Dados);.

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

D. Como criar um tipo de tabela definido pelo usuário com o índice e a chave primária

O exemplo a seguir cria um tipo de tabela definido pelo usuário que tem três colunas, uma delas (Name) é a chave primária e outra (Price) tem um índice não clusterizado. Para obter mais informações sobre como criar e usar parâmetros com valor de tabela, confira Usar parâmetros com valor de tabela (Mecanismo de Banco de Dados);.

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

Consulte Também

CREATE ASSEMBLY (Transact-SQL)
DROP TYPE (Transact-SQL)
EVENTDATA (Transact-SQL)
Tipos definidos pelo usuário de CLR
Trabalhando com tipos de dados definidos pelo usuário no SQL Server

CREATE TYPE (Transact-SQL)

Sintaxe

Argumentos

Comentários

Tipos de tabela com otimização de memória

Permissões

Exemplos

a. Criando um tipo de alias com base no tipo de dados varchar

B. Criando um tipo definido pelo usuário

C. Criando um tipo de tabela definido pelo usuário

D. Como criar um tipo de tabela definido pelo usuário com o índice e a chave primária

Consulte Também

Recursos adicionais

Recursos adicionais