ALTER TABLE (Transact-SQL)

Modifica uma definição de tabela alterando, adicionando ou removendo colunas e restrições, reatribuindo e recriando partições, ou desabilitando ou habilitando restrições e gatilhos.

Aplica-se a: SQL Server (do SQL Server 2008 à versão atual), Banco de dados SQL do Windows Azure (da versão inicial até a versão atual).

Convenções da sintaxe Transact-SQL

Sintaxe

ALTER TABLE [ database_name . [ schema_name ] . | schema_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 }
    } 
        | [ WITH { CHECK | NOCHECK } ]

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

    | DROP 
     {
         [ CONSTRAINT ] 
         { 
              constraint_name 
              [ WITH 
               ( <drop_clustered_constraint_option> [ ,...n ] ) 
              ] 
          } [ ,...n ]
          | COLUMN 
          {
              column_name 
          } [ ,...n ]
     } [ ,...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_lock_priority_wait> ) ]
    | SET ( FILESTREAM_ON = 
            { partition_scheme_name | filegroup | "default" | "NULL" } 
          )

    | REBUILD 
      [ [PARTITION = ALL]
        [ WITH ( <rebuild_option> [ ,...n ] ) ] 
      | [ PARTITION = partition_number 
           [ WITH ( <single_partition_rebuild_option> [ ,...n ] ) ]
        ]
      ]

    | <table_option>

    | <filetable_option>

}
[ ; ]

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

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 está no banco de dados atual ou não consta no esquema pertencente ao usuário atual, o banco de dados e o esquema devem ser especificados explicitamente.

• ALTER COLUMN
  Especifica que a coluna nomeada será alterada ou modificada.

  A coluna modificada não pode ser uma das seguintes:

  • Uma coluna com um tipo de dados timestamp.

  • O ROWGUIDCOL para a tabela.

  • Uma coluna computada ou usada em uma coluna computada.

  • Usada em estatísticas geradas pela instrução CREATE STATISTICS, a menos que a coluna seja um tipo de dados varchar, nvarchar ou varbinary, o tipo de dados não seja modificado e o novo tamanho seja igual ou maior que o tamanho anterior ou a coluna seja modificada de não nula para nula. Primeiro, remova as estatísticas que usam a instrução DROP STATISTICS. As estatísticas que são geradas automaticamente pelo otimizador de consulta são descartadas automaticamente por ALTER COLUMN.

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

  • Usada em uma restrição CHECK ou UNIQUE. Entretanto, a alteração do comprimento de uma coluna de comprimento variável usada em uma restrição CHECK ou UNIQUE é permitida.

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

    O tipo de dados das colunas text, ntext e image podem ser alterados apenas das seguintes maneiras:

    • text para varchar(max), nvarchar(max) ou xml

    • ntext para varchar(max), nvarchar(max) ou xml

    • image 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 (Transact-SQL). Reduzir a precisão ou escala de uma coluna pode causar o truncamento de dados.

    O tipo de dados de uma coluna em uma tabela particionada não pode ser alterado.

  O tipo de dados de colunas incluído em um índice não poderá ser alterado, a menos que a coluna seja um tipo de dados varchar, nvarchar ou varbinary e o novo tamanho seja igual ou maior que o tamanho anterior.

  Uma coluna incluída em uma restrição de chave primária não pode ser alterada de NOT NULL para NULL.

• column_name
  É o nome da coluna a ser alterada, adicionada ou removida. column_name pode ter um máximo 128 caracteres. Para novas colunas, o column_name pode ser omitido para colunas criadas com um tipo de dados timestamp. O nome timestamp será usado se nenhum column_name for especificado para uma coluna de tipo de dados timestamp.

• [ type_schema_name**.** ] type_name
  É o novo tipo de dados da coluna alterada ou o tipo de dados da coluna adicionada. type_name não pode ser especificado para colunas de tabelas particionadas existentes. type_name pode ser qualquer um dos seguintes:

  • Um tipo de dados de sistema SQL Server.

  • Um tipo de dados do alias com base em um tipo de dados de sistema SQL Server. Os tipos de dados do alias são criados com a instrução CREATE TYPE antes que possam ser usados em uma definição de tabela.

  • Um tipo definido pelo usuário .NET Framework e o esquema ao qual ele pertence. Tipos definidos pelo usuário .NET Framework são criados com a instrução CREATE TYPE antes de poderem ser usados em uma definição de tabela.

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

  • O tipo de dados anterior deve ser implicitamente conversível para o novo tipo de dados.

  • type_name não pode ser timestamp.

  • Padrões ANSI_NULL estão sempre ativados para ALTER COLUMN; se não for especificado, a coluna permite valor nulo.

  • O preenchimento ANSI_PADDING está sempre ON para ALTER COLUMN.

  • Se a coluna modificada for uma coluna de identidade, new_data_type deve ser um tipo de dados que aceita a propriedade de identidade.

  • A configuração atual para SET ARITHABORT é ignorada. ALTER TABLE operará como se ARITHABORT estivesse definido como ON.

  Dica

  Se a cláusula COLLATE não for especificada, a alteração do tipo de dados de uma coluna fará com que um agrupamento seja modificado para o agrupamento padrão do banco de dados.

