TABELA ALTER (Transact-SQL)

se aplica a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada SQL do AzureArmazém dedo Azure Synapse AnalyticsAnalytics Platform System (PDW) no Microsoft Fabric

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

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

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 } )
}

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 para o Azure Synapse Analytics e o Parallel Data Warehouse

-- Syntax for Azure Synapse Analytics and 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 do Armazém na Malha

-- Syntax for Warehouse om Microsoft Fabric:

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 { [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
    }

Argumentos

database_name

O nome do banco de dados no qual 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 contida pelo esquema de propriedade do usuário atual, você deverá especificar explicitamente o banco de dados e o esquema.

COLUNA ALTER

Especifica que a coluna nomeada deve ser alterada ou alterada.

A coluna modificada não pode ser:

• Uma coluna com um carimbo de data/hora tipo de dados.

• 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 descartar as estatísticas antes que ALTER COLUMN possa ter sucesso. Execute esta consulta para obter todas as estatísticas criadas pelo usuário e colunas de estatísticas 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>');
  

  Observação

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

• Usado em uma restrição de CHAVE PRIMÁRIA ou referências de [CHAVE ESTRANGEIRA].

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

• Associado a uma definição padrão. No entanto, o comprimento, a precisão ou a escala de uma coluna podem ser alterados se o tipo de dados não for alterado.

O tipo de dados de texto, ntexte imagem colunas podem ser alterados somente das seguintes maneiras:

• texto para varchar(max), nvarchar(max)ou xml
• ntext para varchar(max), nvarchar(max)ou xml
• imagem para 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 causar a conversão de caracteres estendidos. Para obter mais informações, consulte CAST e CONVERT. Reduzir a precisão ou a escala de uma coluna pode causar truncamento de dados.

Ao usar Always Encrypted (sem enclaves seguros), se a coluna que está sendo modificada estiver criptografada com 'ENCRYPTED WITH', você pode alterar o tipo de dados para um tipo de dados compatível (como INT para BIGINT), mas não pode alterar nenhuma configuração de criptografia.

Ao usar Always Encrypted com enclaves seguros, você pode 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 você estiver alterando a chave) oferecer suporte a cálculos de enclave (criptografados com chaves mestras de coluna habilitadas para enclave). Para obter detalhes, consulte Always Encrypted with secure enclaves.

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 de coluna anterior como uma coluna solta. 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, recrie 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 descartada. O column_name máximo é de 128 caracteres. Para novas colunas, você pode omitir column_name para colunas criadas com um carimbo de data/hora tipo de dados. O nome de carimbo de data/hora será usado se você não especificar column_name para uma coluna de carimbo de data/hora tipo de dados.

[ type_schema_name. ] type_name

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

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

A seguir estão os critérios para type_name de uma coluna alterada:

• O tipo de dados anterior deve ser implicitamente convertível para o novo tipo de dados.
• type_name não pode ser carimbo de data/hora.
• ANSI_NULL os padrões estão sempre ativos para ALTER COLUMN; Se não for especificado, a coluna será anulável.
• ANSI_PADDING preenchimento está sempre ATIVADO para ALTER COLUMN.
• Se a coluna modificada for uma coluna de identidade, new_data_type deverá ser um tipo de dados que ofereça suporte à propriedade identity.
• A configuração atual para SET ARITHABORT é ignorada. ALTER TABLE opera como se ARITHABORT estivesse definido como ON.

precisão

A precisão para o tipo de dados especificado. Para obter mais informações sobre valores de precisão válidos, consulte Precision, Scale and Length.

escala

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

máx.

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

xml_schema_collection

Aplica-se a: SQL Server (SQL Server 2008 (10.0.x) e posterior) e Banco de Dados SQL do Azure.

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

COLLATE <collation_name>

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

A cláusula COLLATE altera os agrupamentos somente de colunas dos char, varchar, nchare nvarchar tipos de dados. Para alterar o agrupamento de uma coluna de tipo de dados de alias definida pelo usuário, use instruções ALTER TABLE separadas para alterar a coluna para um tipo de dados do sistema SQL Server. Em seguida, altere seu agrupamento e altere a coluna de volta para um tipo de dados de alias.

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

• Se uma restrição CHECK, uma restrição FOREIGN KEY ou colunas computadas fizer referência à coluna alterada.
• Se qualquer índice, estatística ou índice de texto completo for criado na coluna. As estatísticas criadas automaticamente na coluna alterada são descartadas se o agrupamento de colunas for alterado.
• Se uma exibição ou função vinculada ao esquema fizer referência à coluna.

Para obter mais informações, consulte COLLATE.

NULO | NÃO NULO

Especifica se a coluna pode aceitar valores nulos. As colunas que não permitem valores nulos são adicionadas com ALTER TABLE somente se tiverem um padrão especificado ou se a tabela estiver vazia. Você pode especificar NOT NULL para colunas computadas somente 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, 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, será necessário adicionar uma definição PADRÃO com a nova coluna. Além disso, a nova coluna é carregada automaticamente com o valor padrão nas novas colunas em cada linha existente.

Você pode especificar NULL em ALTER COLUMN para forçar uma coluna NOT NULL para permitir valores nulos, exceto para colunas em restrições de CHAVE PRIMÁRIA. Você pode especificar NOT NULL em ALTER COLUMN somente se a coluna não contiver valores nulos. Os valores nulos devem ser atualizados para algum valor antes que a ALTER COLUMN NOT NULL seja permitida, 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 CREATE TABLE ou ALTER TABLE, as configurações do banco de dados e da sessão influenciam e, possivelmente, substituem a anulabilidade do tipo de dados usado em uma definição de coluna. Certifique-se de sempre definir 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, certifique-se de definir a coluna com a mesma anulabilidade que o tipo de dados definido pelo usuário. E especifique um valor padrão para a coluna. Para obter mais informações, consulte CREATE TABLE.

[ {ADICIONAR | DROP} ROWGUIDCOL ]

Aplica-se a: SQL Server (SQL Server 2008 (10.0.x) e posterior) e Banco de Dados SQL do Azure.

Especifica que a propriedade ROWGUIDCOL é adicionada ou descartada da coluna especificada. ROWGUIDCOL indica que a coluna é uma coluna GUID de linha. Você pode definir apenas um identificador exclusivo coluna por tabela como a coluna ROWGUIDCOL. Além disso, você só pode atribuir a propriedade ROWGUIDCOL a uma coluna uniqueidentifier. Não é possível atribuir ROWGUIDCOL a uma coluna de um tipo de dados definido pelo usuário.

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

