MESA DE ALTERAÇÕES (Transact-SQL)

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsPDW (Analytics Platform System)Warehouse no Microsoft FabricBanco de dados SQL no Microsoft Fabric

Modifica uma definição de tabela alterando, adicionando ou descartando colunas e restrições. ALTER TABLE também reatribui e recria partições ou desabilita e habilita restrições e gatilhos.

A sintaxe para ALTER TABLE é diferente para tabelas baseadas em disco e tabelas com otimização de memória. Use os links a seguir para acessar diretamente o bloco de sintaxe adequado para seus tipos de tabela e os exemplos de sintaxe adequados:

Tabelas baseadas em disco:

• Syntax
• Examples

Tabelas com otimização de memória:

• Syntax
• Alterando tabelas de Memory-Optimized

Para obter mais informações sobre as convenções de sintaxe, confira Convenções de sintaxe Transact-SQL.

Sintaxe para tabelas baseadas em disco

ALTER TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
{
    ALTER COLUMN column_name
    {
        [ type_schema_name. ] type_name
            [ (
                {
                   precision [ , scale ]
                 | max
                 | xml_schema_collection
                }
            ) ]
        [ COLLATE collation_name ]
        [ NULL | NOT NULL ] [ SPARSE ]
      | { ADD | DROP }
          { ROWGUIDCOL | PERSISTED | NOT FOR REPLICATION | SPARSE | HIDDEN }
      | { ADD | DROP } MASKED [ WITH ( FUNCTION = ' mask_function ') ]
    }
    [ WITH ( ONLINE = ON | OFF ) ]
    | [ WITH { CHECK | NOCHECK } ]

    | ADD
    {
        <column_definition>
      | <computed_column_definition>
      | <table_constraint>
      | <column_set_definition>
    } [ ,...n ]
      | [ system_start_time_column_name datetime2 GENERATED ALWAYS AS ROW START
                [ HIDDEN ] [ NOT NULL ] [ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES] ,
                system_end_time_column_name datetime2 GENERATED ALWAYS AS ROW END
                   [ HIDDEN ] [ NOT NULL ][ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES] ,
                start_transaction_id_column_name bigint GENERATED ALWAYS AS TRANSACTION_ID START
                   [ HIDDEN ] NOT NULL [ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES],
                  end_transaction_id_column_name bigint GENERATED ALWAYS AS TRANSACTION_ID END
                   [ HIDDEN ] NULL [ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES],
                  start_sequence_number_column_name bigint GENERATED ALWAYS AS SEQUENCE_NUMBER START
                   [ HIDDEN ] NOT NULL [ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES],
                  end_sequence_number_column_name bigint GENERATED ALWAYS AS SEQUENCE_NUMBER END
                   [ HIDDEN ] NULL [ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES]
        ]
       PERIOD FOR SYSTEM_TIME ( system_start_time_column_name, system_end_time_column_name )
    | DROP
     [ {
         [ CONSTRAINT ][ IF EXISTS ]
         {
              constraint_name
              [ WITH
               ( <drop_clustered_constraint_option> [ ,...n ] )
              ]
          } [ ,...n ]
          | COLUMN [ IF EXISTS ]
          {
              column_name
          } [ ,...n ]
          | PERIOD FOR SYSTEM_TIME
     } [ ,...n ] ]
    | [ WITH { CHECK | NOCHECK } ] { CHECK | NOCHECK } CONSTRAINT
        { ALL | constraint_name [ ,...n ] }

    | { ENABLE | DISABLE } TRIGGER
        { ALL | trigger_name [ ,...n ] }

    | { ENABLE | DISABLE } CHANGE_TRACKING
        [ WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } ) ]

    | SWITCH [ PARTITION source_partition_number_expression ]
        TO target_table
        [ PARTITION target_partition_number_expression ]
        [ WITH ( <low_priority_lock_wait> ) ]

    | SET
        (
            [ FILESTREAM_ON =
                { partition_scheme_name | filegroup | "default" | "NULL" } ]
            | SYSTEM_VERSIONING =
                  {
                    OFF
                  | ON
                      [ ( HISTORY_TABLE = schema_name . history_table_name
                          [, DATA_CONSISTENCY_CHECK = { ON | OFF } ]
                          [, HISTORY_RETENTION_PERIOD =
                          {
                              INFINITE | number {DAY | DAYS | WEEK | WEEKS
                  | MONTH | MONTHS | YEAR | YEARS }
                          }
                          ]
                        )
                      ]
                  }
            | DATA_DELETION =
                {
                      OFF
                    | ON
                        [(  [ FILTER_COLUMN = column_name ]
                            [, RETENTION_PERIOD = { INFINITE | number { DAY | DAYS | WEEK | WEEKS
                                    | MONTH | MONTHS | YEAR | YEARS } } ]
                        )]
                    } )
    | REBUILD
      [ [PARTITION = ALL]
        [ WITH ( <rebuild_option> [ ,...n ] ) ]
      | [ PARTITION = partition_number
           [ WITH ( <single_partition_rebuild_option> [ ,...n ] ) ]
        ]
      ]

    | <table_option>
    | <filetable_option>
    | <stretch_configuration>
}
[ ; ]

-- ALTER TABLE options

<column_set_definition> ::=
    column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS

<drop_clustered_constraint_option> ::=
    {
        MAXDOP = max_degree_of_parallelism
      | ONLINE = { ON | OFF }
      | MOVE TO
         { partition_scheme_name ( column_name ) | filegroup | "default" }
    }
<table_option> ::=
    {
        SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )
    }

<filetable_option> ::=
    {
       [ { ENABLE | DISABLE } FILETABLE_NAMESPACE ]
       [ SET ( FILETABLE_DIRECTORY = directory_name ) ]
    }

<stretch_configuration> ::=
    {
      SET (
        REMOTE_DATA_ARCHIVE
        {
            = ON (<table_stretch_options>)
          | = OFF_WITHOUT_DATA_RECOVERY ( MIGRATION_STATE = PAUSED )
          | ( <table_stretch_options> [, ...n] )
        }
            )
    }

<table_stretch_options> ::=
    {
     [ FILTER_PREDICATE = { null | table_predicate_function } , ]
       MIGRATION_STATE = { OUTBOUND | INBOUND | PAUSED }
    }

<single_partition_rebuild__option> ::=
{
      SORT_IN_TEMPDB = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE}
    | ONLINE = { ON [( <low_priority_lock_wait> ) ] | OFF }
}

<low_priority_lock_wait>::=
{
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ],
        ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
}

Para obter mais informações, consulte:

• ALTERAR MESA column_constraint
• ALTERAR TABELA column_definition
• ALTERAR MESA computed_column_definition
• ALTER TABLE index_option
• ALTER TABLE table_constraint

Sintaxe para tabelas com otimização de memória

ALTER TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
{
    ALTER COLUMN column_name
    {
        [ type_schema_name. ] type_name
            [ (
                {
                   precision [ , scale ]
                }
            ) ]
        [ COLLATE collation_name ]
        [ NULL | NOT NULL ]
    }

    | ALTER INDEX index_name
    {
        [ type_schema_name. ] type_name
        REBUILD
        [ [ NONCLUSTERED ] WITH ( BUCKET_COUNT = bucket_count )
        ]
    }

    | ADD
    {
        <column_definition>
      | <computed_column_definition>
      | <table_constraint>
      | <table_index>
      | <column_index>
    } [ ,...n ]

    | DROP
     [ {
         CONSTRAINT [ IF EXISTS ]
         {
              constraint_name
          } [ ,...n ]
        | INDEX [ IF EXISTS ]
      {
         index_name
       } [ ,...n ]
          | COLUMN [ IF EXISTS ]
          {
              column_name
          } [ ,...n ]
          | PERIOD FOR SYSTEM_TIME
     } [ ,...n ] ]
    | [ WITH { CHECK | NOCHECK } ] { CHECK | NOCHECK } CONSTRAINT
        { ALL | constraint_name [ ,...n ] }

    | { ENABLE | DISABLE } TRIGGER
        { ALL | trigger_name [ ,...n ] }

    | SWITCH [ [ PARTITION ] source_partition_number_expression ]
        TO target_table
        [ PARTITION target_partition_number_expression ]
        [ WITH ( <low_priority_lock_wait> ) ]

}
[ ; ]

-- ALTER TABLE options

< table_constraint > ::=
 [ CONSTRAINT constraint_name ]
{
   {PRIMARY KEY | UNIQUE }
     {
       NONCLUSTERED (column [ ASC | DESC ] [ ,... n ])
       | NONCLUSTERED HASH (column [ ,... n ] ) WITH ( BUCKET_COUNT = bucket_count )
     }
    | FOREIGN KEY
        ( column [ ,...n ] )
        REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ]
    | CHECK ( logical_expression )
}

<column_index> ::=
  INDEX index_name
{ [ NONCLUSTERED ] | [ NONCLUSTERED ] HASH WITH (BUCKET_COUNT = bucket_count) }

<table_index> ::=
  INDEX index_name
{ [ NONCLUSTERED ] HASH (column [ ,... n ] ) WITH (BUCKET_COUNT = bucket_count)
  | [ NONCLUSTERED ] (column [ ASC | DESC ] [ ,... n ] )
      [ ON filegroup_name | default ]
  | CLUSTERED COLUMNSTORE [ WITH ( COMPRESSION_DELAY = { 0 | delay [MINUTES] } ) ]
      [ ON filegroup_name | default ]
}

Sintaxe do Azure Synapse Analytics e do Parallel Data Warehouse

ALTER TABLE { database_name.schema_name.source_table_name | schema_name.source_table_name | source_table_name }
{
    ALTER COLUMN column_name
        {
            type_name [ ( precision [ , scale ] ) ]
            [ COLLATE Windows_collation_name ]
            [ NULL | NOT NULL ]
        }
    | ADD { <column_definition> | <column_constraint> FOR column_name} [ ,...n ]
    | DROP { COLUMN column_name | [CONSTRAINT] constraint_name } [ ,...n ]
    | REBUILD {
            [ PARTITION = ALL [ WITH ( <rebuild_option> ) ] ]
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_option> ] ]
      }
    | { SPLIT | MERGE } RANGE (boundary_value)
    | SWITCH [ PARTITION source_partition_number
        TO target_table_name [ PARTITION target_partition_number ] [ WITH ( TRUNCATE_TARGET = ON | OFF ) ] ]
}
[ ; ]

<column_definition>::=
{
    column_name
    type_name [ ( precision [ , scale ] ) ]
    [ <column_constraint> ]
    [ COLLATE Windows_collation_name ]
    [ NULL | NOT NULL ]
}

<column_constraint>::=
    [ CONSTRAINT constraint_name ]
    {
        DEFAULT constant_expression
        | PRIMARY KEY NONCLUSTERED (column_name [ ,... n ]) NOT ENFORCED -- Applies to Azure Synapse Analytics only
        | UNIQUE (column_name [ ,... n ]) NOT ENFORCED -- Applies to Azure Synapse Analytics only
    }
<rebuild_option > ::=
{
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
        [ ON PARTITIONS ( {<partition_number> [ TO <partition_number>] } [ , ...n ] ) ]
    | XML_COMPRESSION = { ON | OFF }
        [ ON PARTITIONS ( {<partition_number> [ TO <partition_number>] } [ , ...n ] ) ]
}

<single_partition_rebuild_option > ::=
{
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
}

Sintaxe para armazém na malha

ALTER TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
{
  ADD  { column_name <data_type> [COLLATE collation_name] [ <column_options> ] } [ ,...n ]
| ADD { <column_constraint> FOR column_name} [ ,...n ]
| DROP { COLUMN column_name | [CONSTRAINT] constraint_name } [ ,...n ]
}
[ ; ]

<column_options> ::=
[ NULL ] -- default is NULL

<data type> ::=
datetime2 ( n )
| date
| time ( n )
| float [ ( n ) ]
| real [ ( n ) ]
| decimal [ ( precision [ , scale ] ) ]
| numeric [ ( precision [ , scale ] ) ]
| bigint
| int
| smallint
| bit
| varchar [ ( n ) ]
| char [ ( n ) ]
| varbinary [ ( n ) ]
| uniqueidentifier

<column_constraint>::=
    [ CONSTRAINT constraint_name ]
    {
       PRIMARY KEY NONCLUSTERED (column_name [ ,... n ]) NOT ENFORCED
        | UNIQUE NONCLUSTERED (column_name [ ,... n ]) NOT ENFORCED
    | FOREIGN KEY
        ( column [ ,...n ] )
        REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ] NOT ENFORCED
    }

Arguments

database_name

O nome do banco de dados em que a tabela foi criada.

schema_name

O nome do esquema ao qual a tabela pertence.

table_name

O nome da tabela a ser alterada. Se a tabela não estiver no banco de dados atual ou não constar no esquema pertencente ao usuário atual, especifique explicitamente o banco de dados e o esquema.

ALTER COLUMN

Especifica que a coluna nomeada será alterada ou modificada.

A coluna modificada não pode ser:

• Uma coluna com um tipo de dados timestamp.

• O ROWGUIDCOL para a tabela.

• Uma coluna computada ou usada em uma coluna computada.

• Usado em estatísticas geradas pela instrução CREATE STATISTICS . Os usuários precisam executar DROP STATISTICS para remover as estatísticas antes ALTER COLUMN que possam ter êxito. Execute esta consulta para obter todas as colunas de estatísticas e as estatísticas criadas pelo usuário para uma tabela.

  SELECT s.name AS statistics_name,
         c.name AS column_name,
         sc.stats_column_id
  FROM sys.stats AS s
       INNER JOIN sys.stats_columns AS sc
           ON s.object_id = sc.object_id
          AND s.stats_id = sc.stats_id
       INNER JOIN sys.columns AS c
           ON sc.object_id = c.object_id
          AND c.column_id = sc.column_id
  WHERE s.object_id = OBJECT_ID('<table_name>');
  

  Note

  As estatísticas geradas automaticamente pelo otimizador de consulta são descartadas automaticamente por ALTER COLUMN.