• precision
  É a precisão do tipo de dados especificado. Para obter mais informações sobre valores de precisão válidos, consulte Precisão, escala e comprimento (Transact-SQL).

• scale
  É a escala do tipo de dados especificado. Para obter mais informações sobre valores de escala válidos, consulte Precisão, escala e comprimento (Transact-SQL).

• max
  Aplica apenas os tipos de dados varchar, nvarchar e varbinary para armazenar 2^31-1 bytes de caracteres, dados binários e dados Unicode.

• xml_schema_collection

  Aplica-se a: do SQL Server 2008 ao SQL Server 2014.

  Aplica-se apenas ao tipo de dados xml para associar um esquema XML ao tipo. Antes de digitar uma coluna xml em uma coleção de esquema, a coleção de esquema deve ser criada primeiramente 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, a coluna será atribuída ao 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 Nome de agrupamento do Windows (Transact-SQL) e Nome de agrupamento do SQL Server (Transact-SQL).

  A cláusula COLLATE pode ser usada para alterar os agrupamentos somente de colunas dos tipos de dados char, varchar, nchar e nvarchar. Para alterar o agrupamento de uma coluna de tipo de dados de alias definido pelo usuário, você deve executar instruções ALTER TABLE separadas para alterar a coluna para um tipo de dados de sistema SQL Server e alterar seu agrupamento. Depois, deve alterar novamente a coluna para um tipo de dados de alias.

  ALTER COLUMN não poderá ter uma alteração de agrupamento se ocorrer uma ou mais das condições a seguir:

  • Se uma restrição CHECK, FOREIGN KEY ou colunas computadas referenciarem a coluna alterada.

  • Se forem criados qualquer índice, estatísticas ou índice de texto completo na coluna. As estatísticas criadas automaticamente na coluna alterada serão descartadas se o agrupamento da coluna for alterado.

  • Se uma função ou exibição associada a esquema referenciar a coluna.

  Para obter mais informações, consulte COLLATE (Transact-SQL).

• NULL | NOT NULL
  Especifica se a coluna pode aceitar valores nulos. As colunas que não permitem valores nulos podem ser adicionadas com ALTER TABLE apenas se tiverem um padrão especificado ou se a tabela estiver vazia. NOT NULL poderá ser especificado para colunas computadas somente se PERSISTED também for especificado. Se a nova coluna permitir valores nulos e nenhum padrão for especificado, ela conterá um valor nulo para cada linha da tabela. Se a nova coluna permitir valores nulos e uma definição padrão for adicionada com a nova coluna, WITH VALUES poderá ser usada 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, uma definição DEFAULT deve ser adicionada com a nova coluna e a nova coluna será carregada automaticamente com o valor padrão nas novas colunas em cada linha existente.

  NULL pode ser especificado em ALTER COLUMN para forçar uma coluna NOT NULL a permitir valores nulos, exceto no caso de colunas nas restrições PRIMARY KEY. NOT NULL poderá ser especificado em ALTER COLUMN apenas se a coluna não contiver nenhum valor nulo. Os valores nulos devem ser atualizados para algum valor antes que ALTER COLUMN NOT NULL seja permitido. Por exemplo:

  UPDATE MyTable SET NullCol = N'some_value' WHERE NullCol IS NULL;
  ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL;
  

  Quando você cria ou altera uma tabela com a instrução CREATE TABLE ou ALTER TABLE, as configurações de banco de dados e de sessão influenciam e, possivelmente, substituem a nulidade do tipo de dados que é usado em uma definição de coluna. É recomendável 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, é recomendável definir a coluna com a mesma nulidade que o tipo de dados definido pelo usuário e especificar um valor padrão para a coluna. Para obter mais informações, consulte CREATE TABLE (SQL Server).

  Dica

  Se NULL ou NOT NULL for especificado com ALTER COLUMN, new_data_type [(precision [, scale ])] também deverá ser especificado.Se o tipo de dados, a precisão e a escala não forem alterados, especifique os valores de coluna atuais.

• [ {ADD | DROP} ROWGUIDCOL ]

  Aplica-se a: do SQL Server 2008 ao SQL Server 2014.

  Especifica que a propriedade ROWGUIDCOL propriedade é adicionada ou descartada da coluna especificada. ROWGUIDCOL indica que a coluna é uma coluna GUID de linha. Apenas uma coluna uniqueidentifier por tabela pode ser atribuída como a coluna ROWGUIDCOL, e a propriedade ROWGUIDCOL pode ser atribuída somente a uma coluna uniqueidentifier. ROWGUIDCOL não pode ser atribuída a uma coluna de um tipo de dados definido pelo usuário.

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