[ {ADICIONAR | DROP} PERSISTIU ]

Especifica que a propriedade PERSISTED é adicionada ou descartada da coluna especificada. A coluna deve ser uma coluna computada definida com uma expressão determinística. 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. Ao marcar 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 estar ATIVADO quando você estiver criando ou alterando índices em colunas computadas ou exibições indexadas. Para obter mais informações, consulte SET QUOTED_IDENTIFIER (Transact-SQL).

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

DROP NOT PARA REPLICAÇÃO

Aplica-se a: SQL Server (SQL Server 2008 (10.0.x) e posterior) 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. Você pode especificar essa cláusula somente se column_name for uma coluna de identidade.

ESPARSO

Indica que a coluna é uma coluna esparsa. O armazenamento de colunas esparsas é otimizado para valores nulos. Não é possível definir colunas esparsas como NOT NULL. Quando você converte uma coluna de esparsa para não esparsa, ou de não esparsa para esparsa, essa opção bloqueia a tabela durante a execução do comando. Pode ser necessário usar a cláusula REBUILD 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 ( FUNCTION = ' mask_function ')

Aplica-se a: SQL Server (SQL Server 2016 (13.x) e posterior) e Banco de Dados SQL do Azure.

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

• padrão()
• e-mail()
• parcial()
• aleatório()

Requer a permissão ALTER ANY MASK.

Para largar uma máscara, use DROP MASKED. Para parâmetros de função, consulte Dynamic Data Masking.

Adicionar e soltar uma máscara requer ALTERAR QUALQUER MÁSCARA permissão.

COM ( ONLINE = ON | OFF) <como se aplica à alteração de uma coluna>

Aplica-se a: SQL Server (SQL Server 2016 (13.x) e posterior) e Banco de Dados SQL do Azure.

Permite que muitas ações de coluna de alteração sejam executadas enquanto a tabela permanece disponível. O padrão é OFF. Você pode executar alterar coluna online para alterações de coluna relacionadas ao tipo de dados, comprimento ou precisão da coluna, anulabilidade, esparsidade e agrupamento.

A coluna de alteração on-line permite que o usuário crie e as estatísticas automáticas façam referência à coluna alterada durante a operação ALTER COLUMN, o que permite que as consultas sejam executadas normalmente. No final da operação, os autostats que fazem referência à coluna são descartados 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 quaisquer estatísticas ou índices, não será possível executar uma operação de alteração de coluna.

• Enquanto a operação de coluna de alteração online está em execução, todas as operações que podem depender da coluna (índice, exibições e assim por diante.) bloqueiam ou falham com um erro apropriado. Esse comportamento garante que a coluna de alteração online não falhará devido a dependências introduzidas durante a execução da operação.
• Não há suporte para alterar uma coluna de NOT NULL para NULL como uma operação online quando a coluna alterada é referenciada por índices não clusterizados.
• O ALTER online não é suportado quando a coluna é referenciada por uma restrição de verificação e a operação ALTER 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 coluna alter online.
• ALTER COLUMN ... ADD/DROP PERSISTED não é suportado para a coluna Alter online.
• ALTER COLUMN ... ADD/DROP ROWGUIDCOL/NOT FOR REPLICATION não é afetado pela coluna alter online.
• A coluna de alteração online não oferece suporte à alteração de uma tabela em que o controle de alterações esteja habilitado ou que seja um editor de replicação de mesclagem.
• A coluna de alteração online não suporta a alteração de ou para tipos de dados CLR.
• A coluna de alteração online não oferece suporte à alteração para um tipo de dados XML que tenha uma coleção de esquema diferente da coleção de esquema atual.
• A coluna de alteração online não reduz as restrições sobre quando uma coluna pode ser alterada. Referências por índice/estatísticas, e assim por diante, podem fazer com que a alteração falhe.
• A coluna de alteração online não suporta a alteração simultânea de mais de uma coluna.
• A coluna de alteração online não tem efeito em uma tabela temporal com versão do sistema. A coluna ALTER não é executada como online, independentemente do valor especificado para a opção ONLINE.

A coluna de alteração online tem requisitos, restrições e funcionalidades semelhantes aos da reconstrução de índice online, que inclui:

• Não há suporte para a reconstrução de índice online quando a tabela contém colunas LOB ou de fluxo de arquivos herdadas ou quando a tabela tem um índice columnstore. As mesmas limitações aplicam-se à coluna de alteração online.
• Uma coluna existente sendo alterada requer o dobro da 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 online de coluna alter segue o mesmo padrão de bloqueio usado para a compilação de índice online.

COM CHEQUE | COM NOCHECK

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

Se você não quiser verificar novas restrições de CHECK ou FOREIGN KEY em relação aos dados existentes, use WITH NOCHECK. Não recomendamos fazer isso, exceto em casos raros. A nova restrição é avaliada em todas as atualizações de dados posteriores. Qualquer violação de restrição suprimida por WITH NOCHECK quando 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 COM NOCHECK. Essas restrições são ignoradas até que sejam reativadas 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.

ÍNDICE ALTER index_name

Especifica que a contagem de buckets para index_name deve ser alterada ou alterada.

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

ADICIONAR

Especifica que uma ou mais definições de coluna, definições de coluna computadas 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, você pode adicionar um índice.

PERÍODO PARA SYSTEM_TIME ( system_start_time_column_name, system_end_time_column_name )

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

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

Use esse argumento com o argumento SET SYSTEM_VERSIONING para tornar uma tabela existente uma tabela temporal. Para obter mais informações, consulte Tabelas Temporais e Introdução às Tabelas Temporais no Banco de Dados SQL do Azure.

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

QUEDA

Especifica que uma ou mais definições de coluna, definições de coluna computadas ou restrições de tabela são descartadas ou para descartar a especificação para as colunas que o sistema usa para controle de versão do sistema.

RESTRIÇÃO constraint_name

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

Você pode determinar o nome da restrição definido pelo usuário ou fornecido pelo sistema consultando as exibições de catálogo sys.check_constraint, sys.default_constraints, sys.key_constraintse sys.foreign_keys.

Uma restrição de chave primária não pode ser descartada se existir um índice XML na tabela.