• Usado em uma PRIMARY KEY ou [FOREIGN KEY] REFERENCES restrição.

• Usado em uma CHECK ou UNIQUE restrição. No entanto, é permitido alterar o comprimento de uma coluna de comprimento variável usada em uma CHECK ou UNIQUE restrição.

• Associada com uma definição padrão. Entretanto, o comprimento, a precisão e a escala de uma coluna poderão ser alterados se o tipo de dados não for modificado.

O tipo de dados das colunas text, ntext e image pode ser alterado somente das seguintes maneiras:

• text a varchar(max) , nvarchar(max) ou xml
• ntext a varchar(max) , nvarchar(max) ou xml
• image a varbinary(max)

Algumas alterações de tipo de dados podem causar uma alteração nos dados. Por exemplo, alterar uma coluna nchar ou nvarchar para char ou varchar pode levar à conversão de caracteres estendidos. Para obter mais informações, veja CAST e CONVERT (Transact-SQL). Reduzir a precisão ou escala de uma coluna pode causar o truncamento de dados.

Ao usar o Always Encrypted (sem enclaves seguros), se a coluna que está sendo modificada for criptografada, ENCRYPTED WITHvocê poderá alterar o tipo de dados para um tipo de dados compatível (como INT para BIGINT), mas não é possível alterar nenhuma configuração de criptografia.

Ao usar o Always Encrypted com enclaves seguros, você poderá alterar qualquer configuração de criptografia, se a chave de criptografia de coluna que protege a coluna (e a nova chave de criptografia de coluna, se estiver alterando a chave) for compatível com cálculos de enclave (criptografada com chaves mestras de coluna habilitadas para enclave). Para conhecer detalhes, consulte Always Encrypted com enclaves seguros.

Quando você modifica uma coluna, o Mecanismo de Banco de Dados controla cada modificação adicionando uma linha em uma tabela do sistema e marcando a modificação da coluna anterior como uma coluna descartada. No caso raro de você modificar uma coluna muitas vezes, o Mecanismo de Banco de Dados pode atingir o limite de tamanho do registro. Se isso acontecer, você receberá o erro 511 ou 1708. Para evitar esses erros, recompile o índice clusterizado na tabela periodicamente ou reduza o número de modificações de coluna.

column_name

O nome da coluna a ser alterada, adicionada ou removida. O máximo para nome_da_coluna é 128 caracteres. Para novas colunas, é possível omitir nome_da_coluna para colunas criadas com um tipo de dados timestamp. O nome timestamp será usado se nenhum nome_da_coluna for especificado para uma coluna de tipo de dados timestamp.

[ type_schema_name. ] type_name

O novo tipo de dados da coluna alterada ou o tipo de dados da coluna adicionada. Não é possível especificar type_name para colunas de tabelas particionadas existentes. type_name pode ser qualquer um dos seguintes tipos:

• Um tipo de dados de sistema SQL Server.
• Um tipo de dados do alias com base em um tipo de dados de sistema SQL Server. Crie tipos de dados de alias com a CREATE TYPE instrução antes que eles possam ser usados em uma definição de tabela.
• Um tipo definido pelo usuário .NET Framework e o esquema ao qual ele pertence. Você cria tipos definidos pelo usuário com a CREATE TYPE instrução antes que eles possam ser usados em uma definição de tabela.

Os seguintes são critérios para type_name de uma coluna alterada:

• O tipo de dados anterior deve ser implicitamente conversível para o novo tipo de dados.
• type_name não pode ser timestamp.
• ANSI_NULL padrões estão sempre ativados; ALTER COLUMNse não for especificado, a coluna será anulável.
• ANSI_PADDING preenchimento é sempre ON para ALTER COLUMN.
• Se a coluna modificada for uma coluna de identidade, new_data_type deverá ser um tipo de dados compatível com a propriedade de identidade.
• A configuração atual para SET ARITHABORT a qual é ignorada. ALTER TABLE opera como se ARITHABORT estivesse definido ONcomo .

precision

A precisão do tipo de dados especificado. Para obter mais informações sobre valores de precisão válidos, consulte Precisão, escala e comprimento.

scale

A escala do tipo de dados especificado. Para obter mais informações sobre valores de escala válidos, consulte Precisão, escala e comprimento.

max

Aplica-se apenas aos tipos de dados varchar, nvarchar e varbinary para armazenar 2^31-1 bytes de caractere, dados binários e dados Unicode.

xml_schema_collection

Aplica-se a: SQL Server e Banco de Dados SQL do Azure.

Aplica-se apenas ao tipo de dados xml para associar um esquema XML ao tipo. Antes de digitar uma coluna xml em uma coleção de esquema, crie primeiro a coleção de esquema no banco de dados usando CREATE XML SCHEMA COLLECTION.

< collation_name>

Especifica a nova ordenação para a coluna alterada. Se não for especificado, à coluna será atribuída a ordenação padrão do banco de dados. O nome da ordenação pode ser um nome de ordenação do Windows ou um nome de ordenação SQL. Para obter uma lista e mais informações, consulte o nome da ordenação do Windows e o nome da ordenação do SQL Server.

A COLLATE cláusula altera as ordenações somente de colunas dos tipos de dados char, varchar, nchar e nvarchar . Para alterar a ordenação de uma coluna de tipo de dados de alias definida pelo usuário, use instruções separadas ALTER TABLE para alterar a coluna para um tipo de dados do sistema DO SQL Server. Depois, altere novamente a coluna e a ordenação para um tipo de dados de alias.

ALTER COLUMN não poderá ter uma alteração de ordenação se uma ou mais das seguintes condições existirem:

• Uma CHECK restrição, FOREIGN KEY restrição ou colunas computadas fazem referência à coluna alterada.
• Qualquer índice, estatística ou índice de texto completo é criado na coluna. As estatísticas criadas automaticamente na coluna alterada serão descartadas se a ordenação da coluna for alterada.
• Uma exibição ou função associada a esquema faz referência à coluna.

Para obter mais informações sobre ordenações com suporte, consulte COLLATE.

NULL | NÃO NULL

Especifica se a coluna pode aceitar valores nulos. As colunas que não permitem valores nulos são adicionadas ALTER TABLE somente se tiverem um padrão especificado ou se a tabela estiver vazia. Você só poderá especificar NOT NULL colunas computadas se também tiver especificado PERSISTED. Se a nova coluna permitir valores nulos e você não especificar um padrão, a nova coluna conterá um valor nulo para cada linha da tabela. Se a nova coluna permitir valores nulos e você adicionar uma definição padrão com a nova coluna, você poderá usar WITH VALUES para armazenar o valor padrão na nova coluna para cada linha existente na tabela.

Se a nova coluna não permitir valores nulos e a tabela não estiver vazia, você precisará adicionar uma DEFAULT definição com a nova coluna. E a nova coluna será carregada automaticamente com o valor padrão nas novas colunas em cada linha existente.

Você pode especificar NULLALTER COLUMN para forçar uma NOT NULL coluna a permitir valores nulos, exceto para colunas em PRIMARY KEY restrições. Você só poderá especificar NOT NULLALTER COLUMN se a coluna não contiver valores nulos. Os valores nulos devem ser atualizados para algum valor antes do ALTER COLUMNNOT NULL permitido, por exemplo:

UPDATE MyTable SET NullCol = N'some_value' WHERE NullCol IS NULL;

ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR (20) NOT NULL;

Quando você cria ou altera uma tabela com as instruções ou CREATE TABLE instruçõesALTER TABLE, as configurações de banco de dados e sessão influenciam e possivelmente substituem a nulidade do tipo de dados usado em uma definição de coluna. Certifique-se de que você sempre defina explicitamente uma coluna como NULL ou NOT NULL para colunas não computadas.

Se você adicionar uma coluna com um tipo de dados definido pelo usuário, defina a coluna com a mesma nulidade que o tipo de dados definido pelo usuário. Além disso, especifique um valor padrão para a coluna. Para obter mais informações, confira CREATE TABLE.

[ {ADD | LARGA} ROWGUIDCOL ]

Aplica-se a: SQL Server e Banco de Dados SQL do Azure.

Especifica que a ROWGUIDCOL propriedade é adicionada ou removida da coluna especificada. ROWGUIDCOL indica que a coluna é uma coluna GUID de linha. Você pode definir apenas uma coluna uniqueidentifier por tabela como a ROWGUIDCOL coluna. E você só pode atribuir a ROWGUIDCOL propriedade a uma coluna uniqueidentifier . Você não pode atribuir ROWGUIDCOL a uma coluna de um tipo de dados definido pelo usuário.

ROWGUIDCOL não impõe a exclusividade dos valores armazenados na coluna e não gera valores automaticamente para novas linhas inseridas na tabela. Para gerar valores exclusivos para cada coluna, use a função NEWID() ou NEWSEQUENTIALID() em instruções INSERT. Ou especifique a função NEWID() ou NEWSEQUENTIALID() como padrão para a coluna.

[ {ADD | DROP} PERSISTIU ]

Especifica que a PERSISTED propriedade é adicionada ou removida da coluna especificada. A coluna deve ser uma coluna computada definida com uma expressão determinista. Para colunas especificadas como PERSISTED, o Mecanismo de Banco de Dados armazena fisicamente os valores computados na tabela e atualiza os valores quando quaisquer outras colunas das quais a coluna computada depende são atualizadas. Marcando uma coluna computada como PERSISTED, você pode criar índices em colunas computadas definidas em expressões determinísticas, mas não precisas. Para obter mais informações, consulte Índices em colunas computadas.

SET QUOTED_IDENTIFIER deve ser ON quando você estiver criando ou alterando índices em colunas computadas ou exibições indexadas. Para saber mais, confira SET QUOTED_IDENTIFIER.

Qualquer coluna computada usada como uma coluna de particionamento de uma tabela particionada deve ser explicitamente marcada PERSISTED.

NÃO DROP PARA REPLICAÇÃO

Aplica-se a: SQL Server e Banco de Dados SQL do Azure.

Especifica que os valores são incrementados em colunas de identidade quando os agentes de replicação executam operações de inserção. Será possível especificar essa cláusula somente se nome_da_coluna for uma coluna de identidade.

SPARSE

Indica que a coluna é uma coluna esparsa. O armazenamento de colunas esparsas é otimizado para obter valores nulos. Não é possível definir colunas esparsas como NOT NULL. Ao converter uma coluna de esparsa para não esparsa, ou vice-versa, essa opção bloqueia a tabela durante a execução do comando. Talvez seja necessário usar a REBUILD cláusula para recuperar qualquer economia de espaço. Para obter restrições adicionais e mais informações sobre colunas esparsas, consulte Usar colunas esparsas.

ADICIONAR MASCARADO COM ( FUNÇÃO = 'mask_function')

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores e Banco de Dados SQL do Azure.

Especifica uma máscara de dados dinâmicos. mask_function é o nome da função de mascaramento com os parâmetros apropriados. Três funções estão disponíveis:

• default()
• email()
• partial()
• random()

Requer ALTER ANY MASK permissão.

Para remover uma máscara, use DROP MASKED. Para parâmetros de função, consulte Máscara de dados dinâmicos.

Adicionar e soltar uma máscara exige permissão ALTER ANY MASK .

WITH ( ONLINE = ON | OFF) <conforme se aplica a alterar uma coluna>

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores e Banco de Dados SQL do Azure.

Permite que muitas ações de alteração de coluna sejam executadas enquanto a tabela permanece disponível. O padrão é OFF. É possível executar a alteração da coluna online para as alterações de coluna relacionadas ao tipo de dados, comprimento da coluna ou precisão, nulidade, dispersão e ordenação.

A alter column online permite que o usuário criado e a autoestatística referenciem a coluna alterada durante a operação, o que permite que as ALTER COLUMN consultas executem normalmente. No final da operação, as estatísticas automáticas que fazem referência à coluna são descartadas e as estatísticas criadas pelo usuário são invalidadas. O usuário deve atualizar manualmente as estatísticas geradas pelo usuário após a conclusão da operação. Se a coluna fizer parte de uma expressão de filtro para estatísticas ou índices, você não poderá executar uma operação de alteração de coluna.

• Enquanto a operação de alter column online está em execução, qualquer operação DDL que possa depender dessa coluna (como criar ou modificar índices, exibições etc.) é bloqueada ou falha com um erro apropriado. Esse comportamento garante que a alteração online de coluna não falhe devido a dependências introduzidas enquanto a operação estiver em execução.

• Não há suporte para alterar uma coluna de NOT NULL para NULL uma operação online quando a coluna alterada é referenciada por índices não clusterizados.

• Não há suporte para ALTER online quando a coluna é referenciada por uma restrição de verificação e a ALTER operação está restringindo a precisão da coluna (numérica ou datetime).

• A opção WAIT_AT_LOW_PRIORITY não pode ser usada com a alteração online de coluna.

• ALTER COLUMN ... ADD/DROP PERSISTED não é compatível para a coluna de alteração online.

• ALTER COLUMN ... ADD/DROP ROWGUIDCOL/NOT FOR REPLICATION não é afetado pela coluna de alteração online.

• A alteração online de coluna não dá suporte à alteração de uma tabela em que o controle de alterações está habilitado ou é um publicador de replicação de mesclagem.

