CREATE TYPE (Transact-SQL)
Aplica-se a:
SQL ServerBanco de Dados SQL do AzureInstâ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 alias é baseada em um tipo de sistema nativo do Mecanismo de Banco de Dados. 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 vincular 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 Mecanismo de Banco de Dados usando CREATE ASSEMBLY.
A capacidade de executar código CLR é desativada por padrão no SQL Server. Você pode criar, modificar e descartar objetos de banco de dados que fazem referência a módulos de código gerenciado. No entanto, essas referências não são executadas no SQL Server, a menos que a opção clr enabled esteja 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
Sintaxe do tipo de dados definido pelo usuário:
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 ] )
Sintaxe de tipos de tabela otimizados para memória definida pelo usuário:
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 } ]
}
Observação
Para exibir a sintaxe do Transact-SQL para o SQL Server 2014 (12.x) e versões anteriores, confira a Documentação das versões anteriores.
Argumentos
schema_name
O nome do esquema ao qual pertence o tipo de dados alias ou o tipo definido pelo usuário.
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 Mecanismo de Banco de Dados forneceu o tipo de dados no qual o tipo de dados alias se baseia. base_type é sysname, sem padrão, e pode ter um dos seguintes valores:
- bigint, int, smallint e tinyint
- binary(n), varbinary(n) e varbinary(max)
- bit
- char(n), nchar(n), nvarchar(n), nvarchar(max), varchar(n), e varchar(max)
- date, datetime, datetime2, datetimeoffset, smalldatetime e time
- decimal e numeric
- flutuador e real
- imagem
- dinheiro e dinheiro pequeno
- sql_variant
- text e ntext
- uniqueidentifier
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 numérico, precisão é um inteiro não negativo que indica o número total máximo de dígitos decimais que podem ser armazenados, tanto à esquerda quanto à direita da vírgula decimal. Para obter mais informações, confira decimal e numeric (Transact-SQL).
scale
Para decimal ou numérico, escala é um inteiro não negativo que indica o número máximo de dígitos decimais que podem ser armazenados à direita da vírgula decimal, e 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, o padrão será NULL.
assembly_name
Aplica-se a: SQL Server
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 contido.
[ . class_name ]
Aplica-se a: SQL Server
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 é 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 suportadas incluem PRIMARY KEY, UNIQUEe 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 suportadas incluem PRIMARY KEY, UNIQUEe 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).
ÍNDICE index_name [ AGRUPADO | NÃO AGRUPADO ] ( column_name [ ASC | DESC ] [ , ... n ] )
Aplica-se a: SQL Server 2014 (12.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada SQL do Azure.
Especifica a criação de um índice na tabela. Isso pode ser um índice clusterizado ou um índice não clusterizado. O índice contém as colunas listadas e classifica os dados em ordem crescente ou decrescente.
INDEX
Você deve especificar índices de coluna e tabela como parte da CREATE TABLE instrução. CREATE INDEX e DROP INDEX não são suportados para tabelas otimizadas para memória.
MEMORY_OPTIMIZED
Aplica-se a: SQL Server 2014 (12.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada SQL do Azure. A Instância Gerenciada SQL do Azure 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 está desativada por padrão; A tabela (tipo) não é uma tabela otimizada para memória (tipo). 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 2014 (12.x) e versões posteriores, Banco de Dados SQL do Azure, Banco de Dados SQL do Azure e Instância Gerenciada 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 contagens de bucket, consulte Índices em tabelas otimizadas para memória. bucket_count é um argumento obrigatório.
HASH
Aplica-se a: SQL Server 2014 (12.x) e versões posteriores, Banco de Dados SQL do Azure, Banco de Dados SQL do Azure e Instância Gerenciada SQL do Azure.
Indica que um HASH índice 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 conter métodos sobrecarregados, mas esses métodos podem ser chamados somente de dentro do código gerenciado, não do Transact-SQL.
Quaisquer membros estáticos devem ser declarados como const ou readonly se assembly_name for
SAFEouEXTERNAL_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 já existe um tipo definido pelo usuário 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 qualquer método mutador no tipo não retornar void, a CREATE TYPE instrução não será executada.
Para modificar um tipo definido pelo usuário, você deve descartar o tipo usando uma DROP TYPE instrução e, em seguida, recriá-lo.
Ao contrário dos tipos definidos pelo usuário que são criados usando o , a função de banco de dados público não recebe permissão automaticamente REFERENCES em tipos criados usando CREATE TYPEsp_addtypeo . 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 nas 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, consulte Visão geral do OLTP na memória e cenários de uso. 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
Requer CREATE TYPE permissão no banco de dados atual e ALTER permissão no schema_name. Se schema_name não for especificado, as regras de resolução de nome padrão para determinar o esquema para o usuário atual serão aplicadas. Se assembly_name for especificado, um usuário deverá possuir o assembly ou ter REFERENCES permissão nele.
Se uma coluna da instrução CREATE TABLE for definida como um tipo definido pelo usuário, a permissão REFERENCES no tipo definido pelo usuário será necessária.
Um usuário que cria uma tabela com uma coluna que usa um tipo definido pelo usuário precisa da REFERENCES permissão no tipo definido pelo usuário. Se essa tabela precisar ser criada no tempdb, a REFERENCES permissão precisará ser concedida explicitamente todas as vezes antes que a tabela seja criada, ou esse tipo de dados e REFERENCES permissão precisarão ser adicionados ao model banco de dados. Por exemplo:
CREATE TYPE dbo.udt_money FROM varchar(11) NOT NULL;
GO
GRANT REFERENCES ON TYPE::dbo.udt_money TO public
Se isso for feito, esse tipo de dados e REFERENCES permissão estarão disponíveis permanentemente tempdb . Caso contrário, o tipo de dados e as permissões definidos pelo usuário desaparecerão quando o SQL Server for reiniciado. Para obter mais informações, confira CREATE TABLE.
Se você não quiser que cada novo banco de dados herde a definição e as permissões para esse tipo de dados definido pelo usuário do modelo, poderá usar um procedimento armazenado de inicialização para criar e atribuir as permissões apropriadas somente no tempdb banco de dados. Por exemplo:
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, em vez de usar tabelas temporárias, considere o uso de variáveis de tabela quando precisar fazer referência a tipos de dados definidos pelo usuário para necessidades de armazenamento temporário. Para variáveis de tabela para fazer referência a tipos de dados definidos pelo usuário, você não precisa conceder explicitamente permissões para o tipo de dados definido pelo usuário.
Exemplos
R. Criar 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. Criar um tipo definido pelo usuário
Aplica-se a: SQL Server
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 CREATE ASSEMBLY instrução por uma descrição válida.
CREATE ASSEMBLY utf8string
AUTHORIZATION [dbi]
FROM 0x4D... ;
GO
CREATE TYPE Utf8String
EXTERNAL NAME utf8string.[Microsoft.Samples.SqlServer.utf8string];
GO
C. Criar 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. Criar um tipo de tabela definido pelo usuário com chave primária e índice
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
Conteúdo relacionado
Comentários
Em breve: Ao longo de 2024, eliminaremos os problemas do GitHub como o mecanismo de comentários para conteúdo e o substituiremos por um novo sistema de comentários. Para obter mais informações, consulte https://aka.ms/ContentUserFeedback.
Enviar e exibir comentários de