ÍNDICE index_name

Especifica que index_name é removido da tabela.

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

COLUNA column_name

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

Uma coluna não pode ser descartada quando:

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

PERÍODO PARA SYSTEM_TIME

Aplica-se a: SQL Server (SQL Server 2016 (13.x) e posterior) e Banco de Dados SQL do Azure.

Descarta a especificação para as colunas que o sistema usará para o controle de versão do sistema.

COM <drop_clustered_constraint_option>

Especifica que uma ou mais opções de restrição clusterizadas de descarte são definidas.

MAXDOP = max_degree_of_parallelism

Aplica-se a: SQL Server (SQL Server 2008 (10.0.x) e posterior) e Banco de Dados SQL do Azure.

Substitui o grau máximo de paralelismo opção de configuração apenas durante a operação. Para obter mais informações, consulte Configurar o grau máximo de paralelismo Opção de configuração do servidor.

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 planos paralelos.

• >1

  Restringe o número máximo de processadores usados em uma operação de índice paralelo 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 = { LIGADO | OFF } <como 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 operação ONLINE.

EM
Os bloqueios de tabela de longo prazo não são mantidos durante a operação de índice. Durante a fase principal da operação de índice, apenas um bloqueio de compartilhamento de intenção (IS) é mantido na tabela de origem. Esse comportamento permite que consultas ou atualizações para a tabela subjacente e índices para continuar. No início da operação, um bloqueio compartilhado (S) é mantido no objeto de origem por um curto período de tempo. No final da operação, por um curto período de tempo, um bloqueio S (compartilhado) é 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 reconstruído. ONLINE não pode ser definido como ON quando um índice está sendo criado em uma tabela temporária local. Somente a operação de reconstrução de heap de thread único é permitida.

Para executar a DDL para SWITCH ou reconstrução de índice online, todas as transações de bloqueio ativas em execução em uma tabela específica devem ser concluídas. Durante a execução, a operação de ou reconstrução do SWITCH impede o início de novas transações e pode afetar significativamente a taxa de transferência da carga de trabalho e atrasar temporariamente o acesso à tabela subjacente.

DESLIGADO
Os bloqueios de tabela aplicam-se durante a operação de índice. Uma operação de índice offline que cria, reconstrói ou descarta um índice clusterizado, ou recria ou descarta um índice não clusterizado, adquire um bloqueio de modificação de esquema (Sch-M) na tabela. Esse bloqueio impede todo o acesso do usuário à tabela subjacente durante 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 na tabela subjacente, mas permite operações de leitura, como instruções SELECT. São permitidas operações de reconstrução de heap multi-threaded.

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

MOVER PARA { partition_scheme_name(column_name [ ,...n ] ) | de grupo de arquivos | "padrão" }

Aplica-se a: SQL Server (SQL Server 2008 (10.0.x) e posterior) e Banco de Dados SQL do Azure.

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

{ VERIFICAR | NOCHECK } RESTRIÇÃO

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

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

{ ATIVAR | DESATIVAR } GATILHO

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

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

trigger_name
Especifica o nome do gatilho a ser desabilitado ou habilitado.

{ ATIVAR | DESATIVAR } CHANGE_TRACKING

Aplica-se a: SQL Server (SQL Server 2008 (10.0.x) e posterior) e Banco de Dados SQL do Azure.

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

Essa opção está disponível somente quando o controle de alterações está habilitado para o banco de dados. Para obter mais informações, consulte ALTER DATABASE SET Options.

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

COM ( TRACK_COLUMNS_UPDATED = { EM | DESLIGADO } )

Aplica-se a: SQL Server (SQL Server 2008 (10.0.x) e posterior) e Banco de Dados SQL do Azure.

Especifica se o Mecanismo de Banco de Dados rastreia quais colunas controladas foram atualizadas. O valor padrão é OFF.

MUDE [ PARTIÇÃO source_partition_number_expression ] PARA [ schema_name. ] target_table [ PARTIÇÃO target_partition_number_expression ]

Aplica-se a: SQL Server (SQL Server 2008 (10.0.x) e posterior) e Banco de Dados SQL do Azure.

Alterna um bloco de dados de uma das seguintes maneiras:

• Reatribui todos os dados de uma tabela como uma partição a 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 a uma tabela não particionada existente.

Se tabela for uma tabela particionada, você deverá especificar source_partition_number_expression. Se target_table estiver particionado, você deverá especificar target_partition_number_expression. Ao reatribuir os dados de uma tabela como uma partição a uma tabela particionada já existente ou alternar uma partição de uma tabela particionada para outra, a partição de destino deve existir e deve estar vazia.

Ao reatribuir os dados de uma partição para formar uma única tabela, a tabela de destino já deve existir e deve estar vazia. Tanto a tabela ou partição de origem quanto a tabela ou partição de destino devem estar localizadas no mesmo grupo de arquivos. Os índices correspondentes, ou partições de índice, também devem estar localizados no mesmo grupo de arquivos. Muitas restrições adicionais se aplicam à comutação de partições. tabela e target_table não pode ser a mesma. target_table pode ser um identificador de várias partes.

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

Uma tabela particionada com um índice columnstore clusterizado se comporta como uma pilha particionada:

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

Para restrição de SWITCH ao usar a replicação, consulte Replicar tabelas particionadas e índices.

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 reconstruir índices columnstore não clusterizados para o formato atual (que é atualizável) antes que qualquer operação PARTITION possa ser executada.

Limitações

Se ambas as tabelas forem particionadas de forma idêntica, incluindo índices não clusterizados, e a tabela de destino não tiver nenhum índice não clusterizado, 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 | "inadimplência" | "NUL" })

Aplica-se a: SQL Server (SQL Server 2008 (10.0.x) e posterior). A Base de Dados SQL do Azure não suporta FILESTREAM.

Especifica onde os dados FILESTREAM são armazenados.

ALTER TABLE com a cláusula SET FILESTREAM_ON só terá êxito se a tabela não tiver colunas FILESTREAM. Você pode adicionar colunas FILESTREAM usando uma segunda instrução ALTER TABLE.

Se você especificar partition_scheme_name, as regras para CREATE TABLE se aplicar. Certifique-se de que a tabela já está 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 um erro resulta.