• A alteração online de coluna não dá suporte à alteração de ou para tipos de dados CLR.

• A alteração online de coluna não dá suporte à alteração para um tipo de dados XML que tem uma coleção de esquema diferente da coleção de esquema atual.

• A alteração online de coluna não reduz as restrições nem determina quando uma coluna pode ser alterada. Referências de índice/estatísticas e assim por diante podem causar falha na alteração.

• A alteração online de coluna não dá para suporte a alteração de mais de uma coluna simultaneamente.

• A alteração online de coluna não tem nenhum efeito no caso da tabela temporal com versão do sistema. ALTER A coluna não é executada como online, independentemente do valor especificado para ONLINE a opção.

A alteração online de coluna tem requisitos, restrições e funcionalidades similares à recompilação de índice online, o que inclui:

• A recompilação de índice online não é compatível quando a tabela contém colunas LOB ou filestream herdadas ou quando a tabela tem um índice columnstore. As mesmas limitações se aplicam à alteração de coluna online.
• Uma coluna existente que está sendo alterada requer duas vezes a alocação de espaço; para a coluna original e para a coluna oculta recém-criada.
• A estratégia de bloqueio durante uma operação de alteração online de coluna segue o mesmo padrão de bloqueio usado para criação de índice online.

COM XEQUE | COM NOCHECK

Especifica se os dados na tabela são ou não validados em relação a uma restrição ou novamente adicionada ou FOREIGN KEY reabilitadaCHECK. Se você não especificar, WITH CHECK será assumido para novas restrições e WITH NOCHECK será assumido para restrições reabilitadas.

Se você não quiser verificar novas CHECK ou FOREIGN KEY restrições em relação aos dados existentes, use WITH NOCHECK. Não recomendamos fazer isso, com raríssimas exceções. A nova restrição é avaliada em todas as atualizações de dados posteriores. Qualquer violação de restrição suprimida quando WITH NOCHECK a restrição é adicionada pode fazer com que atualizações futuras falhem se atualizarem linhas com dados que não seguem a restrição. O otimizador de consulta não considera restrições definidas WITH NOCHECK. Essas restrições são ignoradas até que sejam reabilitadas, usando ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL. Para obter mais informações, consulte Desabilitar restrições de chave estrangeira com instruções INSERT e UPDATE.

ALTER INDEX index_name

Especifica que o número de buckets para index_name foi alterado ou modificado.

A sintaxe ALTER TABLE ... ADD/DROP/ALTER INDEX tem suporte apenas para tabelas com otimização de memória.

ADD

Especifica que uma ou mais definições de coluna, definições de coluna computada ou restrições de tabela são adicionadas. Ou as colunas que o sistema usa para controle de versão do sistema são adicionadas. Para tabelas com otimização de memória, é possível adicionar um índice.

PONTO PARA SYSTEM_TIME (system_start_time_column_name, system_end_time_column_name)

Aplica-se a: SQL Server 2017 (14.x) e versões posteriores e Banco de Dados SQL do Azure.

Especifica os nomes das colunas que o sistema usará para registrar o período em que um registro é válido. Você pode especificar colunas existentes ou criar novas colunas como parte do ADD PERIOD FOR SYSTEM_TIME argumento. Configure as colunas com o tipo de dados datetime2 e defina-as como NOT NULL. Se você definir uma coluna de período como NULL, um erro resultará. Você pode definir um column_constraint e/ou especificar valores padrão para colunas para as colunas system_start_time e system_end_time. Consulte o Exemplo A nos exemplos de Controle de versão do sistema a seguir, que demonstram o uso de um valor padrão para a coluna system_end_time.

Use esse argumento com o SET SYSTEM_VERSIONING argumento para tornar uma tabela existente uma tabela temporal. Para obter mais informações, consulte tabelas temporais e Introdução às tabelas temporais.

A partir do SQL Server 2017 (14.x), os usuários podem marcar uma ou ambas as colunas de período com HIDDEN sinalizador para ocultar implicitamente essas colunas de modo que SELECT * FROM <table_name> não retornem um valor para as colunas. Por padrão, as colunas de período não ficam ocultas. Para serem usadas, colunas ocultas devem ser explicitamente incluídas em todas as consultas que fazem referência direta à tabela temporal.

DROP

Especifica que uma ou mais definições de coluna, definições de coluna computada ou restrições de tabela são removidas ou remove a especificação das colunas que o sistema usa para controle de versão do sistema.

RESTRIÇÃO constraint_name

Especifica que constraint_name é removida da tabela. Várias restrições podem ser listadas.

O nome definido pelo usuário ou fornecido pelo sistema das restrições pode ser determinado ao consultar as exibições do catálogo sys.check_constraint, sys.default_constraints, sys.key_constraints e sys.foreign_keys.

Uma PRIMARY KEY restrição não poderá ser descartada se houver um índice XML na tabela.

INDEX index_name

Especifica que index_name foi removido da tabela.

A sintaxe ALTER TABLE ...ADD/DROP/ALTER INDEXtem suporte apenas para tabelas com otimização de memória.

COLUMN_NAME DE COLUNA

Especifica que constraint_name ou column_name é removida da tabela. Várias colunas podem ser listadas.

Uma coluna não pode ser descartada quando for:

• Usado em um índice, seja como uma coluna de chave ou como um INCLUDE
• Usado em uma restrição ou CHECK em FOREIGN KEYUNIQUEuma PRIMARY KEYrestrição.
• Associado a um padrão definido com a DEFAULT palavra-chave ou associado a um objeto padrão.
• Associada a uma regra.

PONTO PARA SYSTEM_TIME

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores e Banco de Dados SQL do Azure.

Descarta a especificação das colunas que o sistema usa para controle de versão do sistema.

COM <drop_clustered_constraint_option>

Especifica que há uma ou mais opções de descarte de restrição clusterizada definidas.

MAXDOP = max_degree_of_parallelism

Aplica-se a: SQL Server e Banco de Dados SQL do Azure.

Substitui a opção de configuração de grau máximo de paralelismo apenas para a duração da operação. Para obter mais informações, consulte a configuração do servidor: grau máximo de paralelismo.

Use a opção MAXDOP para limitar o número de processadores usados na execução do plano paralelo. O máximo é de 64 processadores.

max_degree_of_parallelism pode ser um dos seguintes valores:

• 1

  Suprime a geração de plano paralelo.

• >1

  Restringe o número máximo de processadores usados em uma operação de índice paralela ao número especificado.

• 0 (padrão)

  Usa o número real de processadores ou menos com base na carga de trabalho atual do sistema.

Para obter mais informações, consulte Configurar operações de índice paralelo.

ONLINE = { ON | OFF } <conforme se aplica a drop_clustered_constraint_option>

Especifica se as tabelas subjacentes e os índices associados estão disponíveis para consultas e modificação de dados durante a operação de índice. O padrão é OFF. Você pode executar REBUILD como uma ONLINE operação.

• ON

  Bloqueios de tabela de longa duração não são mantidos durante a operação do índice. Durante a fase principal da operação de índice, somente um bloqueio de Compartilhamento de Intenção (IS) é mantido na tabela de origem. Esse comportamento permite a continuação de consultas ou atualizações feitas na tabela e nos índices subjacentes. No início da operação, um bloqueio compartilhado (S) é mantido no objeto de origem por um curto período. Ao término da operação, por um curto período, um bloqueio S (Compartilhado) será adquirido na origem se um índice não clusterizado estiver sendo criado. Ou, um bloqueio Sch-M (Modificação de Esquema) é adquirido quando um índice clusterizado é criado ou descartado online e quando um índice clusterizado ou não clusterizado está sendo recriado. ONLINE não pode ser definido ON como quando um índice está sendo criado em uma tabela temporária local. Apenas a operação de reconstrução de heap de thread único é permitida.

  Para executar a recompilação de índice online ou DDL SWITCH , todas as transações de bloqueio ativas em execução em uma tabela específica devem ser concluídas. Ao executar, a SWITCH operação ou recompilação impede que novas transações sejam iniciadas e possam afetar significativamente a taxa de transferência da carga de trabalho e atrasar temporariamente o acesso à tabela subjacente.

• OFF

  Os bloqueios de tabela são aplicados enquanto durar a operação de índice. Uma operação de índice offline que cria, recria ou cancela um índice clusterizado ou recria ou cancela um índice não clusterizado, adquire um bloqueio de esquema de modificação (Sch-M) na tabela. Isso impede o acesso de todos os usuários à tabela subjacente enquanto durar a operação. Uma operação de índice offline que cria um índice não clusterizado adquire um bloqueio Compartilhado (S) na tabela. Esse bloqueio impede atualizações para a tabela subjacente, mas permite operações de leitura, como SELECT instruções. Permite operações de reconstrução de heap multi-threaded.

  Para obter mais informações, consulte Como funcionam as operações de índice online.

  Note

  As operações de índice online não estão disponíveis em todas as edições do SQL Server. Para obter mais informações, confira Edições e recursos com suporte do SQL Server 2022.

MOVE TO { partition_scheme_name(column_name [ ,... n ] ) | grupo de arquivos | "default" }

Aplica-se a: SQL Server e Banco de Dados SQL do Azure.

Especifica o local para onde mover as linhas de dados atualmente no nível folha do índice clusterizado. A tabela é movida para o novo local. Esta opção se aplica apenas a restrições que criam um índice clusterizado.

{ CHECK | RESTRIÇÃO NOCHECK }

Especifica que constraint_name está habilitado ou desabilitado. Essa opção só pode ser usada com FOREIGN KEY e CHECK restrições. Quando NOCHECK é especificada, a restrição é desabilitada e inserções ou atualizações futuras na coluna não são validadas em relação às condições de restrição. DEFAULT, PRIMARY KEYe UNIQUE as restrições não podem ser desabilitadas.

• ALL

  Especifica que todas as restrições estão desabilitadas com a opção NOCHECK ou habilitadas com a opção CHECK .

{ ENABLE | GATILHO DISABLE }

Especifica que trigger_name está habilitado ou desabilitado. Quando um gatilho é desabilitado, ele ainda permanece definido para a tabela. No entanto, quando INSERT, UPDATEou DELETE instruções são executadas na tabela, as ações no gatilho não são executadas até que o gatilho seja habilitado novamente.

• ALL

  Especifica que todos os gatilhos na tabela são habilitados ou desabilitados.

• trigger_name

  Especifica o nome do gatilho a ser desabilitado ou habilitado.

{ HABILITAR | DESATIVAR } CHANGE_TRACKING

Aplica-se a: SQL Server e Banco de Dados SQL do Azure.

Especifica se controle de alterações está habilitado ou desabilitado para a tabela. Por padrão, o controle de alterações está desabilitado.

Essa opção só estará disponível quando o controle de alterações estiver habilitado para o banco de dados. Para obter mais informações, consulte as opções ALTER DATABASE SET.

Para habilitar o controle de alterações, a tabela deve ter uma chave primária.

COM ( TRACK_COLUMNS_UPDATED = { ON | DESLIGADO } )

Aplica-se a: SQL Server e Banco de Dados SQL do Azure.

Especifica se o Mecanismo de Banco de Dados controla quais colunas com alteração controlada foram atualizadas. O valor padrão é OFF.

SWITCH [ PARTITION source_partition_number_expression ] TO [ schema_name. ] target_table [ PARTITION target_partition_number_expression ]

Aplica-se a: SQL Server e Banco de Dados SQL do Azure.

Alterna um bloco de dados em um dos seguintes modos:

• Reatribui todos os dados de uma tabela como uma partição para uma tabela particionada já existente.
• Alterna uma partição de uma tabela particionada para outra.
• Reatribui todos os dados em uma partição de uma tabela particionada para uma tabela não particionada existente.

Se tabela for uma tabela particionada, será preciso especificar expressão_do_número_da_partição_de_origem. Se tabela_de_destino for uma tabela particionada, será preciso especificar expressão_do_número_da_partição_de_destino. Na reatribuição dos dados de uma tabela com uma partição para uma tabela particionada já existente ou alternando uma partição de uma tabela particionada para outra, a partição de destino deve existir e estar vazia.

Na reatribuição dos dados de uma partição para formar uma tabela única, a tabela de destino já deve existir e estar vazia. A tabela de origem ou a partição e a tabela de destino ou a partição devem estar localizadas no mesmo grupo de arquivos. Os índices correspondentes ou as partições de índice também devem estar localizadas no mesmo grupo de arquivos. Muitas restrições adicionais são aplicadas para alternância de partições. tabela e tabela_de_destino não podem ser iguais. target_table pode ser um identificador de várias partes.

source_partition_number_expression e target_partition_number_expression são expressões de constante que podem fazer referência a variáveis e funções. Eles incluem variáveis de tipo definidas pelo usuário e funções definidas pelo usuário. Eles não podem referenciar expressões Transact-SQL.

Uma tabela particionada com um índice clusterizado columnstore se comporta como um heap particionado:

• A chave primária deve incluir a chave de partição.
• Um índice exclusivo deve incluir a chave de partição. Incluir a chave de partição com um índice exclusivo existente pode alterar a exclusividade.
• Para mudar as partições, todos os índices não clusterizados devem incluir a chave de partição.

Para SWITCH restrição ao usar a replicação, consulte Replicar Tabelas e Índices Particionados.

Os índices columnstore não clusterizados foram criados em um formato somente leitura antes do SQL Server 2016 (13.x) e para o Banco de Dados SQL antes da versão V12. Você deve recompilar índices columnstore não clusterizados para o formato atual (que é atualizável) antes que qualquer PARTITION operação possa ser executada.

Limitations

