Partilhar via

CRIAR TIPO (Transact-SQL)

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

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 Microsoft .NET Framework common language runtime (CLR). 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 o código CLR está 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 gerenciados. 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.

Note

A integração do CLR do .NET Framework no SQL Server é discutida neste artigo. A integração com CLR não se aplica ao Azure SQL Database nem à base de dados SQL no Microsoft Fabric, onde os tipos CLR (.NET) não são suportados.

Transact-SQL convenções de sintaxe

Syntax

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 ] )
     [INCLUDE (column, ...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 } ]
}

Arguments

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 tipo definido pelo usuário. Os nomes dos tipos devem estar em conformidade com as regras relativas aos 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 ser um dos seguintes valores:

  • bigint, int, smallint e tinyint
  • binário(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 hora
  • decimal e numérico
  • flutuador e real
  • image
  • dinheiro e dinheiro pequeno
  • sql_variant
  • texto e ntexto
  • uniqueidentifier

base_type também pode ser qualquer sinônimo de tipo de dados que mapeia para um desses tipos de dados do sistema.

precision

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, consulte decimal e numérico (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 do ponto decimal, e deve ser menor ou igual à precisão. Para obter mais informações, consulte decimal e numérico (Transact-SQL).

NULO | NÃO NULO

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

assembly_name

Aplica-se a: SQL Server

Especifica o assembly do SQL Server que faz referência à 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.

Note

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 de assembly. class_name diferencia maiúsculas de minúsculas, independentemente do agrupamento do banco de dados, e deve corresponder exatamente ao nome da classe no assembly correspondente. O nome da classe pode ser um nome qualificado para namespace entre colchetes ([ ]) se a linguagem de programação usada para escrever 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, consulte Tipos de dados (Transact-SQL). Para obter mais informações sobre tabelas, consulte 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, consulte 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, consulte 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 clusterizado exclusivo ou não clusterizado. Para obter mais informações sobre opções de índice, consulte 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 para criar um índice na tabela. 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 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 SQL do Azure. A Instância Gerenciada SQL do Azure não oferece suporte a tabelas otimizadas de memória na camada de Uso Geral.

Indica se o tipo de tabela está otimizado para memória. Esta 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, cujo esquema é persistido no disco de forma 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 do 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 buckets, consulte Índices em Memory-Optimized tabelas. bucket_count é um argumento necessá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 do SQL do Azure.

Indica que um HASH índice foi criado. Os índices de hash são suportados apenas em tabelas otimizadas para memória.

Remarks

A classe do assembly referenciada no assembly_name, juntamente com seus métodos, deve satisfazer todos os requisitos para implementar um tipo definido pelo usuário no SQL Server. Para obter mais informações sobre esses requisitos, consulte Tipos de User-Defined CLR.

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 somente leitura se assembly_name for SAFE ou EXTERNAL_ACCESS.

Dentro de um banco de dados, pode haver apenas um tipo definido pelo usuário registrado em qualquer tipo especificado que tenha sido carregado no SQL Server a partir 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 sp_addtypeo , a função de banco de dados público não recebe permissão automaticamente REFERENCES nos tipos criados usando CREATE TYPEo . Esta 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<tipo> de dados fazem parte do escopo do esquema de banco de dados no qual o tipo de tabela é definido. Para acessar tipos estruturados definidos pelo usuário em um escopo diferente no 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.

No banco de dados SQL da malha, os tipos definidos pelo usuário podem ser criados, mas não são espelhados no Fabric OneLake, e as colunas dos tipos definidos pelo usuário são ignoradas no espelhamento.

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

A partir do SQL Server 2014 (12.x), o processamento de dados em um tipo de tabela pode ser feito na memória primária e não no disco. Para obter mais informações, consulte In-Memory visão geral do OLTP e cenários de uso. Para obter exemplos de código mostrando como criar tipos de tabela com otimização de memória, consulte Criando uma tabela de Memory-Optimized e um procedimento armazenado compilado nativamente.

Permissions

Requer CREATE TYPE permissão no banco de dados atual e ALTER permissão em schema_name. Se schema_name não for especificado, aplicam-se as regras de resolução de nome padrão para determinar o esquema para o usuário atual. Se assembly_name for especificado, um usuário deverá possuir o assembly ou ter REFERENCES permissão sobre ele.

Se alguma coluna na instrução CREATE TABLE for definida como sendo de um tipo definido pelo usuário, REFERENCES permissão 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 sempre 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 definido pelo usuário e as permissões desaparecerão quando o SQL Server for reiniciado. Para obter mais informações, consulte 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, não é necessário conceder explicitamente permissões para o tipo de dados definido pelo usuário.

Examples

A. 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 fornecido varchar 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 faz referência à 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, consulte Usar parâmetros de Table-Valued (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 das quais (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, consulte Usar parâmetros de Table-Valued (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

  • Last updated on