"padrão " especifica o grupo de arquivos FILESTREAM com o conjunto de propriedades DEFAULT. Se não houver nenhum grupo de arquivos FILESTREAM, ocorrerá 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 = { DESLIGADO | ON [ ( HISTORY_TABLE = schema_name . history_table_name [ , DATA_CONSISTENCY_CHECK = { ON | DESLIGADO } ] ] ] } )

Aplica-se a: SQL Server (SQL Server 2016 (13.x) e posterior) e Banco de Dados SQL do Azure.

Desativa 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 os requisitos de tipo de dados, restrição de anulabilidade e restrição de chave primária para controle de versão do sistema foram atendidos. O sistema registrará o histórico de cada registro na tabela com versão do sistema em uma tabela de histórico separada. Se o argumento HISTORY_TABLE não for usado, o nome desta 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 correspondente ao esquema da tabela atual, criará um link entre as duas tabelas e permitirá que o sistema registre o histórico de cada registro na tabela atual na tabela de histórico. Se você usar o argumento HISTORY_TABLE para criar um link e usar uma tabela de histórico existente, o sistema criará um link entre a tabela atual e a tabela especificada. Ao criar um link para uma tabela de histórico existente, você pode optar por fazer 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. Executar a verificação de consistência de dados é o padrão. Use o argumento SYSTEM_VERSIONING = ON em uma tabela definida com a cláusula PERIOD FOR SYSTEM_TIME para tornar a tabela existente uma tabela temporal. Para obter mais informações, consulte Tabelas temporais.

HISTORY_RETENTION_PERIOD = { INFINITO | número {DIA | DIAS | SEMANA | SEMANAS | MÊS | MESES | ANO | ANOS} }

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 uma tabela temporal. Se omitido, a retenção infinita é assumida.

DATA_DELETION

Aplica-se a: Borda SQL do Azure apenas

Permite a limpeza baseada em política de retenção de dados antigos ou antigos de tabelas dentro de um banco de dados. Para obter mais informações, consulte Habilitar e desabilitar a retenção de dados. Os parâmetros a seguir devem 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 seguintes tipos de dados são permitidos para a coluna de filtro.

• Data
• Data/Hora
• DateTime2
• SmallDateTime
• DateTimeOffset

RETENTION_PERIOD = { INFINITO | número {DIA | DIAS | SEMANA | SEMANAS | MÊS | MESES | ANO | ANOS }}
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 peça de data.

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

Aplica-se a: SQL Server (SQL Server 2008 (10.0.x) e posterior) e Banco de Dados SQL do Azure.

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

AUTOMÁTICO
Essa opção permite que o Mecanismo de Banco de Dados do SQL Server selecione a granularidade de escalonamento de bloqueio apropriada para o esquema de tabela.

• Se a tabela for particionada, o escalonamento de bloqueio será permitido para a granularidade de heap ou B-tree (HoBT). Em outras palavras, o escalonamento será permitido para o nível de partição. Depois que o bloqueio for escalado para o nível HoBT, o bloqueio não será escalado posteriormente para a granularidade TABLE.
• Se a tabela não estiver particionada, o escalonamento de bloqueio será feito para a granularidade TABLE.

TABELA
O escalonamento de bloqueio é feito com granularidade no nível da tabela, independentemente de a tabela ser particionada ou não. TABLE é o valor padrão.

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

RECONSTRUIR

Use a sintaxe REBUILD WITH para reconstruir 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 operação ONLINE.

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

PARTIÇÃO = TODOS

Aplica-se a: SQL Server (SQL Server 2008 (10.0.x) e posterior) e Banco de Dados SQL do Azure.

Reconstrói todas as partições ao alterar as configurações de compactação de 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 apenas por algumas das opções.

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

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

DATA_COMPRESSION

Aplica-se a: SQL Server (SQL Server 2008 (10.0.x) e posterior) e Banco de Dados SQL do Azure.

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

NONE Tabela ou partições especificadas não são compactadas. Esta opção não se aplica a tabelas columnstore.

A tabela ROW ou as partições especificadas são compactadas usando a compactação de linha. Esta opção não se aplica a tabelas columnstore.

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

COLUMNSTORE
Aplica-se a: SQL Server (SQL Server 2014 (12.x) e posterior) e Banco de Dados SQL do Azure.

Aplica-se somente a tabelas columnstore. COLUMNSTORE especifica descompactar uma partição que foi compactada com a opção COLUMNSTORE_ARCHIVE. Quando os dados são restaurados, eles continuam a ser compactados com a compactação columnstore usada para todas as tabelas columnstore.

COLUMNSTORE_ARCHIVE
Aplica-se a: SQL Server (SQL Server 2014 (12.x) e posterior) e Banco de Dados SQL do Azure.

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

Para reconstruir várias partições ao mesmo tempo, consulte 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 de compactação de dados .

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

XML_COMPRESSION

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

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

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

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

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

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

EM
Os bloqueios de tabela de longo prazo não são mantidos durante a operação de índice. O bloqueio S na tabela é necessário no início da reconstrução do índice e um bloqueio de Sch-M na tabela no final da reconstrução do índice online. Embora ambos os bloqueios sejam bloqueios de metadados curtos, o bloqueio Sch-M deve aguardar que todas as transações de bloqueio sejam concluídas. Durante o tempo de espera, o bloqueio Sch-M bloqueia todas as outras transações que aguardam atrás desse bloqueio ao acessar a mesma tabela.

DESLIGADO
Os bloqueios de tabela são aplicados durante a operação de índice. Isso impede que todo o usuário acesse a tabela subjacente durante a operação.

column_set_name COLUMN_SET XML PARA ALL_SPARSE_COLUMNS

Aplica-se a: SQL Server (SQL Server 2008 (10.0.x) e posterior) e Banco de Dados SQL do Azure.

O nome do conjunto de colunas. Um conjunto de colunas é uma representação 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 contenha colunas esparsas. Para obter mais informações sobre conjuntos de colunas, consulte Usar conjuntos de colunas.

{ ATIVAR | DESATIVAR } FILETABLE_NAMESPACE

Aplica-se a: SQL Server (SQL Server 2012 (11.x) e posterior).

Habilita ou desabilita as restrições definidas pelo sistema em uma FileTable. Só pode ser usado com uma FileTable.

SET ( FILETABLE_DIRECTORY = directory_name )

Aplica-se a: SQL Server (SQL Server 2012 (11.x) e posterior). A Base de Dados SQL do Azure não suporta FILETABLE.

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