Se ambas as tabelas forem particionadas de forma idêntica, incluindo índices não clusterizados e a tabela de destino não tiver índices não clusterizados, você poderá receber um erro 4907.

Exemplo de saída:

Msg 4907, Level 16, State 1, Line 38
'ALTER TABLE SWITCH' statement failed. The table 'MyDB.dbo.PrtTable1' has 4 partitions while index 'MS1' has 6 partitions.

SET ( FILESTREAM_ON = { partition_scheme_name | filestream_filegroup_name | "padrão" | "NULL" })

Aplica-se a: SQL Server. Banco de Dados SQL do Azure não dá suporte a FILESTREAM.

Especifica onde os dados FILESTREAM são armazenados.

ALTER TABLE com a SET FILESTREAM_ON cláusula for bem-sucedida somente se a tabela não tiver colunas FILESTREAM. Você pode adicionar colunas FILESTREAM usando uma segunda ALTER TABLE instrução.

Se você especificar partition_scheme_name, as regras para CREATE TABLE se aplicarão. A tabela já deve estar particionada para dados de linha e seu esquema de partição usa a mesma função de partição e colunas que o esquema de partição FILESTREAM.

filestream_filegroup_name especifica o nome de um grupo de arquivos FILESTREAM. O grupo de arquivos deve ter um arquivo definido para o grupo de arquivos usando uma instrução CREATE DATABASE ou ALTER DATABASE ou você receberá um erro.

"default" especifica o grupo de arquivos FILESTREAM com o DEFAULT conjunto de propriedades. Se não houver um grupo de arquivos FILESTREAM, você receberá um erro.

"NULL" especifica que todas as referências a grupos de arquivos FILESTREAM para a tabela são removidas. Todas as colunas FILESTREAM devem ser descartadas primeiro. Use SET FILESTREAM_ON = "NULL" para excluir todos os dados FILESTREAM associados a uma tabela.

SET ( SYSTEM_VERSIONING = { OFF | ON [ ( HISTORY_TABLE = schema_name . history_table_name [ , DATA_CONSISTENCY_CHECK = { ON | DESLIGADO } ] ) ] } )

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores e Banco de Dados SQL do Azure.

Desabilita ou habilita o controle de versão do sistema de uma tabela. Para habilitar o controle de versão do sistema de uma tabela, o sistema verifica se o tipo de dados, a restrição de nulidade e os requisitos de restrição de chave primária para o controle de versão do sistema são atendidos. O sistema registra o histórico de cada registro na tabela com versão do sistema em uma tabela de histórico separada. Se o HISTORY_TABLE argumento não for usado, o nome dessa tabela de histórico será MSSQL_TemporalHistoryFor<primary_table_object_id>. Se a tabela de histórico não existir, o sistema gerará uma nova tabela de histórico correspondendo ao esquema da tabela atual, criando um link entre as duas tabelas e permitindo que o sistema registre o histórico de cada registro da tabela atual na tabela de histórico. Se o argumento HISTORY_TABLE for usado para criar um vínculo e usar a tabela de histórico existente, o sistema criará um vínculo entre a tabela atual e a tabela especificada. Ao criar um link para uma tabela de histórico existente, você pode optar por executar uma verificação de consistência de dados. Essa verificação de consistência de dados garante que os registros existentes não se sobreponham. A execução da verificação de consistência dos dados é o padrão. Use o argumento SYSTEM_VERSIONING = ON em uma tabela definida com a cláusula PERIOD FOR SYSTEM_TIME para transformar a tabela existente uma tabela temporal. Para obter mais informações, consulte tabelas temporais.

HISTORY_RETENTION_PERIOD = { INFINITE | number { DAY | DIAS | SEMANA | SEMANAS | MÊS | MESES | ANO | YEARS } }

Aplica-se a: SQL Server 2017 (14.x) e Banco de Dados SQL do Azure.

Especifica a retenção finita ou infinita para dados históricos em tabela temporais. Se omitido, será presumida retenção infinita.

DATA_DELETION

Aplica-se a:somente ao SQL do Azure no Edge

Habilita a limpeza baseada em política de retenção de dados antigos em tabelas de um banco de dados. Para obter mais informações, confira Habilitar e desabilitar a retenção de dados. Os parâmetros a seguir precisam ser especificados para que a retenção de dados seja habilitada.

• FILTER_COLUMN = { column_name }

  Especifica a coluna que deve ser usada para determinar se as linhas na tabela estão obsoletas ou não. Os tipos de dados a seguir são permitidos na coluna de filtro.

  • date
  • datetime
  • datetime2
  • smalldatetime
  • datetimeoffset

• RETENTION_PERIOD = { INFINITE | number { DAY | DIAS | SEMANA | SEMANAS | MÊS | MESES | ANO | YEARS } }

  Especifica a política de período de retenção para a tabela. O período de retenção é especificado como uma combinação de um valor inteiro positivo e a unidade de parte de data.

SET ( LOCK_ESCALATION = { AUTO | TABELA | DESATIVAR } )

Aplica-se a: SQL Server e Banco de Dados SQL do Azure.

Especifica os métodos permitidos de escalonamento de bloqueios para uma tabela.

• AUTO

  Essa opção permite que o Mecanismo de Banco de Dados do SQL Server selecione a granularidade do escalonamento de bloqueios apropriado para o esquema da tabela.

  • Se a tabela for particionada, o escalonamento de bloqueio será permitido para a granularidade de heap ou de árvore B (HoBT). Em outras palavras, o escalonamento tem permissão para o nível de partição. Depois que o bloqueio for escalonado para o nível hoBT, o bloqueio não será escalonado mais tarde para granularidade TABLE .

  • Se a tabela não estiver particionada, o escalonamento de bloqueio será feito para a TABLE granularidade.

• TABLE

  O escalonamento de bloqueios será feito na granularidade em nível de tabela, esteja a tabela particionada ou não. TABLE é o valor padrão.

• DISABLE

  Impede o escalonamento de bloqueios na maioria dos casos. Os bloqueios em nível de tabela não são totalmente desautorizados. Por exemplo, quando você está verificando uma tabela que não tem nenhum índice clusterizado no nível de isolamento serializável, o Mecanismo de Banco de Dados deve usar um bloqueio de tabela para proteger a integridade dos dados.

REBUILD

Use a REBUILD WITH sintaxe para recompilar uma tabela inteira, incluindo todas as partições em uma tabela particionada. Se a tabela tiver um índice clusterizado, a opção REBUILD recriará o índice clusterizado. REBUILD pode ser executado como uma ONLINE operação.

Use a REBUILD PARTITION sintaxe para recompilar uma única partição em uma tabela particionada.

PARTIÇÃO = ALL

Aplica-se a: SQL Server e Banco de Dados SQL do Azure.

Recria todas as partições ao alterar as configurações de compactação da partição.

RECONSTRUIR COM ( <rebuild_option> )

Todas as opções se aplicam a uma tabela com um índice clusterizado. Se a tabela não tiver um índice clusterizado, a estrutura de heap será afetada somente por algumas opções.

Quando uma configuração de compactação específica não é especificada com a REBUILD operação, a configuração de compactação atual para a partição é usada. Para retornar à configuração atual, consulte a coluna data_compression na exibição do catálogo sys.partitions.

Para obter descrições completas das opções de recriação, confira ALTER TABLE index_option.

DATA_COMPRESSION

Aplica-se a: SQL Server e Banco de Dados SQL do Azure.

Especifica a opção de compactação de dados para a tabela, o número de partição ou o intervalo de partições especificado. As opções são as descritas a seguir:

• NONE

  A tabela ou as partições especificadas não são compactadas. Essa opção não se aplica a tabelas columnstore.

• REMAR

  As partições de tabela ou especificadas são compactadas usando compactação de linha. Essa opção não se aplica a tabelas columnstore.

• PÁGINA

  As partições de tabela ou especificadas são compactadas usando compactação de página. Essa opção não se aplica a tabelas columnstore.

• COLUMNSTORE

  Aplica-se a: SQL Server 2014 (12.x) e versões posteriores e Banco de Dados SQL do Azure.

  Aplica-se somente a tabelas columnstore. COLUMNSTORE especifica descompactar uma partição compactada com a opção COLUMNSTORE_ARCHIVE . Quando os dados forem restaurados, eles continuarão sendo compactados por meio da compactação columnstore usada em todas as tabelas columnstore.

• COLUMNSTORE_ARCHIVE

  Aplica-se a: SQL Server 2014 (12.x) e versões posteriores e Banco de Dados SQL do Azure.

  Aplica-se a tabelas columnstore, que são armazenadas com um índice columnstore clusterizado. COLUMNSTORE_ARCHIVE compacta ainda mais a partição especificada para um tamanho menor. Use essa opção para fins de arquivamento ou em outras situações que exijam menos armazenamento e possam dispensar mais tempo para armazenamento e recuperação.

  Para recompilar várias partições ao mesmo tempo, veja index_option. Se a tabela não tiver um índice clusterizado, alterar a compactação de dados recriará o heap e os índices não clusterizados. Para obter mais informações sobre compactação, consulte Compactação de dados.

  ALTER TABLE REBUILD PARTITION WITH DATA COMPRESSION = ROW ou PAGE não é permitido no banco de dados SQL do Microsoft Fabric.

XML_COMPRESSION