• [ {ADD | DROP} PERSISTED ]
  Especifica que a propriedade PERSISTED é adicionada ou descartada da coluna especificada. A coluna deve ser uma coluna computada que é definida com uma expressão determinista. No caso de colunas especificadas como PERSISTED, o Mecanismo de Banco de Dados armazenará fisicamente os valores computados na tabela e os atualizará quando qualquer outra coluna, da qual depende a coluna computada, for atualizada. Ao marcar uma coluna computada como PERSISTED, é possível criar índices em colunas computadas definidas em expressões que são determinísticas, mas não precisas. Para obter mais informações, consulte Índices em colunas computadas.

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

• DROP NOT FOR REPLICATION

  Aplica-se a: do SQL Server 2008 ao SQL Server 2014.

  Especifica que os valores são incrementados em colunas de identidade quando os agentes de replicação executam operações de inserção. Essa cláusula só poderá ser especificada se column_name for uma coluna de identidade.

• SPARSE
  Indica que a coluna é uma coluna esparsa. O armazenamento de colunas esparsas é otimizado para valores nulos. Colunas esparsas não podem ser designadas como NOT NULL. A conversão de uma coluna de esparsa para não esparsa, ou vice-versa, bloqueia a tabela durante a execução do comando. Talvez você precise usar a cláusula REBUILD para reclamar qualquer economia de espaço. Para obter restrições adicionais e mais informações sobre colunas esparsas, consulte Usar colunas esparsas.

• WITH CHECK | WITH NOCHECK
  Especifica se os dados na tabela são ou não validados com relação à restrição FOREIGN KEY ou CHECK recentemente adicionada ou reabilitada. Se não especificado, WITH CHECK é assumido para novas restrições e WITH NOCHECK é assumido para restrições reabilitadas.

  Se você não quiser verificar novas restrições CHECK ou FOREIGN KEY com relação aos dados existentes, use WITH NOCHECK. Nós não recomendamos fazer isso, com raríssimas exceções. A nova restrição será avaliada em todas as atualizações de dados posteriores. Qualquer violação que seja suprimida por WITH NOCHECK ao adicionar a restrição pode gerar falha em atualizações futuras caso elas atualizem as linhas com dados que não estejam de acordo com a restrição.

  O otimizador de consulta não considera restrições que são definidas WITH NOCHECK. Tais restrições são ignoradas até que sejam reabilitadas, usando ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL.

• ADD
  Especifica que uma ou mais definições de coluna, definições de coluna computada ou restrições de tabela são adicionadas.

• DROP { [ CONSTRAINT ] constraint_name | COLUMN column_name }
  Especifica que constraint_name ou column_name é removido da tabela. Várias colunas e restrições podem ser listadas.

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

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

  Uma coluna não pode ser descartada quando for:

  • Usada em um índice.

  • Usada em uma restrição CHECK, FOREIGN KEY, UNIQUE ou PRIMARY KEY.

  • Associada a um padrão que é definido com a palavra-chave DEFAULT ou associada com um objeto padrão.

  • Associada a uma regra.

  Dica

  Descartar uma coluna não recupera o espaço em disco da coluna.Talvez seja necessário recuperar o espaço em disco de uma coluna descartada quando o tamanho da linha de uma tabela estiver próximo do limite ou o exceder.Recupere o espaço, criando um índice clusterizado da tabela ou recriando um índice clusterizado existente usando ALTER INDEX.Para obter informações sobre o impacto de remover tipos de dados LOB, consulte esta entrada de blog CSS.

• WITH <drop_clustered_constraint_option>
  Especifica que há uma ou mais opções de descarte de restrição clusterizada definidas.

• MAXDOP = max_degree_of_parallelism

  Aplica-se a: do SQL Server 2008 ao SQL Server 2014.

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

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

  max_degree_of_parallelism pode ser um dos seguintes valores:

  • 1
    Suprime a geração de plano paralelo.

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

  • 0 (padrão)
    Usa o número real de processadores, ou menos, com base na carga de trabalho atual do sistema.

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

  Dica

  As operações de índice paralelas não estão disponíveis em todas as edições do SQL Server.Para obter mais informações, consulte Recursos compatíveis com as edições do SQL Server 2014.