REMOTE_DATA_ARCHIVE

Aplica-se a: SQL Server (SQL Server 2017 (14.x) e posterior).

Habilita ou desabilita o Stretch Database para uma tabela. Para obter mais informações, consulte Stretch Database.

Habilitando o Stretch Database para uma tabela

Ao habilitar o Stretch para uma tabela especificando ON, você também precisa especificar MIGRATION_STATE = OUTBOUND começar a migrar dados imediatamente ou MIGRATION_STATE = PAUSED adiar a migração de dados. O valor padrão é MIGRATION_STATE = OUTBOUND. Para obter mais informações sobre como habilitar o Stretch para uma tabela, consulte Habilitar o Stretch Database para uma tabela.

Pré-requisitos. Antes de habilitar o Stretch para uma tabela, você precisa habilitar o Stretch no servidor e no banco de dados. Para obter mais informações, consulte Habilitar o Stretch Database para um banco de dados.

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

Desativando o Stretch Database para uma tabela

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

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

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

Esta operação implica custos de transferência de dados e não pode ser cancelada. Para obter mais informações, consulte Detalhes de preços de transferências de dados.

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

• Para desativar o Stretch para uma tabela e abandonar os dados remotos, execute o seguinte comando.

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

Depois de desativar o Stretch Database para uma tabela, a migração de dados para e os resultados da consulta deixam de incluir resultados da tabela remota.

A desativação do Stretch não remove a tabela remota. Se quiser excluir a tabela remota, solte-a usando o portal do Azure.

[ FILTER_PREDICATE = { null | predicado } ]

Aplica-se a: SQL Server (SQL Server 2017 (14.x) e posterior).

Opcionalmente, especifica um predicado de filtro para selecionar linhas a serem migradas de uma tabela que contém dados históricos e atuais. O predicado deve chamar uma função determinística com valor de tabela embutido. Para obter mais informações, consulte Habilitar o Stretch Database para uma tabela e Selecionar linhas para migrar usando uma função de filtro - Stretch Database.

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

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

MIGRATION_STATE = { SAÍDA | ENTRADA | PAUSADO }

Aplica-se a: SQL Server (SQL Server 2017 (14.x) e posterior).

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

• Especifique INBOUND copiar os dados remotos da tabela do Azure de volta para o SQL Server e desabilitar o Stretch para a tabela. Para obter mais informações, consulte Desabilitar o Stretch Database e trazer de volta dados remotos.

  Esta operação implica custos de transferência de dados e não pode ser cancelada.

• Especifique PAUSED pausar ou adiar a migração de dados. Para obter mais informações, consulte Pausar e retomar a migração de dados - Stretch Database.

WAIT_AT_LOW_PRIORITY

Aplica-se a: SQL Server (SQL Server 2014 (12.x) e posterior) e Banco de Dados SQL do Azure.

Uma reconstrução de índice on-line tem que esperar por operações de bloqueio nesta tabela. WAIT_AT_LOW_PRIORITY indica que a operação de reconstrução de índice online aguarda bloqueios de baixa prioridade, permitindo que outras operações continuem enquanto a operação de compilação de índice online está aguardando. Omitir a opção WAIT AT LOW PRIORITY é o mesmo que .

MAX_DURATION = tempo [MINUTOS ]
Aplica-se a: SQL Server (SQL Server 2014 (12.x) e posterior) e Banco de Dados SQL do Azure.

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

ABORT_AFTER_WAIT = [NENHUM | PRÓPRIO | BLOQUEADORES } ]

Aplica-se a: SQL Server (SQL Server 2014 (12.x) e posterior) e Banco de Dados SQL do Azure.

NENHUM
Continue aguardando o cadeado com prioridade normal (regular).

EU PRÓPRIO
Saia da de SWITCH ou da operação DDL de reconstrução de índice online que está sendo executada no momento sem tomar nenhuma ação.

BLOQUEADORES
Mate todas as transações de usuário que atualmente bloqueiam o SWITCH ou a operação DDL de reconstrução de índice on-line para que a operação possa continuar.

Requer ALTERAR QUALQUER CONEXÃO permissão.

SE EXISTE

Aplica-se a: SQL Server (SQL Server 2016 (13.x) e posterior) e Banco de Dados SQL do Azure.

Condicionalmente descarta a coluna ou restrição somente se ela já existir.

RESUMÍVEL = { EM | DESLIGADO}

Aplica-se a: SQL Server 2022 (16.x) e posterior.

Especifica se uma operação de ALTER TABLE ADD CONSTRAINT é retomável. Adicionar operação de restrição de tabela é retomável quando ON. Adicionar operação de restrição de tabela não é retomada quando OFF. O padrão é OFF. A opção pode ser usada como parte do index_option ALTER TABLE no ALTER TABLE table_constraint.

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

Para obter mais informações sobre como habilitar e usar operações de ALTER TABLE ADD CONSTRAINT retomáveis, consulte tabela Resumable adicionar restrições.

Comentários

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

Se houver planos de execução no cache de procedimentos que façam referência à tabela, ALTER TABLE os marcará para serem recompilados em sua próxima execução.

No banco de dados SQL no Microsoft Fabric, alguns recursos de tabela podem ser criados, mas não serão espelhados no Fabric OneLake. Para obter mais informações, consulte Limitações do espelhamento do banco de dados SQL de malha.

Alterar o tamanho de uma coluna

Você pode 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 cláusula ALTER COLUMN. Se existirem dados 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 varchar, nvarcharou tipo de dados varbinary e o índice não seja o resultado de uma restrição de CHAVE PRIMÁRIA. Veja o exemplo na pequena seção intitulada Alterando uma definição de coluna.

Fechaduras e ALTER TABLE

As alterações especificadas em ALTER TABLE são implementadas imediatamente. Se as alterações exigirem modificações das linhas na tabela, ALTER TABLE atualiza 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 operações de índice on-line que exigem um bloqueio de SCH-M curto no final. Em uma operação ALTER TABLE...SWITCH, o bloqueio é adquirido nas tabelas de origem e de destino. As modificações feitas na tabela são registradas e totalmente recuperáveis. Alterações que afetam todas as linhas em tabelas grandes, como soltar uma coluna ou, em algumas edições do SQL Server, adicionar uma coluna NOT NULL com um valor padrão, podem levar muito tempo para serem concluídas e gerar muitos registros de log. Execute essas instruções ALTER TABLE com o mesmo cuidado que qualquer instrução INSERT, UPDATE ou DELETE que afete muitas linhas.