Aplicável a: SQL Server 2022 (16.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure.

Especifica a opção de compactação XML para qualquer coluna de tipo de dados xml na tabela. As opções são as descritas a seguir:

• ON

  As colunas que usam o tipo de dados xml são compactadas.

• OFF

  As colunas que usam o tipo de dados xml não são compactadas.

ONLINE = { ON | OFF } <como se aplica a single_partition_rebuild_option>

Especifica se uma única partição das tabelas subjacentes e os índices associados estão disponíveis para consultas e modificação de dados durante a operação de índice. O padrão é OFF. Você pode executar REBUILD como uma ONLINE operação.

• ON

  Bloqueios de tabela de longa duração não são mantidos durante a operação do índice. Um bloqueio S na tabela é exigido no início da recompilação de índice e um bloqueio Sch-M na tabela no final da recompilação de índice online. Embora ambos os bloqueios sejam bloqueios de metadados curtos, o bloqueio Sch-M deve esperar que todas as transações de bloqueio sejam concluídas. Durante o tempo de espera, o bloqueio Sch-M bloqueia todas as transações restantes que esperam atrás desse bloqueio ao acessar a mesma tabela.

  Note

  A recompilação de índice online pode definir as opções low_priority_lock_wait descritas posteriormente nesta seção.

• OFF

  Os bloqueios de tabela são aplicados enquanto durar a operação de índice. Isso evita o acesso de todos os usuários à tabela subjacente enquanto durar a operação.

column_set_name COLUMN_SET XML PARA ALL_SPARSE_COLUMNS

Aplica-se a: SQL Server e Banco de Dados SQL do Azure.

O nome do conjunto de colunas. Um conjunto de colunas é uma representação em XML sem-tipo que combina todas as colunas esparsas de uma tabela em uma saída estruturada. Um conjunto de colunas não pode ser adicionado a uma tabela que contém colunas esparsas. Para obter mais informações sobre conjuntos de colunas, consulte Usar conjuntos de colunas.

{ HABILITAR | DESATIVAR } FILETABLE_NAMESPACE

Aplica-se a: SQL Server.

Habilita ou desabilita as restrições definidas pelo sistema em uma FileTable. Pode ser usado apenas com uma FileTable.

SET ( FILETABLE_DIRECTORY = directory_name )

Aplica-se a: SQL Server. O Banco de Dados SQL do Azure não dá suporte a FileTable.

Especifica o nome do diretório de FileTable compatível com o Windows. Esse nome deve ser exclusivo entre todos os nomes de diretórios de FileTable no banco de dados. A comparação de exclusividade não diferencia maiúsculas de minúsculas, apesar das configurações de ordenação do SQL. Pode ser usado apenas com uma FileTable.

REMOTE_DATA_ARCHIVE

Aplica-se a: SQL Server 2017 (14.x) e versões posteriores.

Habilita ou desabilita o Stretch Database para uma tabela. Saiba mais em Stretch Database.

Habilitar o Stretch Database para uma tabela

Quando você habilita Stretch para uma tabela especificando ON, também precisa especificar MIGRATION_STATE = OUTBOUND, para começar a migração de dados imediatamente, ou MIGRATION_STATE = PAUSED, para adiar a migração de dados. O valor padrão é MIGRATION_STATE = OUTBOUND. Saiba mais sobre como habilitar o Stretch para uma tabela em Habilitar o Stretch Database para uma tabela.

Prerequisites. Antes de habilitar o Stretch para uma tabela, você precisa habilitar o Stretch no servidor e no banco de dados. Saiba mais em Enable Stretch Database for a database.

Permissions. Habilitar o Stretch para um banco de dados ou uma tabela exige permissões db_owner. Habilitar o Stretch para uma tabela também requer ALTER permissões na tabela.

Desabilitar o Stretch Database para uma tabela

Quando você desabilitar o Stretch para uma tabela, tem duas opções para os dados remotos que já foram migrados para o Azure. Saiba mais em Desabilitar Stretch Database e trazer de volta dados remotos.

• Para desabilitar o Stretch para uma tabela e copiar os dados remotos para a tabela do Azure de volta para o SQL Server, execute o comando a seguir. Esse comando não pode ser cancelado.

  ALTER TABLE <table_name>
     SET ( REMOTE_DATA_ARCHIVE ( MIGRATION_STATE = INBOUND ) ) ;
  

Essa operação incorre em custos de transferência de dados e não pode ser cancelada. Saiba mais em Detalhes de preços de transferências de dados.

Depois que todos os dados remotos forem copiados do Azure de volta para o SQL Server, o Stretch será desabilitado para a tabela.

• Para desabilitar o Stretch de uma tabela e abandonar os dados remotos, execute o comando a seguir.

  ALTER TABLE <table_name>
     SET ( REMOTE_DATA_ARCHIVE = OFF_WITHOUT_DATA_RECOVERY ( MIGRATION_STATE = PAUSED ) ) ;
  

Depois de desabilitar o Banco de Dados de Stretch de uma tabela, a migração de dados é interrompida e os resultados da consulta já não incluem os resultados da tabela remota.

Desabilitar o Stretch não remove a tabela remota. Se você quiser excluir o banco de dados remoto, descarte-o usando o portal do Azure.

[ FILTER_PREDICATE = { nulo | predicate } ]

Aplica-se a: SQL Server 2017 (14.x) e versões posteriores.

Opcionalmente, especifique um predicado de filtro para selecionar linhas para migrar de uma tabela que contém dados atuais e históricos. O predicado deve chamar uma função com valor de tabela embutido determinística. Saiba mais em Habilitar Stretch Database para uma tabela e Selecionar linhas a serem migradas usando uma função de filtro – Stretch Database.

Se você não especificar um predicado de filtro, a tabela inteira será migrada.

Quando você especifica um predicado de filtro, também precisa especificar MIGRATION_STATE.

MIGRATION_STATE = { OUTBOUND | ENTRADA | PAUSED }

Aplica-se a: SQL Server 2017 (14.x) e versões posteriores.

• Especifique OUTBOUND para migrar dados do SQL Server para o Azure.

• Especifique INBOUND para copiar os dados remotos da tabela do Azure de volta para o SQL Server e para desabilitar o Stretch para a tabela. Saiba mais em Desabilitar Stretch Database e trazer de volta dados remotos.

  Essa operação incorre em custos de transferência de dados e não pode ser cancelada.

• Especifique PAUSED para pausar ou adiar a migração de dados. Saiba mais em Pausar e retomar a migração de dados – Stretch Database.

WAIT_AT_LOW_PRIORITY

Aplica-se a: SQL Server 2014 (12.x) e versões posteriores e Banco de Dados SQL do Azure.

Uma recriação de índice online precisa aguardar as operações de bloqueio nesta tabela. WAIT_AT_LOW_PRIORITY indica que a operação de recompilação de índice online aguarda bloqueios de baixa prioridade, permitindo que outras operações continuem enquanto a operação de build de índice online está aguardando. Omitir a opção WAIT AT LOW PRIORITY é o mesmo que WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = tempo [ MINUTOS ]

Aplica-se a: SQL Server 2014 (12.x) e versões posteriores e Banco de Dados SQL do Azure.

O tempo de espera, que é um valor inteiro especificado em minutos, que os bloqueios de recompilação de SWITCH índice online ou online esperam com baixa prioridade ao executar o comando DDL. Se a operação for bloqueada por tempo MAX_DURATION , uma das ABORT_AFTER_WAIT ações será executada. MAX_DURATION o tempo é sempre em minutos, e você pode omitir a palavra MINUTES.

ABORT_AFTER_WAIT = { NENHUM | SELF | BLOQUEADORES }

Aplica-se a: SQL Server 2014 (12.x) e versões posteriores e Banco de Dados SQL do Azure.

• NONE

  Continue aguardando o bloqueio com prioridade normal.

• SELF

  Saia da SWITCH operação DDL de recompilação de índice online atualmente em execução sem tomar nenhuma ação.

• BLOCKERS

  Encerre todas as transações de usuário que atualmente bloqueiam a SWITCH operação DDL de recompilação de índice online para que a operação possa continuar.

  Requer ALTER ANY CONNECTION permissão.

SE EXISTIR

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores e Banco de Dados SQL do Azure.

Descartará condicionalmente a coluna ou restrição somente se ela já existir.

RETOMÁVEL = { ON | DESLIGADO}

Aplica-se a: SQL Server 2022 (16.x) e versões posteriores.

Especifica se uma operação ALTER TABLE ADD CONSTRAINT é retomável. A adição da operação de restrição de tabela é retomável quando ON. A adição da operação de restrição de tabela é retomável quando OFF. O padrão é OFF. A opção RESUMABLE pode ser usada como parte de ALTER TABLE index_option em ALTER TABLE table_constraint.

MAX_DURATION quando usado com RESUMABLE = ON (requer ONLINE = ON) indica o tempo (um valor inteiro especificado em minutos) que uma operação de restrição de adição online retomável é executada antes de ser pausada. Se não for especificada, a operação continuará até a conclusão.

Para obter mais informações sobre como habilitar e usar operações retomáveis ALTER TABLE ADD CONSTRAINT , consulte Resumable add table constraints.

Remarks

Para adicionar novas linhas de dados, use INSERT. Para remover linhas de dados, use DELETE ou TRUNCATE TABLE. Para modificar os valores nas linhas existentes, use UPDATE.

Se houver planos de execução no cache de procedimentos que referenciem a tabela, ALTER TABLE marque-os para serem recompilados na próxima execução.

No banco de dados SQL no Microsoft Fabric, algumas características de tabela podem ser criadas, mas não são espelhadas no Fabric OneLake. Para mais informações, veja Limitações para espelhamento de banco de dados Fabric SQL.

Alterar o tamanho de uma coluna

É possível alterar o comprimento, a precisão ou a escala de uma coluna, especificando um novo tamanho para o tipo de dados da coluna. Use a ALTER COLUMN cláusula. Se dados existirem na coluna, o novo tamanho não poderá ser menor do que o tamanho máximo dos dados. Além disso, você não pode definir a coluna em um índice, a menos que a coluna seja um tipo de dados varchar, nvarchar ou varbinary e o índice não seja o resultado de uma PRIMARY KEY restrição. Veja o exemplo na seção curta intitulada Alterar uma definição de coluna.

Bloqueios e ALTER TABLE

Alterações especificadas na ALTER TABLE implementação imediatamente. Se as alterações exigirem modificações das linhas na tabela, ALTER TABLE atualize as linhas. ALTER TABLE adquire um bloqueio de modificação de esquema (Sch-M) na tabela para garantir que nenhuma outra conexão faça referência até mesmo aos metadados da tabela durante a alteração, exceto as operações de índice online que exigem um curto bloqueio de Sch-M no final. Em uma operação ALTER TABLE...SWITCH, o bloqueio é adquirido em tabelas de origem e de destino. As modificações feitas na tabela são registradas e completamente recuperáveis. Alterações que afetam todas as linhas em tabelas grandes, como remover uma coluna ou, em algumas edições do SQL Server, adicionar uma NOT NULL coluna com um valor padrão, podem levar muito tempo para serem concluídas e gerar muitos registros de log. Execute essas ALTER TABLE instruções com o mesmo cuidado que qualquer INSERTinstrução ou UPDATE instrução DELETEque afete muitas linhas.

Aplica-se ao Warehouse no Microsoft Fabric.

ALTER TABLE não pode fazer parte de uma transação explícita.

Eventos Estendidos (XEvents) para o comutador de partição

Os seguintes XEvents estão relacionados a ALTER TABLE ... SWITCH PARTITION e reconstruções de índices online.

• lock_request_priority_state
• process_killed_by_abort_blockers
• ddl_with_wait_at_low_priority

Adicionar colunas NOT NULL como uma operação online

No SQL Server 2012 (11.x) Enterprise Edition e versões posteriores, adicionar uma NOT NULL coluna com um valor padrão é uma operação online quando o valor padrão é uma constante de runtime. Isso significa que a operação é concluída quase instantaneamente, apesar do número de linhas na tabela, porque as linhas existentes na tabela não são atualizadas durante a operação. Em vez disso, o valor padrão é armazenado somente nos metadados da tabela e o valor é pesquisado conforme necessário em consultas que acessam essas linhas. Esse comportamento é automático. Nenhuma sintaxe adicional é necessária para implementar a operação online além da ADD COLUMN sintaxe. Uma constante de runtime é uma expressão que gera o mesmo valor no runtime para cada linha na tabela, independentemente de seu determinismo. Por exemplo, a expressão "My temporary data"constante ou a função GETUTCDATETIME() do sistema são constantes de runtime. Por outro lado, as funções NEWID() ou NEWSEQUENTIALID() não são constantes de runtime porque é gerado um valor exclusivo para cada linha da tabela. A adição de uma NOT NULL coluna com um valor padrão que não é uma constante de runtime é sempre executada offline e um bloqueio exclusivo (Sch-M) é adquirido durante a operação.

Enquanto as linhas existentes referenciam o valor armazenado nos metadados, o valor padrão é armazenado na linha para qualquer nova linha inserida e não especifica outro valor para a coluna. O valor padrão armazenado em metadados é movido para uma linha existente quando a linha é atualizada (mesmo que a coluna real não seja especificada na UPDATE instrução) ou se a tabela ou índice clusterizado for recriado.

Colunas do tipo varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image, hierarchyid, geometry, geography ou CLR não podem ser adicionadas em uma operação online. Não será possível adicionar uma coluna online se isso fizer o tamanho máximo de linha possível exceder o limite de 8.060 bytes. Nesse caso, a coluna é adicionada como uma operação offline.

Execução de plano paralelo

No SQL Server 2012 (11.x) Enterprise Edition e versões posteriores, o número de processadores empregados para executar uma única ALTER TABLE ADD instrução (baseada em índice) CONSTRAINT ou DROP (índice clusterizado) CONSTRAINT é determinado pelo grau máximo da opção de configuração de paralelismo e pela carga de trabalho atual. Se o Mecanismo de Banco de Dados detectar que o sistema está ocupado, o grau de paralelismo da operação será automaticamente reduzido antes do início da execução da instrução. Você pode configurar manualmente o número de processadores usados para executar a instrução especificando a opção MAXDOP . Para obter mais informações, consulte a configuração do servidor: grau máximo de paralelismo.

Tabelas particionadas

Além de executar operações SWITCH que envolvem tabelas particionadas, use ALTER TABLE para alterar o estado das colunas, restrições e gatilhos de uma tabela particionada, assim como é usada para tabelas não particionadas. Porém, essa instrução não pode ser usada para alterar o modo que a própria tabela é particionada. Para reparticionar uma tabela particionada, use ALTER PARTITION SCHEME e ALTER PARTITION FUNCTION. Além disso, não é possível alterar o tipo de dados de uma coluna em uma tabela particionada.

Restrições em tabelas com exibições associadas a esquema

As restrições que se aplicam a ALTER TABLE instruções em tabelas com exibições associadas ao esquema são as mesmas aplicadas atualmente ao modificar tabelas com um índice simples. É permitido adicionar uma coluna. Porém, não é permitido remover ou alterar uma coluna que participa de qualquer exibição associada a esquema. Se a ALTER TABLE instrução exigir a alteração de uma coluna usada em uma exibição associada ao esquema, ALTER TABLE falhará e o Mecanismo de Banco de Dados gerará uma mensagem de erro. Para obter mais informações sobre associação de esquema e exibições indexadas, veja CREATE VIEW.

A adição ou remoção de gatilhos em tabelas base não é afetada pela criação de uma exibição associada a esquema que referencia tabelas.

Índices e ALTER TABLE

Os índices criados como parte de uma restrição são descartados quando a restrição é descartada. Índices com os quais foram criados CREATE INDEX devem ser descartados com DROP INDEX. Use a ALTER INDEX instrução para recompilar uma parte de índice de uma definição de restrição; a restrição não precisa ser descartada e adicionada novamente com ALTER TABLE.

Todos os índices e as restrições com base em uma coluna devem ser removidos antes que a coluna possa ser removida.

Quando uma restrição que cria um índice clusterizado é excluída, as linhas de dados que foram armazenadas no nível folha do índice clusterizado são armazenadas em uma tabela não clusterizada. Você pode remover o índice clusterizado e mover a tabela resultante para outro grupo de arquivos ou esquema de partição em uma única transação especificando a opção MOVE TO . A MOVE TO opção tem as seguintes restrições:

• MOVE TO não é válido para exibições indexadas ou índices não clusterizados.

• O esquema de partição ou grupo de arquivos já deve existir.

• Se MOVE TO não for especificado, a tabela estará localizada no mesmo esquema de partição ou grupo de arquivos definido para o índice clusterizado.

Ao remover um índice clusterizado, especifique a opção ONLINE = ON para que a DROP INDEX transação não bloqueie consultas e modificações nos dados subjacentes e índices não clusterizados associados.

ONLINE = ON tem as seguintes restrições:

• ONLINE = ON não é válido para índices clusterizados que também estão desabilitados. Índices desabilitados devem ser descartados usando ONLINE = OFF.
• Apenas um índice pode ser descartado por vez.
• ONLINE = ON não é válido para exibições indexadas, índices não clusterizados ou índices em tabelas temporárias locais.
• ONLINE = ON não é válido para índices columnstore.

É necessário ter espaço temporário em disco igual ao tamanho do índice clusterizado existente para descartar um índice clusterizado. Esse espaço adicional será liberado assim que a operação for concluída.

Replicar alterações de esquema

Quando você é executado ALTER TABLE em uma tabela publicada em um Publicador do SQL Server, por padrão, essa alteração se propaga para todos os Assinantes do SQL Server. Essa funcionalidade tem algumas restrições. É possível desabilitá-la. Para obter mais informações, consulte Make Schema Changes on Publication Databases (Fazer alterações de esquema em bancos de dados de publicação).

Compactação de dados

Não é possível habilitar as tabelas do sistema para compactação. Se a tabela for um heap, a operação de recompilação para ONLINE o modo será encadeada. Use o OFFLINE modo para uma operação de recompilação de heap com vários threads. Para obter mais informações sobre compactação de dados, consulte Compactação de dados.

Para avaliar como a alteração do estado de compactação afeta uma tabela, um índice ou uma partição, use o sp_estimate_data_compression_savings procedimento armazenado do sistema.

As restrições a seguir se aplicam a tabelas particionadas:

• Não será possível alterar a configuração de compactação de uma única partição se a tabela tiver índices não alinhados.
• A sintaxe ALTER TABLE <table> REBUILD PARTITION ... recria a partição especificada.
• A sintaxe ALTER TABLE <table> REBUILD WITH ... recria todas as partições.

Soltar colunas ntext

Ao remover colunas usando o tipo de dados ntext preterido, a limpeza dos dados excluídos ocorre como uma operação serializada em todas as linhas. A limpeza pode exigir uma grande quantidade de tempo. Ao soltar uma coluna ntext em uma tabela com muitas linhas, atualize a coluna ntext para NULL valor primeiro e, em seguida, solte a coluna. Você pode executar essa opção com operações paralelas e torná-la muito mais rápida.

Recompilação de índice online

Para executar a instrução DDL de uma recompilação de índice online, todas as transações de bloqueio ativas em execução em uma tabela específica devem ser concluídas. Quando a recompilação de índice online for iniciada, ela bloqueará todas as novas transações que estão prontas para iniciar a execução nessa tabela. Embora a duração do bloqueio da recompilação de índice online seja curta, é possível que a espera pela conclusão de todas as transações abertas em uma tabela específica e o bloqueio das novas transações a serem iniciadas afetem significativamente a taxa de transferência. Isso pode diminuir a velocidade de uma carga de trabalho ou ocasionar o tempo limite dela, bem como limitar consideravelmente o acesso à tabela subjacente. A WAIT_AT_LOW_PRIORITY opção permite que os DBAs gerenciem o bloqueio S e Sch-M bloqueios necessários para recompilações de índice online. Em todos os três casos: NONE, SELFe BLOCKERS, se durante o tempo de espera ((MAX_DURATION = n [minutes])) não houver atividades de bloqueio, a recompilação de índice online será executada imediatamente sem esperar e a instrução DDL for concluída.

Suporte à compatibilidade

A ALTER TABLE instrução dá suporte apenas a nomes de tabela de duas partes (schema.object). No SQL Server, a especificação de uma tabela usando os formatos a seguir falhará em tempo de compilação com o erro 117.

• server.database.schema.table
• .database.schema.table
• ..schema.table

Em versões anteriores, a especificação do formato server.database.schema.table retornava o erro 4902. A especificação do formato .database.schema.table ou o formato ..schema.table foi bem-sucedida.

Para resolver o problema, remova o uso de um prefixo de 4 partes.

Permissions

Requer ALTER permissão na tabela.

ALTER TABLE as permissões se aplicam a ambas as tabelas envolvidas em uma instrução ALTER TABLE SWITCH . Qualquer dado que seja alternado herda a segurança da tabela de destino.

Se você definiu qualquer coluna na ALTER TABLE instrução como sendo de um tipo clr (common language runtime) ou tipo de dados de alias definido pelo usuário, REFERENCES a permissão no tipo será necessária.

Adicionar ou alterar uma coluna que atualiza as linhas da tabela requer UPDATE permissão na tabela. Por exemplo, adicionar uma NOT NULL coluna com um valor padrão ou adicionar uma coluna de identidade quando a tabela não estiver vazia.

Examples

Os exemplos de código neste artigo usam o AdventureWorks2025 banco de dados ou AdventureWorksDW2025 exemplo, que você pode baixar na home page de Exemplos do Microsoft SQL Server e Projetos da Comunidade .

Adicionar colunas e restrições

Os exemplos desta seção demonstram a adição de colunas e restrições em uma tabela.

A. Adicionar uma nova coluna

O exemplo a seguir adiciona uma coluna que permite valores nulos e não tem valores fornecidos por meio de uma DEFAULT definição. Na nova coluna, cada linha tem NULL.

CREATE TABLE dbo.doc_exa (column_a INT);
GO

ALTER TABLE dbo.doc_exa
    ADD column_b VARCHAR (20) NULL;
GO

B. Adicionar uma coluna com uma restrição

O exemplo a seguir adiciona uma nova coluna com uma restrição UNIQUE.

CREATE TABLE dbo.doc_exc (column_a INT);
GO

ALTER TABLE dbo.doc_exc
    ADD column_b VARCHAR (20) NULL
        CONSTRAINT exb_unique UNIQUE;
GO

EXECUTE sp_help doc_exc;
GO

DROP TABLE dbo.doc_exc;
GO

C. Adicionar uma restrição CHECK não verificada a uma coluna existente

O exemplo a seguir adiciona uma restrição a uma coluna existente na tabela. A coluna tem um valor que viola a restrição. Portanto, WITH NOCHECK é usado para evitar que a restrição seja validada contra as linhas existentes e para permitir que a restrição seja adicionada.

CREATE TABLE dbo.doc_exd (column_a INT);
GO

INSERT INTO dbo.doc_exd VALUES (-1);
GO

ALTER TABLE dbo.doc_exd WITH NOCHECK
    ADD CONSTRAINT exd_check CHECK (column_a > 1);
GO

EXECUTE sp_help doc_exd;
GO

DROP TABLE dbo.doc_exd;
GO

D. Adicionar uma restrição DEFAULT a uma coluna existente

O exemplo a seguir cria uma tabela com duas colunas e insere um valor na primeira coluna e a outra coluna permanece NULL. Depois, uma restrição DEFAULT é adicionada à segunda coluna. Para verificar se o padrão está aplicado, outro valor é inserido na primeira coluna e a tabela é consultada.

CREATE TABLE dbo.doc_exz
(
    column_a INT,
    column_b INT
);
GO

INSERT INTO dbo.doc_exz (column_a) VALUES (7);
GO

ALTER TABLE dbo.doc_exz
    ADD CONSTRAINT col_b_def
        DEFAULT 50 FOR column_b;
GO

INSERT INTO dbo.doc_exz (column_a) VALUES (10);
GO

SELECT * FROM dbo.doc_exz;
GO

DROP TABLE dbo.doc_exz;
GO

E. Adicionar várias colunas com restrições

O exemplo a seguir adiciona várias colunas com restrições definidas com a nova coluna. A primeira coluna nova tem uma propriedade IDENTITY. Cada linha na tabela tem novos valores com incremento na coluna de identidade.

CREATE TABLE dbo.doc_exe
(
    column_a INT
        CONSTRAINT column_a_un UNIQUE
);
GO

ALTER TABLE dbo.doc_exe

    -- Add a PRIMARY KEY identity column.
    ADD column_b INT IDENTITY
        CONSTRAINT column_b_pk PRIMARY KEY,

    -- Add a column that references another column in the same table.
        column_c INT NULL
            CONSTRAINT column_c_fk FOREIGN KEY REFERENCES doc_exe (column_a),

    -- Add a column with a constraint to enforce that
    -- nonnull data is in a valid telephone number format.
        column_d VARCHAR (16) NULL
            CONSTRAINT column_d_chk CHECK (column_d LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
                                       OR column_d LIKE '([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),

    -- Add a nonnull column with a default.
        column_e DECIMAL (3, 3)
            CONSTRAINT column_e_default DEFAULT .081;
GO

EXECUTE sp_help doc_exe;
GO

DROP TABLE dbo.doc_exe;
GO

F. Adicionar uma coluna que permite valor nulo com valores padrão

O exemplo a seguir adiciona uma coluna que permite valor nulo com uma definição DEFAULT e usa WITH VALUES para fornecer valores para cada linha existente na tabela. Se WITH VALUES não for usado, cada linha terá o valor NULL na nova coluna.

CREATE TABLE dbo.doc_exf (column_a INT);
GO

INSERT INTO dbo.doc_exf VALUES (1);
GO

ALTER TABLE dbo.doc_exf
    ADD AddDate SMALLDATETIME
        CONSTRAINT AddDateDflt DEFAULT GETDATE() WITH VALUES NULL;
GO

DROP TABLE dbo.doc_exf;
GO

G. Criar uma restrição PRIMARY KEY com opções de índice ou de compressão de dados

O exemplo a seguir cria a PRIMARY KEY restrição PK_TransactionHistoryArchive_TransactionID e define as opções FILLFACTORe ONLINEPAD_INDEX. O índice clusterizado resultante tem o mesmo nome da restrição.

Aplica-se a: SQL Server e Banco de Dados SQL do Azure.

USE AdventureWorks2022;
GO

ALTER TABLE Production.TransactionHistoryArchive WITH NOCHECK
    ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID
        PRIMARY KEY CLUSTERED (TransactionID) WITH (FILLFACTOR = 75, ONLINE = ON, PAD_INDEX = ON);
GO

Este exemplo semelhante se aplica a compactação de página ao aplicar a chave primária clusterizada.

USE AdventureWorks2022;
GO

ALTER TABLE Production.TransactionHistoryArchive WITH NOCHECK
    ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID
        PRIMARY KEY CLUSTERED (TransactionID) WITH (DATA_COMPRESSION = PAGE);
GO

H. Adicionar uma coluna esparsa

Os exemplos a seguir mostram a adição e modificação de colunas esparsas na tabela T1. O código para criar a tabela T1 é o seguinte:

CREATE TABLE T1
(
    C1 INT PRIMARY KEY,
    C2 VARCHAR (50) SPARSE NULL,
    C3 INT SPARSE NULL,
    C4 INT
);
GO

Para adicionar uma outra coluna esparsa C5, execute a seguinte instrução:

ALTER TABLE T1
    ADD C5 CHAR (100) SPARSE NULL;
GO

Para converter a coluna não esparsa C4 a uma coluna esparsa, execute a seguinte instrução:

ALTER TABLE T1
    ALTER COLUMN C4 ADD SPARSE;
GO

Para converter a coluna esparsa C4 para uma coluna não esparsa, execute a seguinte instrução.

ALTER TABLE T1
    ALTER COLUMN C4 DROP SPARSE;
GO

I. Adicionar um conjunto de colunas

Os exemplos a seguir mostram a adição de uma coluna à tabela T2. Um conjunto de colunas não poderá ser adicionado a uma tabela se ela já contiver colunas esparsas. O código para criar a tabela T2 é o seguinte:

CREATE TABLE T2
(
    C1 INT PRIMARY KEY,
    C2 VARCHAR (50) NULL,
    C3 INT NULL,
    C4 INT
);
GO

As três instruções a seguir adicionam um conjunto de colunas chamado CS e, depois, modificam colunas C2 e C3 para SPARSE.

ALTER TABLE T2
    ADD CS XML COLUMN_SET FOR ALL_SPARSE_COLUMNS;
GO

ALTER TABLE T2
    ALTER COLUMN C2 ADD SPARSE;
GO

ALTER TABLE T2
    ALTER COLUMN C3 ADD SPARSE;
GO

J. Adicionar uma coluna criptografada

A instrução a seguir adiciona uma coluna criptografada denominada PromotionCode.

ALTER TABLE Customers
    ADD PromotionCode NVARCHAR (100)
        ENCRYPTED WITH (
            COLUMN_ENCRYPTION_KEY = MyCEK,
            ENCRYPTION_TYPE = RANDOMIZED,
            ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
        );

K. Adicionar uma chave primária com uma operação retomável

Operação retomável ALTER TABLE para adicionar uma chave primária clusterizada na coluna (a) com MAX_DURATION de 240 minutos.

ALTER TABLE table1
ADD CONSTRAINT PK_Constrain PRIMARY KEY CLUSTERED (a)
WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 240);

Descartar colunas e restrições

Os exemplos desta seção demonstram o descarte de colunas e restrições.

A. Descartar uma coluna ou colunas

O primeiro exemplo modifica uma tabela para remover uma coluna. O segundo exemplo remove várias colunas.

CREATE TABLE dbo.doc_exb
(
    column_a INT,
    column_b VARCHAR (20) NULL,
    column_c DATETIME,
    column_d INT
);
GO

-- Remove a single column.
ALTER TABLE dbo.doc_exb DROP COLUMN column_b;
GO

-- Remove multiple columns.
ALTER TABLE dbo.doc_exb DROP COLUMN column_c, column_d;

B. Descartar restrições e colunas

O primeiro exemplo remove uma restrição UNIQUE de uma tabela. O segundo exemplo remove duas restrições e uma única coluna.

CREATE TABLE dbo.doc_exc
(
    column_a INT NOT NULL
        CONSTRAINT my_constraint UNIQUE
);
GO

-- Example 1. Remove a single constraint.
ALTER TABLE dbo.doc_exc DROP my_constraint;
GO

DROP TABLE dbo.doc_exc;
GO

CREATE TABLE dbo.doc_exc
(
    column_a INT NOT NULL
        CONSTRAINT my_constraint UNIQUE,
    column_b INT NOT NULL
        CONSTRAINT my_pk_constraint PRIMARY KEY
);
GO

-- Example 2. Remove two constraints and one column
-- The keyword CONSTRAINT is optional. The keyword COLUMN is required.
ALTER TABLE dbo.doc_exc
    DROP CONSTRAINT my_constraint, my_pk_constraint, COLUMN column_b;
GO

C. Descartar uma restrição PRIMARY KEY no modo ONLINE

O exemplo a seguir exclui uma PRIMARY KEY restrição com a opção ONLINE definida como ON.

ALTER TABLE Production.TransactionHistoryArchive
    DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID
    WITH (ONLINE = ON);
GO

D. Adicionar e descartar uma restrição FOREIGN KEY

O exemplo a seguir cria a tabela ContactBackup e, em seguida, altera a tabela, adicionando uma restrição FOREIGN KEY que referencia a tabela Person.Person e, depois, descartando a restrição FOREIGN KEY.

CREATE TABLE Person.ContactBackup (ContactID INT);
GO

ALTER TABLE Person.ContactBackup
    ADD CONSTRAINT FK_ContactBackup_Contact
        FOREIGN KEY (ContactID) REFERENCES Person.Person (BusinessEntityID);
GO

ALTER TABLE Person.ContactBackup
    DROP CONSTRAINT FK_ContactBackup_Contact;
GO

DROP TABLE Person.ContactBackup;

Alterar uma definição de coluna

A. Alterar o tipo de dados de uma coluna

O exemplo a seguir altera uma coluna de uma tabela de INT para DECIMAL.

CREATE TABLE dbo.doc_exy (column_a INT);
GO

INSERT INTO dbo.doc_exy (column_a) VALUES (10);
GO

ALTER TABLE dbo.doc_exy ALTER COLUMN column_a DECIMAL (5, 2);
GO

DROP TABLE dbo.doc_exy;
GO

B. Alterar o tamanho de uma coluna

O exemplo a seguir aumenta o tamanho de uma coluna varchar e a precisão e escala de uma coluna decimal. Como essas colunas contêm dados, o tamanho da coluna só pode ser aumentado. Além disso, observe que col_a está definido como um índice exclusivo. O tamanho ainda pode ser aumentado porque o tipo de col_a dados é um varchar e o índice não é o resultado de uma PRIMARY KEY restrição.

-- Create a two-column table with a unique index on the varchar column.
CREATE TABLE dbo.doc_exy
(
    col_a VARCHAR (5) UNIQUE NOT NULL,
    col_b DECIMAL (4, 2)
);
GO

INSERT INTO dbo.doc_exy VALUES ('Test', 99.99);
GO

-- Verify the current column size.
SELECT name,
       TYPE_NAME(system_type_id),
       max_length,
       precision,
       scale
FROM sys.columns
WHERE object_id = OBJECT_ID(N'dbo.doc_exy');
GO

-- Increase the size of the varchar column.
ALTER TABLE dbo.doc_exy ALTER COLUMN col_a VARCHAR (25);
GO

-- Increase the scale and precision of the decimal column.
ALTER TABLE dbo.doc_exy ALTER COLUMN col_b DECIMAL (10, 4);
GO

-- Insert a new row.
INSERT INTO dbo.doc_exy VALUES ('MyNewColumnSize', 99999.9999);
GO

-- Verify the current column size.
SELECT name,
       TYPE_NAME(system_type_id),
       max_length,
       precision,
       scale
FROM sys.columns
WHERE object_id = OBJECT_ID(N'dbo.doc_exy');

C. Alterar a ordenação da coluna

Os exemplos a seguir mostram como alterar a ordenação de uma coluna. Primeiro, uma tabela é criada com a ordenação de usuário padrão.

CREATE TABLE T3
(
    C1 INT PRIMARY KEY,
    C2 VARCHAR (50) NULL,
    C3 INT NULL,
    C4 INT
);
GO

Em seguida, a ordenação da coluna C2 é alterada para Latin1_General_BIN. O tipo de dados é obrigatório, mesmo que não tenha sido alterado.

ALTER TABLE T3
    ALTER COLUMN C2 VARCHAR (50) COLLATE Latin1_General_BIN;
GO

D. Criptografar uma coluna

O exemplo a seguir mostra como criptografar uma coluna usando Always Encrypted com enclaves seguros.

Primeiro, uma tabela é criada sem nenhuma coluna criptografada.

CREATE TABLE T3
(
    C1 INT PRIMARY KEY,
    C2 VARCHAR (50) NULL,
    C3 INT NULL,
    C4 INT
);
GO

Em seguida, a coluna 'C2' é criptografada com uma chave de criptografia de coluna, chamada CEK1, e a criptografia aleatória. Para que a instrução a seguir seja bem-sucedida:

• A chave de criptografia de coluna deve ser habilitada para enclave. Ou seja, ele deve ser criptografado com uma CMK (chave mestra de coluna) que permita cálculos de enclave.
• A instância do SQL Server de destino dá suporte a Always Encrypted com enclaves seguros.
• A instrução deve ser emitida por uma conexão configurada para Always Encrypted com enclaves seguros e usando um driver de cliente com suporte.
• O aplicativo de chamada deve ter acesso ao CMK, protegendo CEK1.

ALTER TABLE T3 ALTER COLUMN C2 VARCHAR (50)  ENCRYPTED WITH (
     COLUMN_ENCRYPTION_KEY = [CEK1],
     ENCRYPTION_TYPE = RANDOMIZED,
     ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
    ) NULL;
GO

Alterar uma definição de tabela

Os exemplos desta seção demonstram como alterar a definição de uma tabela.

A. Modificar uma tabela para alterar a compactação

O exemplo a seguir altera a compactação de uma tabela não particionada. O heap ou índice clusterizado é recriado. Se a tabela for um heap, todos os índices não clusterizados serão recriados.

ALTER TABLE T1 REBUILD
    WITH (DATA_COMPRESSION = PAGE);

O exemplo a seguir altera a compactação de uma tabela particionada. A sintaxe REBUILD PARTITION = 1 faz com que somente o número de partição 1 seja recriado.

Aplica-se a: SQL Server.

ALTER TABLE PartitionTable1 REBUILD PARTITION = 1
    WITH (DATA_COMPRESSION = NONE);
GO

A mesma operação usando a sintaxe alternada a seguir faz com que todas as partições na tabela sejam recriadas.

Aplica-se a: SQL Server.

ALTER TABLE PartitionTable1 REBUILD PARTITION = ALL
    WITH (DATA_COMPRESSION = PAGE ON PARTITIONS (1));

Para obter exemplos adicionais de compactação de dados, consulte Compactação de dados.

B. Modificar uma tabela columnstore para alterar a compactação de arquivamento

O exemplo a seguir compacta ainda mais uma partição de tabela columnstore aplicando um algoritmo de compactação adicional. Essa compactação reduz a tabela para um tamanho menor, mas também aumenta o tempo necessário para armazenamento e recuperação. Isso pode ser útil para fins de arquivamento, ou em outras situações que exijam menos espaço e possam dispensar mais tempo para armazenamento e recuperação.

Aplica-se a: SQL Server 2014 (12.x) e versões posteriores e Banco de Dados SQL do Azure.

ALTER TABLE PartitionTable1 REBUILD PARTITION = 1
    WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);