• ONLINE = { ON | 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. REBUILD pode ser executado como uma operação ONLINE.

  • ON
    Bloqueios de tabela de longa duração não são mantidos durante a operação do índice. Durante a fase principal da operação de índice, apenas um bloqueio IS (Tentativa Compartilhada) é mantido na tabela de origem. Isso permite a continuação de consultas ou atualizações feitas na tabela e nos índices subjacentes. No início da operação, um bloqueio compartilhado (S) é mantido no objeto de origem por um período muito curto. Ao final da operação, por um curto período de tempo, um bloqueio compartilhado (S) será adquirido na origem se um índice não clusterizado estiver sendo criado; ou um bloqueio de SCH-M (modificação de esquema) será adquirido quando um índice clusterizado for criado ou descartado online e quando um índice clusterizado ou não clusterizado estiver sendo recriado. Não é possível definir ONLINE como ON quando um índice está sendo criado em uma tabela temporária local. Apenas a operação de reconstrução de heap de thread único é permitida.

    Para executar a DDL de SWITCH ou a recompilaçã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, SWITCH ou a operação de recompilação impede que a nova transação seja iniciada, e pode afetar significativamente a taxa de transferência da carga de trabalho e atrasar temporariamente o acesso à tabela subjacente.

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

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

  Dica

  As operações de índice online não estão disponíveis em todas as edições do SQL Server.Para obter mais informações, consulte Recursos compatíveis com as edições do SQL Server 2014.

• MOVE TO { partition_scheme_name**(column_name [ 1,** ... n] ) | filegroup | "default" }

  Aplica-se a: do SQL Server 2008 ao SQL Server 2014.

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

  Dica

  Nesse contexto, default não é uma palavra-chave.É um identificador do grupo de arquivos padrão e deve ser delimitado, como em MOVE TO "default" ou MOVE TO [default].Se "default" for especificado, a opção QUOTED_IDENTIFIER deverá ser ON para a sessão atual.Essa é a configuração padrão.Para obter mais informações, consulte SET QUOTED_IDENTIFIER (Transact-SQL).

• { CHECK | NOCHECK } CONSTRAINT
  Especifica se constraint_name está habilitado ou desabilitado. Essa 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 da coluna não são validadas com relação às condições de restrição. As restrições DEFAULT, PRIMARY KEY e UNIQUE não podem ser desabilitadas.

• ALL
  Especifica que todas as restrições são desabilitadas com a opção NOCHECK ou habilitado com a opção CHECK.

• { ENABLE | DISABLE } TRIGGER
  Especifica se trigger_name é habilitado ou desabilitado. Quando um gatilho é desabilitado, ele ainda permanece definido para a tabela. Porém, quando a instrução INSERT, UPDATE ou DELETE é executada na tabela, as ações no gatilho não são realizadas até que ele seja reabilitado.

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

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

• { ENABLE | DISABLE } CHANGE_TRACKING

  Aplica-se a: do SQL Server 2008 ao SQL Server 2014.

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

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

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

• WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } )

  Aplica-se a: do SQL Server 2008 ao SQL Server 2014.

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

• SWITCH [ PARTITION source_partition_number_expression ] TO [ schema_name**.** ] target_table [ PARTITION target_ partition_number_expression ]

  Aplica-se a: do SQL Server 2008 ao SQL Server 2014.

  Alterna um bloco de dados em um dos seguintes modos:

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

  • Alterna uma partição de uma tabela particionada para outra.

  • Reatribui todos os dados em uma partição de uma tabela particionada para uma tabela não particionada existente.

  Se table for uma tabela particionada, source_partition_number_expression deve ser especificado. Se target_table for particionado, target_partition_number_expression deve ser especificado. Se estiver reatribuindo os dados de uma tabela com uma partição para uma tabela particionada já existente ou alternando uma partição de uma tabela particionada para outra, a partição de destino deve existir e estar vazia.

  Se estiver reatribuindo os dados de uma partição para formar uma tabela única, a tabela de destino já deve ter sido criada e deve estar vazia. A tabela de origem ou a partição e a tabela de destino ou a partição devem residir no mesmo grupo de arquivos. Os índices correspondentes ou as partições de índice também devem residir no mesmo grupo de arquivos. Muitas restrições adicionais são aplicadas para alternância de partições. table e target_table não podem ser os mesmos. target_table pode ser um identificador de várias partes.

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

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

• SET ( FILESTREAM_ON = { partition_scheme_name | filestream_filegroup_name | "default" | "NULL" })

  Aplica-se a: do SQL Server 2008 ao SQL Server 2014.

  Especifica onde os dados FILESTREAM são armazenados.

  ALTER TABLE com a cláusula SET FILESTREAM_ON só terá sucesso se a tabela não tiver nenhuma coluna FILESTREAM. As colunas FILESTREAM podem ser adicionadas usando uma segunda instrução ALTER TABLE.

  Se partition_scheme_name for especificado, as regras para CREATE TABLE serão aplicadas. A tabela já deve estar particionada para dados de linha e seu esquema de partição deve usar 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. Caso contrário, será gerado um erro.

  "default" especifica o grupo de arquivos FILESTREAM com a propriedade DEFAULT definida. Se não houver um grupo de arquivos FILESTREAM, ocorrerá um erro.

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

• SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )

  Aplica-se a: do SQL Server 2008 ao SQL Server 2014.

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

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

    • Se a tabela não estiver particionada, o escalonamento de bloqueios será permitido para particionar. Depois de ser escalonado para o nível de partição, o bloqueio não será escalonado posteriormente para a granularidade TABLE.

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

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

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

• REBUILD
  Use a sintaxe REBUILD WITH para recriar uma tabela inteira que inclui 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 recriar uma única partição em uma tabela particionada.

• PARTITION = ALL

  Aplica-se a: do SQL Server 2008 ao SQL Server 2014.

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

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

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

  Para obter descrições completas das opções de recriação, consulte index_option (Transact-SQL).

• DATA_COMPRESSION

  Aplica-se a: do SQL Server 2008 ao SQL Server 2014.

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

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

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

  • PAGE
    A tabela ou as partições especificadas são compactadas usando a compactação de página. Não se aplica a tabelas columnstore.

  • COLUMNSTORE

    Aplica-se a: do SQL Server 2014 ao SQL Server 2014.

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

  • COLUMNSTORE_ARCHIVE

    Aplica-se a: do SQL Server 2014 ao SQL Server 2014.

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

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

• ONLINE = { ON | OFF } <como se aplica a single_partition_rebuild_option>
  Especifica se uma única partição das tabelas subjacentes e os índices associados estão disponíveis para consultas e modificação de dados durante a operação de índice. O padrão é OFF. REBUILD pode ser executado como uma operação ONLINE.

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

    Dica

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

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

• column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS

  Aplica-se a: do SQL Server 2008 ao SQL Server 2014.

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

• { ENABLE | DISABLE } FILETABLE_NAMESPACE

  Aplica-se a: do SQL Server 2012 ao SQL Server 2014.

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

• SET ( FILETABLE_DIRECTORY = directory_name )

  Aplica-se a: do SQL Server 2012 ao SQL Server 2014.

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

• WAIT_AT_LOW_PRIORITY

  Aplica-se a: do SQL Server 2014 ao SQL Server 2014.

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

• MAX_DURATION = time [MINUTES ]

  Aplica-se a: do SQL Server 2014 ao SQL Server 2014.

  O tempo (um valor inteiro especificado em minutos) que a opção SWITCH ou os bloqueios de recompilação de índice online deverão aguardar com baixa prioridade ao executar o comando DDL. Se a operação for bloqueada por MAX_DURATION, uma das ações de ABORT_AFTER_WAIT será executada. O tempo MAX_DURATION estará sempre em minutos, e a palavra MINUTES poderá ser omitida.

• ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS } ]

  Aplica-se a: do SQL Server 2014 ao SQL Server 2014.

  • NONE
    Continue aguardando o bloqueio com prioridade normal.

  • SELF
    Saia da opção SWITCH ou da operação DDL de recompilação de índice online em execução sem realizar a ação.

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

    Requer a permissão ALTER ANY CONNECTION.

Comentários

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

Se houver qualquer plano de execução no cache de procedimento que referencie a tabela, ALTER TABLE o marcará para que seja recompilado na próxima execução.

Alterando o tamanho de uma coluna

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

Bloqueios e ALTER TABLE

As alterações especificadas em ALTER TABLE são implementadas imediatamente. Se as alterações requererem modificações das linhas na tabela, ALTER TABLE atualizará as linhas. ALTER TABLE adquire um bloqueio de modificação de esquema (SCH-M) na tabela para se certificar de que nenhuma outra conexão referencie nem mesmo os metadados da tabela durante a alteração, exceto as operações de índice online que exigem um bloqueio SCH-M muito curto no final. Em uma operação ALTER TABLE…SWITCH, o bloqueio é adquirido em ambas as tabelas de origem e destino. As modificações feitas na tabela são registradas e completamente recuperáveis. As alterações que afetam todas as linhas em tabelas muito grandes, como descartar uma coluna ou, em algumas edições do SQL Server, adicionar uma coluna NOT NULL com um valor padrão, podem demorar muito tempo para serem concluídas e gerar muitos registros de log. Essas instruções ALTER TABLE devem ser executadas com o mesmo cuidado de outras instruções INSERT, UPDATE ou DELETE que podem afetar várias linhas.

Adicionando colunas NOT NULL como uma operação online

