CREATE TYPE (Transact-SQL)

  • Artigo

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 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 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 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 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 do CLR não se aplica ao Banco de Dados SQL do Azure.

Convenções de sintaxe de Transact-SQL

Sintaxe

Sintaxe de tipo de dados definida 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 ] )
     [INCLUDE (column, ...n)]

Sintaxe de tipos de tabela com otimização de 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 } ]
}

Argumentos

schema_name

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

type_name

O nome do tipo de dados de alias ou do 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 de 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
  • money e smallmoney
  • 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, scale é 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 independente.

[ . 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 presumirá 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 com suporte 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 com suporte 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 de 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 têm suporte para tabelas com otimização de 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 de SQL do Azure. A Instância Gerenciada de SQL do Azure não dá suporte a tabelas com otimização de 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 com otimização de 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 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 índices de hash é 1.073.741.824. Para obter mais informações sobre contagens de bucket, consulte Índices em tabelas com otimização de 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 de 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.

  • Todos os membros estáticos devem ser declarados como const ou readonly se assembly_name for 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 já existe um tipo definido pelo usuário no banco de dados, CREATE TYPE ele 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 algum 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 recriá-lo.

Ao contrário dos tipos definidos pelo usuário que são criados usando sp_addtypeo , a função de banco de dados público não recebe permissão automaticamente REFERENCES em tipos criados usando 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, consulte Visão geral do OLTP in-memory 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 nomes padrão para determinar o esquema para o usuário atual serão aplicadas. Se assembly_name for especificado, um usuário deverá ser o proprietário do assembly ou ter REFERENCES permissão sobre ele.

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 em tempdb, a REFERENCES permissão precisará ser concedida explicitamente todas as vezes antes da criação da tabela 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 que as variáveis de tabela façam referência a tipos de dados definidos pelo usuário, você não precisa conceder permissões explicitamente 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 CREATE ASSEMBLY parte binária da 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