GO

O exemplo a seguir descompacta uma partição de tabela columnstore compactada com COLUMNSTORE_ARCHIVE a opção. Quando os dados forem restaurados, eles continuarão sendo compactados por meio da compactação columnstore usada em todas as tabelas columnstore.

Aplica-se a: SQL Server 2014 (12.x) e versões posteriores e Banco de Dados SQL do Azure.

ALTER TABLE PartitionTable1 REBUILD PARTITION = 1
    WITH (DATA_COMPRESSION = COLUMNSTORE);
GO

C. Alternar partições entre tabelas

O exemplo a seguir cria uma tabela particionada, pressupondo que o esquema de partição myRangePS1 já esteja criado no banco de dados. Em seguida, uma tabela não particionada é criada com a mesma estrutura de uma tabela particionada e no mesmo grupo de arquivos que PARTITION 2 da tabela PartitionTable. Depois, os dados da PARTITION 2 da tabela PartitionTable são inseridos na tabela NonPartitionTable.

CREATE TABLE PartitionTable
(
    col1 INT,
    col2 CHAR (10)
) ON myRangePS1 (col1);
GO

CREATE TABLE NonPartitionTable
(
    col1 INT,
    col2 CHAR (10)
) ON test2fg;
GO

ALTER TABLE PartitionTable SWITCH PARTITION 2 TO NonPartitionTable;
GO