A partir do SQL Server 2012 Enterprise Edition, a adição de 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, independentemente do número de linhas na tabela. Isso ocorre porque as linhas existentes na tabela não são atualizadas durante a operação; em vez disso, o valor padrão é armazenado somente nos metadados da tabela e o valor é pesquisado conforme necessário em consultas que acessam essas linhas. Esse comportamento é automático; nenhuma sintaxe adicional é necessária para implementar a operação online, além da sintaxe ADD COLUMN. Uma constante de tempo de execução é uma expressão que gera o mesmo valor no tempo de execução para cada linha na tabela, independentemente 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 é gerado um valor exclusivo para cada linha da tabela. A adição de uma coluna NOT NULL com um valor padrão que não é uma constante de tempo de execução é sempre executada offline e um bloqueio exclusivo (SCH-M) é adquirido para a duração da operação.

Enquanto as linhas existentes referenciam o valor armazenado nos metadados, o valor padrão é armazenado na linha para qualquer nova linha inserida e não especifica outro valor para a coluna. O valor padrão armazenado 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 quando a tabela ou o índice clusterizado é recompilado.

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

Execução de plano paralelo

No Microsoft SQL Server 2012 Enterprise e superior, o número de processadores empregados para executar uma instrução ALTER TABLE ADD (baseada em índice) CONSTRAINT ou DROP (índice clusterizado) CONSTRAINT é determinado pela opção de configuração grau máximo de paralelismo e pela atual carga de trabalho. Se o Mecanismo de Banco de Dados detectar que o sistema está ocupado, o grau de paralelismo da operação será automaticamente reduzido antes do início da execução da instrução. É possível configurar manualmente o número de processadores usados para executar a instrução, especificando a opção de índice MAXDOP. Para obter mais informações, consulte Configurar a opção de configuração de servidor max degree of parallelism.

Tabelas particionadas

Além de realizar operações SWITCH que envolvem tabelas particionadas, ALTER TABLE pode ser usado para alterar o estado de colunas, restrições e gatilhos de uma tabela particionada, exatamente da mesma forma que é usado em tabelas não particionadas. Porém, essa instrução não pode ser usada para alterar o modo que a própria tabela é particionada. Para reparticionar uma tabela particionada, use ALTER PARTITION SCHEME e ALTER PARTITION FUNCTION. Além disso, você não pode alterar o tipo de dados de uma coluna em uma tabela particionada.

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

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

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

Índices e ALTER TABLE

Os índices criados como parte de uma restrição são descartados quando a restrição é descartada. Os índices criados com CREATE INDEX devem ser descartados com DROP INDEX. A instrução ALTER INDEX pode ser usada para recriar uma parte de índice de uma definição de restrição. A restrição não tem que ser descartada e adicionada novamente com ALTER INDEX.

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

Quando uma restrição que cria um índice clusterizado é excluída, as linhas de dados que foram armazenadas no nível folha do índice clusterizado são armazenadas em uma tabela não clusterizada. É possível descartar 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 MOVE TO 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 especificada, a tabela resultante estará localizada no mesmo esquema de partição ou grupo de arquivos definido para o índice clusterizado.

Quando você descarta um índice clusterizado, você pode especificar a opção ONLINE = ON de forma 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 desabilitados. Índices desabilitados devem ser descartados usando ONLINE = OFF.

• Apenas um índice pode ser descartado por vez.

• ONLINE = ON não é válido para exibições indexadas, índices não clusterizados ou índices em tabelas temporárias locais.

• ONLINE = ON não é válido para índices columnstore.

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

Dica

As opções listadas em <drop_clustered_constraint_option> aplicam-se a índices clusterizados em tabelas e não podem ser aplicadas a índices clusterizados em exibições ou a índices não clusterizados.

Replicando alterações de esquema

Por padrão, quando você executa ALTER TABLE em uma tabela publicada no Publicador do SQL Server, essa alteração é propagada para todos os Assinantes do SQL Server. Essa funcionalidade tem algumas restrições e pode ser desabilitada. Para obter mais informações, consulte Fazer alterações de esquema em bancos de dados de publicação.

Compactação de dados

Não é possível habilitar as tabelas do sistema para compactação. Se a tabela for um heap, a operação de reconstrução para o modo ONLINE será um thread único. Use o modo OFFLINE para uma operação de reconstrução de um heap multithread. Para obter mais informações sobre compactação de dados, consulte Compactação de dados.

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

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

• Não será possível alterar a configuração de compactação de uma única partição se a tabela tiver índices não alinhados.

• A sintaxe de ALTER TABLE <table> REBUILD PARTITION ... recria a partição especificada.

• A sintaxe de ALTER TABLE <table> REBUILD WITH ... recria todas as partições.

Removendo colunas NTEXT

Ao remover colunas NTEXT, a limpeza dos dados excluídos ocorre como uma operação serializada em todas as linhas. Isso pode exigir um tempo substancial. Ao remover uma coluna NTEXT em uma tabela com um grande número de linhas, primeiro atualize a coluna NTEXT para o valor NULL e, em seguida, remova a coluna. Isso pode ser realizado com operações paralelas e pode ser muito mais rápido.

Recriação de índice online