Aplica-se ao Warehouse no Microsoft Fabric.

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

XEvents para switch de partição

Os XEvents a seguir estão relacionados a ALTER TABLE ... SWITCH PARTITION e reconstruções de índice on-line.

• lock_request_priority_state
• process_killed_by_abort_blockers
• ddl_with_wait_at_low_priority

Adicionar colunas NOT NULL como uma operação online

A partir do SQL Server 2012 (11.x) Enterprise Edition, adicionar uma coluna NOT NULL com um valor padrão é uma operação online quando o valor padrão é uma constante de tempo de execução . 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 apenas 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 sintaxe ADD COLUMN. Uma constante de tempo de execução é uma expressão que produz o mesmo valor em tempo de execução para cada linha da tabela, apesar de seu determinismo. Por exemplo, a expressão constante "Meus dados temporários" ou a função do sistema GETUTCDATETIME() são constantes de tempo de execução. Por outro lado, as funções NEWID() ou NEWSEQUENTIALID() não são constantes de tempo de execução, porque um valor exclusivo é produzido para cada linha na tabela. Adicionar uma coluna NOT NULL com um valor padrão que não seja uma constante de tempo de execução é sempre executado offline e um bloqueio exclusivo (SCH-M) é adquirido durante a operação.

Enquanto as linhas existentes fazem referência ao valor armazenado nos metadados, o valor padrão é armazenado na linha para quaisquer novas linhas inseridas e não especificam outro valor para a coluna. O valor padrão armazenado nos metadados é movido para uma linha existente quando a linha é atualizada (mesmo que a coluna real não seja especificada na instrução UPDATE) ou se a tabela ou o índice clusterizado for reconstruído.

Colunas do tipo varchar(max), nvarchar(max), varbinary(max), xml, texto, ntext, image, hierarchyid, geometry, geographyou CLR UDTS não podem ser adicionadas em uma operação online. Uma coluna não pode ser adicionada online se isso fizer com que o tamanho máximo possível da linha exceda o limite de 8.060 bytes. Neste caso, a coluna é adicionada como uma operação offline.

Execução paralela do plano

No SQL Server 2012 (11.x) Enterprise Edition e versões posteriores, o número de processadores empregados para executar uma única instrução ALTER TABLE ADD (baseada em índice) CONSTRAINT ou DROP (índice clusterizado) CONSTRAINT é determinado pelo grau máximo de paralelismo opção de configuração e pela carga de trabalho atual. Se o Mecanismo de Banco de Dados detetar que o sistema está ocupado, o grau de paralelismo da operação será reduzido automaticamente 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 Configurar o grau máximo de paralelismo Opção de configuração do servidor.

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 da mesma forma que é usado para tabelas não particionadas. No entanto, essa instrução não pode ser usada para alterar a maneira como 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 de uma tabela particionada.

Restrições em tabelas com exibições vinculadas ao esquema

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

Adicionar ou remover gatilhos em tabelas base não é afetado pela criação de uma exibição vinculada ao esquema que faz referência às tabelas.

Índices e ALTER TABLE

Os índices criados como parte de uma restrição são descartados quando a restrição é descartada. Os índices que foram criados com CREATE INDEX devem ser descartados com DROP INDEX. Use a instrução ALTER INDEX para reconstruir 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 restrições baseados em uma coluna devem ser removidos antes que a coluna possa ser removida.

Quando você exclui uma restrição que criou um índice clusterizado, 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 soltar 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 opção MOVER PARA 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 soltar um índice clusterizado, especifique a opção ONLINE **=** ON para que a transação DROP INDEX 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 desativados. Os índices desativados devem ser descartados usando ONLINE = OFF.
• Apenas um índice de cada vez pode ser descartado.
• 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.

Espaço em disco temporário igual ao tamanho do índice clusterizado existente é necessário para descartar um índice clusterizado. Este espaço adicional é libertado assim que a operação é concluída.

Replicar alterações de esquema

Quando você executa ALTER TABLE em uma tabela publicada em um Editor do SQL Server, por padrão, essa alteração se propaga para todos os Assinantes do SQL Server. Esta funcionalidade tem algumas restrições. Você pode desativá-lo. Para obter mais informações, consulte fazer alterações de esquema em bancos de dados de publicação.

Compressão de dados

As tabelas do sistema não podem ser ativadas para compressão. Se a tabela for um heap, a operação de reconstrução para o modo ONLINE será de thread único. Use o modo OFFLINE para uma operação de reconstrução de heap multi-threaded. Para obter mais informações sobre compactação de dados, consulte de compactação de dados .

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

As seguintes restrições aplicam-se a tabelas particionadas:

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

Soltar colunas NTEXT

Ao soltar 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 o valor NULL primeiro e, em seguida, solte a coluna. Você pode executar essa opção com operações paralelas e torná-la muito mais rápida.

Índice online REBUILD

Para executar a instrução DDL para uma reconstruçã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 reconstrução do índice online é iniciada, ela bloqueia todas as novas transações que estão prontas para começar a ser executadas nesta tabela. Embora a duração do bloqueio para a reconstrução do índice online seja curta, esperar que todas as transações abertas em uma determinada tabela sejam concluídas e bloquear o início das novas transações pode afetar significativamente a taxa de transferência. Isso pode causar uma lentidão ou tempo limite da carga de trabalho e limitar significativamente o acesso à tabela subjacente. A opção WAIT_AT_LOW_PRIORITY permite que os DBAs gerenciem os bloqueios S-lock e Sch-M necessários para reconstruções de índice online. Em todos os três casos: NONE, SELF e BLOCKERS, se durante o tempo de espera ( (MAX_DURATION =n [minutes]) ) não houver atividades de bloqueio, a reconstrução do índice on-line é executada imediatamente sem esperar e a instrução DDL é concluída.

Suporte de compatibilidade

A instrução ALTER TABLE suporta apenas nomes de tabela de duas partes (schema.object). No SQL Server, a especificação de um nome de tabela usando os seguintes formatos falha em tempo de compilação com o erro 117.

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

Em versões anteriores, especificar o formato server.database.schema.table retornava o erro 4902. Especificar o formato .database.schema.table ou o formato ..schema.table bem-sucedido.

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