D. Permitir escalonamento de bloqueios em tabelas particionadas

O exemplo a seguir habilita o escalonamento de bloqueios no nível de partição em uma tabela particionada. Se a tabela não estiver particionada, o escalonamento de bloqueio será definido no TABLE nível.

Aplica-se a: SQL Server e Banco de Dados SQL do Azure.

ALTER TABLE dbo.T1 SET (LOCK_ESCALATION = AUTO);
GO

E. Configurar o controle de alterações em uma tabela

O exemplo a seguir habilita o controle de alterações na tabela Person.Person.

Aplica-se a: SQL Server e Banco de Dados SQL do Azure.

USE AdventureWorks2022;

ALTER TABLE Person.Person ENABLE CHANGE_TRACKING;

O exemplo a seguir habilita o controle de alterações e também o controle de colunas que são atualizadas durante uma alteração.

Aplica-se a: SQL Server.

USE AdventureWorks2022;
GO

ALTER TABLE Person.Person ENABLE CHANGE_TRACKING
    WITH (TRACK_COLUMNS_UPDATED = ON);

O exemplo a seguir desabilita o controle de alterações na tabela Person.Person.

Aplica-se a: SQL Server e Banco de Dados SQL do Azure.

USE AdventureWorks2022;
GO

ALTER TABLE Person.Person DISABLE CHANGE_TRACKING;

Desabilitar e habilitar restrições e gatilhos

A. Desabilitar e reabilitar uma restrição

O exemplo a seguir desabilita uma restrição que limita os salários aceitos nos dados. NOCHECK CONSTRAINT é usada com ALTER TABLE para desabilitar a restrição e permitir uma inserção que normalmente violaria a restrição. CHECK CONSTRAINT reabilita a restrição.

CREATE TABLE dbo.cnst_example
(
    id INT NOT NULL,
    name VARCHAR (10) NOT NULL,
    salary MONEY NOT NULL
        CONSTRAINT salary_cap CHECK (salary < 100000)
);

-- Valid inserts
INSERT INTO dbo.cnst_example VALUES (1, 'Joe Brown', 65000);
INSERT INTO dbo.cnst_example VALUES (2, 'Mary Smith', 75000);

-- This insert violates the constraint.
INSERT INTO dbo.cnst_example VALUES (3, 'Pat Jones', 105000);

-- Disable the constraint and try again.
ALTER TABLE dbo.cnst_example NOCHECK CONSTRAINT salary_cap;
INSERT INTO dbo.cnst_example VALUES (3, 'Pat Jones', 105000);

-- Re-enable the constraint and try another insert; this fails.
ALTER TABLE dbo.cnst_example CHECK CONSTRAINT salary_cap;
INSERT INTO dbo.cnst_example VALUES (4, 'Eric James', 110000);

B. Desabilitar e reabilitar um gatilho

O exemplo a seguir usa a opção DISABLE TRIGGER de ALTER TABLE para desabilitar o gatilho e permitir uma inserção que normalmente violaria o gatilho. ENABLE TRIGGER é usado para reabilitar o gatilho.

CREATE TABLE dbo.trig_example
(
    id INT,
    name VARCHAR (12),
    salary MONEY
);
GO

-- Create the trigger.
CREATE TRIGGER dbo.trig1
    ON dbo.trig_example
    FOR INSERT
    AS IF (SELECT COUNT(*)
           FROM INSERTED
           WHERE salary > 100000) > 0
           BEGIN
               PRINT 'TRIG1 Error: you attempted to insert a salary > $100,000';
               ROLLBACK;
           END
GO

-- Try an insert that violates the trigger.
INSERT INTO dbo.trig_example VALUES (1, 'Pat Smith', 100001);
GO

-- Disable the trigger.
ALTER TABLE dbo.trig_example DISABLE TRIGGER trig1;
GO

-- Try an insert that would typically violate the trigger.
INSERT INTO dbo.trig_example VALUES (2, 'Chuck Jones', 100001);
GO

-- Re-enable the trigger.
ALTER TABLE dbo.trig_example ENABLE TRIGGER trig1;
GO

-- Try an insert that violates the trigger.
INSERT INTO dbo.trig_example VALUES (3, 'Mary Booth', 100001);
GO

Operações online

A. Recompilação de índice online usando opções de espera de baixa prioridade

O exemplo a seguir mostra como executar uma recompilação de índice online que especifica as opções de espera de baixa prioridade.

Aplica-se a: SQL Server 2014 (12.x) e versões posteriores e Banco de Dados SQL do Azure.

ALTER TABLE T1 REBUILD WITH (
    PAD_INDEX = ON,
    ONLINE = ON (
        WAIT_AT_LOW_PRIORITY (MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS)
    )
);

B. Alter column online

O exemplo a seguir mostra como executar uma operação de alter column com a opção ONLINE .

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores e Banco de Dados SQL do Azure.

CREATE TABLE dbo.doc_exy (column_a INT);
GO

INSERT INTO dbo.doc_exy (column_a) VALUES (10);
GO

ALTER TABLE dbo.doc_exy
    ALTER COLUMN column_a DECIMAL (5, 2) WITH (ONLINE = ON);
GO

EXECUTE sp_help doc_exy;
DROP TABLE dbo.doc_exy;
GO

Controle de versão do sistema

Os quatro exemplos a seguir ajudam você a se familiarizar com a sintaxe para usar o controle de versão do sistema. Para obter assistência adicional, consulte Introdução às tabelas temporais com versão do sistema.

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores e Banco de Dados SQL do Azure.

A. Adicionar o controle de versão do sistema a tabelas existentes