Para executar a instrução DDL de uma recompilação de índice online, todas as transações de bloqueio ativas em execução em uma tabela específica devem ser concluídas. Quando a recompilação de índice online for executada, ela bloqueará todas as novas transações que estão prontas para iniciar a execução nessa tabela. Embora a duração do bloqueio da recompilação de índice online seja muito curta, é possível que a espera pela conclusão de todas as transações abertas em uma tabela específica e o bloqueio das novas transações a serem iniciadas afetem significativamente a taxa de transferência, diminuindo a velocidade da carga de trabalho ou ocasionando o tempo limite da mesma, e limite consideravelmente o acesso à tabela subjacente. A opção WAIT_AT_LOW_PRIORITY permite que os DBAs gerenciem o bloqueio S e os bloqueios Sch-M necessários às recompilações de índice online, e que eles selecionem uma das três opções. Nos três casos, se, durante o tempo de espera ( (MAX_DURATION =n [minutes]) ), não houver nenhuma atividade de bloqueio, a recompilação de índice online será executada imediatamente sem aguardar e a instrução DDL será concluída.

Suporte de compatibilidade

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

• server.database.schema.table

• .database.schema.table

• ..schema.table

As versões anteriores que especificam que o formato server.database.schema.table retornaram o erro 4902. A especificação do formato .database.schema.table ou do formato ..schema.table foi bem-sucedida.

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

Permissões

Requer a permissão ALTER na tabela.

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

Se alguma coluna da instrução ALTER TABLE for definida como um tipo CLR definido pelo usuário ou tipo de dados de alias, a permissão REFERENCES será necessária naquele tipo.

A adição de uma coluna que atualize as linhas da tabela requer a permissão UPDATE na tabela. Por exemplo, a adição de uma coluna NOT NULL com um valor padrão ou a adição de uma coluna de identidade quando a tabela não está vazia.

Exemplos

Categoria	Elementos de sintaxe em destaque
Adicionando colunas e restrições	ADD • PRIMARY KEY com opções de índice • colunas esparsas e conjuntos de colunas •
Descartando colunas e restrições	DROP
Alterando uma definição de coluna	alterar o tipo de dados • alterar o tamanho da coluna • agrupamento
Alterando uma definição de tabela	DATA_COMPRESSION • SWITCH PARTITION • LOCK ESCALATION • controle de alterações
Desabilitando e habilitando restrições e gatilhos	CHECK • NO CHECK • ENABLE TRIGGER • DISABLE TRIGGER

Adicionando colunas e restrições

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

A.Adicionando uma nova coluna

O exemplo a seguir adiciona uma coluna que permite valores nulos e que não tem nenhum valor fornecido por 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.Adicionando uma coluna com uma restrição

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

CREATE TABLE dbo.doc_exc (column_a INT) ;
GO
ALTER TABLE dbo.doc_exc ADD column_b VARCHAR(20) NULL 
    CONSTRAINT exb_unique UNIQUE ;
GO
EXEC sp_help doc_exc ;
GO
DROP TABLE dbo.doc_exc ;
GO

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

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

CREATE TABLE dbo.doc_exd ( column_a INT) ;
GO
INSERT INTO dbo.doc_exd VALUES (-1) ;
GO
ALTER TABLE dbo.doc_exd WITH NOCHECK 
ADD CONSTRAINT exd_check CHECK (column_a > 1) ;
GO
EXEC sp_help doc_exd ;
GO
DROP TABLE dbo.doc_exd ;
GO

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

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

CREATE TABLE dbo.doc_exz ( column_a INT, column_b INT) ;
GO
INSERT INTO dbo.doc_exz (column_a)VALUES ( 7 ) ;
GO
ALTER TABLE dbo.doc_exz
ADD CONSTRAINT col_b_def
DEFAULT 50 FOR column_b ;
GO
INSERT INTO dbo.doc_exz (column_a) VALUES ( 10 ) ;
GO
SELECT * FROM dbo.doc_exz ;
GO
DROP TABLE dbo.doc_exz ;
GO

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

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

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

-- 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.Adicionando uma coluna que permite valor nulo com valores padrão

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

CREATE TABLE dbo.doc_exf ( column_a INT) ;
GO
INSERT INTO dbo.doc_exf VALUES (1) ;
GO
ALTER TABLE dbo.doc_exf 
ADD AddDate smalldatetime NULL
CONSTRAINT AddDateDflt
DEFAULT GETDATE() WITH VALUES ;
GO
DROP TABLE dbo.doc_exf ;
GO

G.Criando uma restrição PRIMARY KEY com opções de índice

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

Aplica-se a: do SQL Server 2008 ao SQL Server 2014.

USE AdventureWorks2012;
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

H.Adicionando uma coluna esparsa

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

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

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

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

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

ALTER TABLE T1
ALTER COLUMN C4 ADD SPARSE ;
GO

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