Permissões

Requer permissão ALTER na mesa.

As permissões ALTER TABLE aplicam-se a ambas as tabelas envolvidas em uma instrução ALTER TABLE SWITCH. Todos os dados trocados herdam a segurança da tabela de destino.

Se você definiu qualquer coluna na instrução ALTER TABLE como sendo de um tipo de dados definido pelo usuário ou tipo de dados de alias do Common Language Runtime (CLR), a permissão REFERENCES no tipo é necessária.

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

Exemplos

Categoria	Elementos de sintaxe em destaque
Adicionando colunas e restrições	ADICIONAR * CHAVE PRIMÁRIA com opções de índice * colunas esparsas e conjuntos de colunas *
Descartando colunas e restrições	QUEDA
Alterar uma definição de coluna	alterar tipo de dados * alterar tamanho da coluna * agrupamento
Alterar uma definição de tabela	DATA_COMPRESSION * PARTIÇÃO SWITCH * ESCALONAMENTO DE BLOQUEIO * rastreamento de alterações
Desativando e habilitando restrições e gatilhos	MARQUE * SEM VERIFICAÇÃO * ATIVAR GATILHO * DESATIVAR GATILHO
Operações online	ONLINE
de versionamento do sistema	SYSTEM_VERSIONING

Adicionar colunas e restrições

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

Um. 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 definição DEFAULT. Na nova coluna, cada linha terá 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 de 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
EXEC 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 impedir que a restrição seja validada em relação às 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
EXEC 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. Uma restrição de DEFAULT é então adicionada à segunda coluna. Para verificar se o padrão é 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 nova coluna tem uma propriedade IDENTITY. Cada linha da tabela tem novos valores incrementais na coluna de identidade.

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

-- Add a PRIMARY KEY identity column.
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
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
EXEC sp_help doc_exe ;
GO
DROP TABLE dbo.doc_exe ;
GO

F. Adicionar uma coluna anulável com valores padrão

O exemplo a seguir adiciona uma coluna anulável com uma definição de 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 NULL
CONSTRAINT AddDateDflt
DEFAULT GETDATE() WITH VALUES ;
GO
DROP TABLE dbo.doc_exf ;
GO

G. Criar uma restrição de CHAVE PRIMÁRIA com opções de índice ou compactação de dados

O exemplo a seguir cria a restrição PRIMARY KEY PK_TransactionHistoryArchive_TransactionID e define as opções FILLFACTOR, ONLINEe PAD_INDEX. O índice clusterizado resultante terá o mesmo nome que a restrição.

Aplica-se a: SQL Server 2008 (10.0.x) e posterior e Banco de Dados SQL do Azure.

USE AdventureWorks;
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 aplica a compactação de página ao aplicar a chave primária clusterizada.

USE AdventureWorks;
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 T1 de tabela é o seguinte.

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

Para adicionar uma coluna esparsa adicional C5, execute a instrução a seguir.

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

Para converter a C4 coluna não esparsa em uma coluna esparsa, execute a instrução a seguir.

ALTER TABLE T1
ALTER COLUMN C4 ADD SPARSE ;
GO

Para converter a C4 coluna esparsa em uma coluna não esparsa, execute a instrução a seguir.

ALTER TABLE T1
ALTER COLUMN C4 DROP SPARSE ;
GO

Eu. Adicionar um conjunto de colunas

Os exemplos a seguir mostram a adição de uma coluna à tabela T2. Um conjunto de colunas não pode ser adicionado a uma tabela que já contenha colunas esparsas. O código para criar T2 de tabela é 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 CSe, em seguida, modificam as 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 encriptada

A instrução a seguir adiciona uma coluna criptografada chamada 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 operação retomável

Operação ALTER TABLE retomável para adicionar uma chave primária agrupada 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);

Soltar colunas e restrições

Os exemplos nesta seção demonstram a queda de colunas e restrições.

Um. Soltar 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. Restrições de descarte e colunas

O primeiro exemplo remove uma restrição de 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. Solte uma restrição de CHAVE PRIMÁRIA no modo ONLINE

O exemplo a seguir exclui uma restrição PRIMARY KEY 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 de CHAVE ESTRANGEIRA

O exemplo a seguir cria a tabela ContactBackupe, em seguida, altera a tabela, primeiro adicionando uma restrição de FOREIGN KEY que faz referência à tabela Person.Persone, em seguida, 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

Um. 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 a escala de uma coluna decimal . Como as colunas contêm dados, o tamanho da coluna só pode ser aumentado. Observe também que col_a é definido em um índice exclusivo. O tamanho do col_a ainda pode ser aumentado porque o tipo de dados é um varchar e o índice não é o resultado de uma restrição de CHAVE PRIMÁRIA.

-- 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 agrupamento de colunas

O exemplo a seguir mostra como alterar o agrupamento de uma coluna. Primeiro, uma tabela é criada com o agrupamento de usuário padrão.

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

Em seguida, o agrupamento de C2 coluna é alterado para Latin1_General_BIN. O tipo de dados é necessário, embora não seja 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 colunas criptografadas.

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 criptografia aleatória. Para que a seguinte instrução seja bem-sucedida:

• A chave de criptografia de coluna deve ser habilitada para enclave. Ou seja, ele deve ser criptografado com uma chave mestra de coluna que permita cálculos de enclave.
• A instância do SQL Server de destino deve oferecer suporte a Always Encrypted com enclaves seguros.
• A instrução deve ser emitida através de uma conexão configurada para Always Encrypted com enclaves seguros e usando um driver de cliente suportado.
• O aplicativo chamador deve ter acesso à coluna chave mestra, 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 nesta seção demonstram como alterar a definição de uma tabela.

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

O exemplo a seguir altera a compactação de uma tabela não particionada. A pilha ou o índice clusterizado será reconstruído. Se a tabela for um heap, todos os índices não clusterizados serão reconstruídos.

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 apenas o número de partição 1 seja reconstruído.

Aplica-se a: SQL Server 2008 (10.0.x) e posterior e Banco de Dados SQL do Azure.

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

A mesma operação usando a sintaxe alternativa a seguir faz com que todas as partições na tabela sejam reconstruídas.

Aplica-se a: SQL Server 2008 (10.0.x) e posterior e Banco de Dados SQL do Azure.

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