O exemplo a seguir mostra como adicionar o controle de versão do sistema a uma tabela existente e criar uma tabela de histórico futura. Este exemplo presume que há uma tabela existente chamada InsurancePolicy com uma chave primária definida. Este exemplo preenche as colunas de período recém-criadas para controle de versão do sistema usando valores padrão para os horários de início e término, porque esses valores não podem ser nulos. Este exemplo usa a HIDDEN cláusula para garantir que nenhum efeito sobre os aplicativos existentes que interagem com a tabela atual. Ele também usa HISTORY_RETENTION_PERIOD que está disponível somente no Banco de Dados SQL.

--Alter non-temporal table to define periods for system versioning
ALTER TABLE InsurancePolicy
    ADD ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
            DEFAULT SYSUTCDATETIME() NOT NULL,
        ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
            DEFAULT CONVERT (DATETIME2, '9999-12-31 23:59:59.99999999') NOT NULL,
        PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

--Enable system versioning with 1 year retention for historical data
ALTER TABLE InsurancePolicy SET (
    SYSTEM_VERSIONING = ON (
        HISTORY_RETENTION_PERIOD=1 YEAR
    )
);

B. Migrar uma solução existente para usar o controle de versão do sistema

O exemplo a seguir mostra como migrar para o controle de versão do sistema de uma solução que usa gatilhos para imitar simular o suporte temporal. O exemplo pressupõe que há uma solução existente que usa uma ProjectTask tabela e uma ProjectTaskHistory tabela para sua solução existente, que usa o Changed Date e Revised Date as colunas para seus períodos, que essas colunas de período não usam o tipo de dados datetime2 e que a ProjectTask tabela tem uma chave primária definida.

-- Drop existing trigger
DROP TRIGGER ProjectTask_HistoryTrigger;

-- Adjust the schema for current and history table
-- Change data types for existing period columns
ALTER TABLE ProjectTask ALTER COLUMN [Changed Date] DATETIME2 NOT NULL;
ALTER TABLE ProjectTask ALTER COLUMN [Revised Date] DATETIME2 NOT NULL;
ALTER TABLE ProjectTaskHistory ALTER COLUMN [Changed Date] DATETIME2 NOT NULL;
ALTER TABLE ProjectTaskHistory ALTER COLUMN [Revised Date] DATETIME2 NOT NULL;

-- Add SYSTEM_TIME period and set system versioning with linking two existing tables
-- (a certain set of data checks happen in the background)
ALTER TABLE ProjectTask
    ADD PERIOD FOR SYSTEM_TIME ([Changed Date], [Revised Date]);

ALTER TABLE ProjectTask SET (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE=dbo.ProjectTaskHistory, DATA_CONSISTENCY_CHECK=ON
    )
);

C. Desabilitar e reabilitar o controle de versão do sistema para alterar o esquema de tabela

Este exemplo mostra como desabilitar o controle de versão do sistema na tabela Department, adicionar uma coluna e reabilitar o controle de versão do sistema. É necessário desabilitar o controle de versão do sistema para modificar o esquema da tabela. Realize essas etapas em uma transação para impedir atualizações a ambas as tabelas ao atualizar o esquema da tabela, o que permite ao DBA ignorar a verificação de consistência de dados ao habilitar novamente o controle de versão do sistema e obter um benefício de desempenho. Tarefas como criação de estatísticas, alternância de partições ou aplicação de compactação de uma ou ambas as tabelas não exigem a desabilitação do controle de versão do sistema.

BEGIN TRAN
/* Takes schema lock on both tables */
ALTER TABLE Department
    SET (SYSTEM_VERSIONING = OFF) ;
/* expand table schema for temporal table */
ALTER TABLE Department
     ADD Col5 int NOT NULL DEFAULT 0 ;
/* Expand table schema for history table */
ALTER TABLE DepartmentHistory
    ADD Col5 int NOT NULL DEFAULT 0 ;
/* Re-establish versioning again*/
ALTER TABLE Department
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.DepartmentHistory,
                                 DATA_CONSISTENCY_CHECK = OFF)) ;
COMMIT

D. Remover o controle de versão do sistema

Este exemplo mostra como remover completamente o controle de versão do sistema da tabela Departamento e remover a tabela DepartmentHistory. Opcionalmente, você também pode querer remover as colunas de período usadas pelo sistema para registrar informações de controle de versão do sistema. Não é possível remover as tabelas Department ou DepartmentHistory enquanto a versão do sistema está habilitada.

ALTER TABLE Department
    SET (SYSTEM_VERSIONING = OFF);

ALTER TABLE Department
    DROP PERIOD FOR SYSTEM_TIME;

DROP TABLE DepartmentHistory;

Exemplos: Azure Synapse Analytics e PDW (Analytics Platform System)

Os exemplos a seguir de A a C usam a tabela FactResellerSales no banco de dados AdventureWorksPDW2022.

A. Determinar se uma tabela é particionada

A consulta a seguir retornará uma ou mais linhas se a tabela FactResellerSales for particionada. Se a tabela não for particionada, nenhuma linha será retornada.

SELECT *
FROM sys.partitions AS p
     INNER JOIN sys.tables AS t
         ON p.object_id = t.object_id
WHERE p.partition_id IS NOT NULL
      AND t.name = 'FactResellerSales';

B. Determinar os valores de limite para uma tabela particionada

A consulta a seguir retorna os valores de limite para cada partição na tabela FactResellerSales .

SELECT t.name AS TableName,
       i.name AS IndexName,
       p.partition_number,
       p.partition_id,
       i.data_space_id,
       f.function_id,
       f.type_desc,
       r.boundary_id,
       r.value AS BoundaryValue
FROM sys.tables AS t
     INNER JOIN sys.indexes AS i
         ON t.object_id = i.object_id
     INNER JOIN sys.partitions AS p
         ON i.object_id = p.object_id
        AND i.index_id = p.index_id
     INNER JOIN sys.partition_schemes AS s
         ON i.data_space_id = s.data_space_id
     INNER JOIN sys.partition_functions AS f
         ON s.function_id = f.function_id
     LEFT OUTER JOIN sys.partition_range_values AS r
         ON f.function_id = r.function_id
        AND r.boundary_id = p.partition_number
WHERE t.name = 'FactResellerSales'
      AND i.type <= 1
ORDER BY p.partition_number;

C. Determinar a coluna de partição para uma tabela particionada

A consulta a seguir retorna o nome da coluna de particionamento da FactResellerSales tabela.

SELECT t.object_id AS Object_ID,
       t.name AS TableName,
       ic.column_id AS PartitioningColumnID,
       c.name AS PartitioningColumnName
FROM sys.tables AS t
     INNER JOIN sys.indexes AS i
         ON t.object_id = i.object_id
     INNER JOIN sys.columns AS c
         ON t.object_id = c.object_id
     INNER JOIN sys.partition_schemes AS ps
         ON ps.data_space_id = i.data_space_id
     INNER JOIN sys.index_columns AS ic
         ON ic.object_id = i.object_id
        AND ic.index_id = i.index_id
        AND ic.partition_ordinal > 0
WHERE t.name = 'FactResellerSales'
      AND i.type <= 1
      AND c.column_id = ic.column_id;

D. Mesclar duas partições

O exemplo a seguir mescla duas partições em uma tabela.

A tabela Customer tem a seguinte definição:

CREATE TABLE Customer
(
    id INT NOT NULL,
    lastName VARCHAR (20),
    orderCount INT,
    orderDate DATE
)
WITH (
    DISTRIBUTION = HASH(id),
    PARTITION(orderCount RANGE LEFT
        FOR VALUES (1, 5, 10, 25, 50, 100)
    )
);

O comando a seguir combina os limites de partição 10 e 25.

ALTER TABLE Customer MERGE RANGE (10);

A nova DDL para a tabela é:

CREATE TABLE Customer
(
    id INT NOT NULL,
    lastName VARCHAR (20),
    orderCount INT,
    orderDate DATE
)
WITH (
    DISTRIBUTION = HASH(id),
    PARTITION(orderCount RANGE LEFT
        FOR VALUES (1, 5, 25, 50, 100)
    )
);

E. Dividir uma partição

O exemplo a seguir divide uma partição em uma tabela.

A tabela Customer tem a seguinte DDL:

DROP TABLE Customer;

CREATE TABLE Customer
(
    id INT NOT NULL,
    lastName VARCHAR (20),
    orderCount INT,
    orderDate DATE
)
WITH (
    DISTRIBUTION = HASH(id),
    PARTITION(orderCount RANGE LEFT
        FOR VALUES (1, 5, 10, 25, 50, 100)
    )
);

O comando a seguir cria um novo o limite de partição com o valor de 75, entre 50 e 100.

ALTER TABLE Customer SPLIT RANGE (75);

A nova DDL para a tabela é:

CREATE TABLE Customer (
   id INT NOT NULL,
   lastName VARCHAR(20),
   orderCount INT,
   orderDate DATE)
   WITH DISTRIBUTION = HASH(id),
   PARTITION ( orderCount (RANGE LEFT
      FOR VALUES (1, 5, 10, 25, 50, 75, 100))) ;

F. Usar SWITCH para mover uma partição para uma tabela de histórico

O exemplo a seguir move os dados em uma partição da tabela Orders para uma partição da tabela OrdersHistory.

A tabela Orders tem a seguinte DDL:

CREATE TABLE Orders
(
    id INT,
    city VARCHAR (25),
    lastUpdateDate DATE,
    orderDate DATE
)
WITH (
    DISTRIBUTION = HASH(id),
    PARTITION(orderDate RANGE RIGHT
        FOR VALUES ('2004-01-01', '2005-01-01', '2006-01-01', '2007-01-01')
    )
);

Neste exemplo, a tabela Orders tem as seguintes partições. Cada partição contém dados.

• Partição 1 (tem dados): OrderDate < '2004-01-01'
• Partição 2 (tem dados): '2004-01-01' <= OrderDate < '2005-01-01'
• Partição 3 (tem dados): '2005-01-01' <= OrderDate< '2006-01-01'
• Partição 4 (tem dados): '2006-01-01'<= OrderDate < '2007-01-01'
• Partição 5 (tem dados): '2007-01-01' <= OrderDate

A tabela OrdersHistory tem a seguinte DDL, que tem colunas e nomes de coluna idênticos aos da tabela Orders. Ambos são distribuídos por hash na coluna id.

CREATE TABLE OrdersHistory
(
    id INT,
    city VARCHAR (25),
    lastUpdateDate DATE,
    orderDate DATE
)
WITH (
    DISTRIBUTION = HASH(id),
    PARTITION(orderDate RANGE RIGHT
        FOR VALUES ('2004-01-01')
    )
);

Embora as colunas e os nomes de coluna devam ser iguais, os limites de partição não precisam ser. Neste exemplo, a tabela OrdersHistory tem as duas partições a seguir, e ambas estão vazias:

• Partição 1 (sem dados): OrderDate < '2004-01-01'
• Partição 2 (vazia): '2004-01-01' <= OrderDate

Para as duas tabelas anteriores, o comando a seguir move todas as linhas com OrderDate < '2004-01-01' da tabela Orders para a tabela OrdersHistory.

ALTER TABLE Orders SWITCH PARTITION 1 TO OrdersHistory PARTITION 1;

Como resultado, a primeira partição Orders está vazia e a primeira partição em OrdersHistory contém dados. As tabelas agora aparecem da seguinte maneira:

Tabela Orders

• Partição 1 (vazia): OrderDate < '2004-01-01'
• Partição 2 (tem dados): '2004-01-01' <= OrderDate < '2005-01-01'
• Partição 3 (tem dados): '2005-01-01' <= OrderDate< '2006-01-01'
• Partição 4 (tem dados): '2006-01-01'<= OrderDate < '2007-01-01'
• Partição 5 (tem dados): '2007-01-01' <= OrderDate

Tabela OrdersHistory

• Partição 1 (tem dados): OrderDate < '2004-01-01'
• Partição 2 (vazia): '2004-01-01' <= OrderDate

Para limpar a Orders tabela, você pode remover a partição vazia mesclando 1 partições e 2 da seguinte maneira:

ALTER TABLE Orders MERGE RANGE ('2004-01-01');

Após a mesclagem, a tabela Orders tem as seguintes partições:

Tabela Orders

• Partição 1 (tem dados): OrderDate < '2005-01-01'
• Partição 2 (tem dados): '2005-01-01' <= OrderDate< '2006-01-01'
• Partição 3 (tem dados): '2006-01-01'<= OrderDate < '2007-01-01'
• Partição 4 (tem dados): '2007-01-01' <= OrderDate

Suponha que se passe outro ano e você esteja pronto para arquivar o ano de 2005. Você pode alocar uma partição vazia para o ano 2005 na tabela OrdersHistory dividindo a partição vazia da seguinte maneira:

ALTER TABLE OrdersHistory SPLIT RANGE ('2005-01-01');

Após a divisão, a tabela OrdersHistory tem as seguintes partições:

Tabela OrdersHistory

• Partição 1 (tem dados): OrderDate < '2004-01-01'
• Partição 2 (vazia): '2004-01-01' < '2005-01-01'
• Partição 3 (vazia): '2005-01-01' <= OrderDate

Conteúdo relacionado

• sys.tables
• sp_rename
• sp_help
• DADOS DE EVENTOS (Transact-SQL)
• CRIAR TABELA (Transact-SQL)
• MESA DE QUEDA (Transact-SQL)
• ALTERAR TABELA column_constraint (Transact-SQL)
• ALTER TABELA column_definition (Transact-SQL)
• ALTER TABELA computed_column_definition (Transact-SQL)
• ALTERAR TABELA index_option (Transact-SQL)
• ALTERAR TABELA table_constraint (Transact-SQL)