ALTER TABLE T1
ALTER COLUMN C4 DROP SPARSE;
GO

I.Adicionando um conjunto de colunas

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

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

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

ALTER TABLE T2
ADD CS XML COLUMN_SET FOR ALL_SPARSE_COLUMNS ;
GO

ALTER TABLE T2
ALTER COLUMN C2 ADD SPARSE ; 
GO

ALTER TABLE T2
ALTER COLUMN C3 ADD SPARSE ;
GO

[Início]

Descartando colunas e restrições

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

A.Descartando 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.Descartando restrições e colunas

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

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

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

DROP TABLE dbo.doc_exc;
GO

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

-- Example 2. Remove two constraints and one column
-- The keyword CONSTRAINT is optional. The keyword COLUMN is required.
ALTER TABLE dbo.doc_exc 

    DROP CONSTRAINT CONSTRAINT my_constraint, my_pk_constraint, COLUMN column_b ;
GO

C.Descartando uma restrição PRIMARY KEY 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.Adicionando e descartando uma restrição FOREIGN KEY

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

CREATE TABLE Person.ContactBackup
    (ContactID int) ;
GO

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

ALTER TABLE Person.ContactBackup
DROP CONSTRAINT FK_ContactBacup_Contact ;
GO

DROP TABLE Person.ContactBackup ;

[Início]

Alterando uma definição de coluna

A.Alteração do 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.Alterando o tamanho de uma coluna

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

IF OBJECT_ID ( 'dbo.doc_exy', 'U' ) IS NOT NULL 
    DROP TABLE dbo.doc_exy;
GO
-- 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.Alterando o agrupamento de colunas

Os exemplos a seguir mostram 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 da coluna C2 é alterado para Latin1_General_BIN. Observe que o tipo de dados é obrigatório, mesmo que não tenha sido alterado.

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

[Início]

Alterando uma definição de tabela

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

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

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

ALTER TABLE T1 
REBUILD WITH (DATA_COMPRESSION = PAGE);

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

Aplica-se a: do SQL Server 2008 ao SQL Server 2014.

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

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

Aplica-se a: do SQL Server 2008 ao SQL Server 2014.

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

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

B.Modificando 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. Isso reduz a tabela para um tamanho menor, mas também aumenta o tempo necessário para armazenamento e recuperação. Isso pode ser útil para fins de arquivamento, ou em outras situações que exijam menos espaço e possam dispensar mais tempo para armazenamento e recuperação.

Aplica-se a: do SQL Server 2014 ao SQL Server 2014.

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

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

Aplica-se a: do SQL Server 2014 ao SQL Server 2014.

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

C.Alternando partições entre tabelas

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

CREATE TABLE PartitionTable (col1 int, col2 char(10))
ON myRangePS1 (col1) ;
GO
CREATE TABLE NonPartitionTable (col1 int, col2 char(10))
ON test2fg ;
GO
ALTER TABLE PartitionTable SWITCH PARTITION 2 TO NonPartitionTable ;
GO

D.Permitindo escalonamento de bloqueios em tabelas particionadas

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

Aplica-se a: do SQL Server 2008 ao SQL Server 2014.

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

E.Configurando 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: do SQL Server 2008 ao SQL Server 2014.

USE AdventureWorks2012;
ALTER TABLE Person.Person
ENABLE CHANGE_TRACKING;

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

Aplica-se a: do SQL Server 2008 ao SQL Server 2014.

USE AdventureWorks2012;
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: do SQL Server 2008 ao SQL Server 2014.

USE AdventureWorks2012;
Go
ALTER TABLE Person.Person
DISABLE CHANGE_TRACKING;

[Início]

Desabilitando e habilitando restrições e gatilhos

A.Desabilitando e reabilitando uma restrição

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

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

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

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

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

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

B.Desabilitando e reabilitando um gatilho

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

CREATE TABLE dbo.trig_example 
(id INT, 
name VARCHAR(12),
salary MONEY) ;
GO
-- Create the trigger.
CREATE TRIGGER dbo.trig1 ON dbo.trig_example FOR INSERT
AS
IF (SELECT COUNT(*) FROM INSERTED
WHERE salary > 100000) > 0
BEGIN
    print 'TRIG1 Error: you attempted to insert a salary > $100,000'
    ROLLBACK 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

[Início]

Operações online

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

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

Aplica-se a: do SQL Server 2014 ao SQL Server 2014.

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

Consulte também

Referência

sys.tables (Transact-SQL)

sp_rename (Transact-SQL)

CREATE TABLE (SQL Server)

DROP TABLE (Transact-SQL)

sp_help (Transact-SQL)

ALTER PARTITION SCHEME (Transact-SQL)

ALTER PARTITION FUNCTION (Transact-SQL)

EVENTDATA (Transact-SQL)