Para obter exemplos adicionais de compactação de dados, consulte de 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 é útil para arquivamento ou para situações que exigem menos espaço e podem dar mais tempo para armazenamento e recuperação.

Aplica-se a: SQL Server 2014 (12.x) e posterior 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 que foi compactada com COLUMNSTORE_ARCHIVE opção. Quando os dados forem restaurados, eles continuarão a ser compactados com a compactação columnstore usada para todas as tabelas columnstore.

Aplica-se a: SQL Server 2014 (12.x) e posterior 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, supondo 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 da tabela particionada e no mesmo grupo de arquivos que PARTITION 2 da tabela PartitionTable. Os dados de PARTITION 2 da tabela PartitionTable são então alternados para a 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 bloqueio em tabelas particionadas

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

Aplica-se a: SQL Server 2008 (10.0.x) e posterior 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 2008 (10.0.x) e posterior e Banco de Dados SQL do Azure.

USE AdventureWorks;
ALTER TABLE Person.Person
ENABLE CHANGE_TRACKING ;

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

Aplica-se a: SQL Server 2008 (10.0.x) e posterior.

USE AdventureWorks;
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 2008 (10.0.x) e posterior e Banco de Dados SQL do Azure.

USE AdventureWorks;
GO
ALTER TABLE Person.Person
DISABLE CHANGE_TRACKING ;

Desabilitar e habilitar restrições e gatilhos

Um. Desativar e reativar uma restrição

O exemplo a seguir desabilita uma restrição que limita os salários aceitos nos dados. NOCHECK CONSTRAINT é usado com ALTER TABLE para desativar a restrição e permitir uma inserção que normalmente violaria a restrição. CHECK CONSTRAINT reativa 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 will fail.
ALTER TABLE dbo.cnst_example CHECK CONSTRAINT salary_cap;
INSERT INTO dbo.cnst_example VALUES (4,'Eric James',110000) ;

B. Desativar e reativar 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 é então usado para reativar 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 TRANSACTION
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

Um. Recriação de índice on-line usando opções de espera de baixa prioridade

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

Aplica-se a: SQL Server 2014 (12.x) e posterior 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. Coluna alter online

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

Aplica-se a: SQL Server 2016 (13.x) e posterior 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
sp_help doc_exy;
DROP TABLE dbo.doc_exy ;
GO

Controle de versão do sistema

Os quatro exemplos a seguir ajudarão você a se familiarizar com a sintaxe para usar o controle de versão do sistema. Para obter assistência adicional, consulte Introdução ao System-Versioned Tabelas Temporais.

Aplica-se a: SQL Server 2016 (13.x) e posterior e Banco de Dados SQL do Azure.

Um. Adicionar 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 futuro. Este exemplo pressupõe que exista 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 as horas de início e término porque esses valores não podem ser nulos. Este exemplo usa a cláusula HIDDEN para garantir que não haja impacto nos 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 PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo),
ValidFrom datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL
    DEFAULT SYSUTCDATETIME(),
ValidTo datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL
    DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.99999999') ;

--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 o suporte temporal. O exemplo pressupõe que há uma solução existente que usa uma tabela ProjectTask e uma tabela ProjectTaskHistory para sua solução existente, que usa as colunas Changed Date e Revised Date para seus períodos, que essas colunas de período não usam o tipo de dados datetime2 e que a tabela ProjectTask 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 reativar o controle de versão do sistema para alterar o esquema da tabela

Este exemplo mostra como desabilitar o controle de versão do sistema na tabela Department, adicionar uma coluna e reativar o controle de versão do sistema. A desativação do controle de versão do sistema é necessária para modificar o esquema da tabela. Execute estas etapas dentro de uma transação para impedir atualizações em ambas as tabelas durante a atualização do esquema de tabela, o que permite que o DBA ignore a verificação de consistência de dados ao reativar o controle de versão do sistema e obtenha um benefício de desempenho. Tarefas como criar estatísticas, alternar partições ou aplicar compactação a uma ou ambas as tabelas não exigem a desativaçã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 controle de versão do sistema

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

ALTER TABLE Department
    SET (SYSTEM_VERSIONING = OFF) ;
ALTER TABLE Department
DROP PERIOD FOR SYSTEM_TIME ;
DROP TABLE DepartmentHistory ;

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

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

Um. Determinar se uma tabela está particionada

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

SELECT * FROM sys.partitions AS p
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 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
JOIN sys.indexes AS i
    ON t.object_id = i.object_id
JOIN sys.partitions AS p
    ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN  sys.partition_schemes AS s
    ON i.data_space_id = s.data_space_id
JOIN sys.partition_functions AS f
    ON s.function_id = f.function_id
LEFT 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 tabela. FactResellerSales.

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
JOIN sys.indexes AS i
    ON t.object_id = i.object_id
JOIN sys.columns AS c
    ON t.object_id = c.object_id
JOIN sys.partition_schemes AS ps
    ON ps.data_space_id = i.data_space_id
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 uma nova partição vinculada pelo valor 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. Use 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 na 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	Tem dados?	Intervalo de limites
1	Sim	Data do pedido < '2004-01-01'
2	Sim	'2004-01-01' <= Data de encomenda < '2005-01-01'
3	Sim	'2005-01-01' <= Data de encomenda< '2006-01-01'
4	Sim	'2006-01-01'<= Data de encomenda < '2007-01-01'
5	Sim	'2007-01-01' <= Data da encomenda

• 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 colunas 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 das colunas devam ser os mesmos, os limites da partição não precisam ser os mesmos. Neste exemplo, a tabela OrdersHistory tem as duas partições a seguir e ambas as partições 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 em Orders está vazia e a primeira partição em OrdersHistory contém dados. Os quadros são agora apresentados da seguinte forma:

Orders tabela

• 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

OrdersHistory tabela

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

Para limpar a tabela Orders, você pode remover a partição vazia mesclando as partições 1 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:

Orders tabela

• 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 passe mais um ano e você esteja pronto para arquivar o ano de 2005. Você pode alocar uma partição vazia para o ano de 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:

OrdersHistory tabela

• 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
• EVENTDATA
• CRIAR TABELA
• DROP TABLE
• TABELA ALTER column_constraint
• TABELA ALTER column_definition
• TABELA ALTER computed_column_definition
• TABELA ALTER index_option
• TABELA ALTER table_constraints