ALTER TABLE (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 ] 
        [ SPARSE | NULL | NOT NULL ] 
    | {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 ] ) ]
        | COLUMN column_name 
    } [ ,...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 ]

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

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

<single_partition_rebuild__option> ::=
{
      SORT_IN_TEMPDB = { ON | OFF }
    | MAXDOP =max_degree_of_parallelism
    | DATA_COMPRESSION = { NONE | ROW | PAGE} }
}

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. Para obter mais informações, consulte sp_dbcmptlevel (Transact-SQL).

  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 um índice, a menos que a coluna seja um tipo de dados varchar, nvarchar ou varbinary, o tipo de dados não seja modificado, o novo tamanho seja igual ou maior que o tamanho anterior e o índice não seja resultado de uma restrição PRIMARY KEY.

  • 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.

• column_name
  É o nome da coluna a ser alterada, adicionada ou descartada. column_name pode ter, no 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 existente em tabelas particionadas. type_name pode ser 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 .NET Framework definido pelo usuário e o esquema ao qual ele pertence. Tipos .NET Framework definidos pelo usuário são criados com a instrução CREATE TYPE antes que possam ser usados em uma definição de tabela.

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

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

  • type_name não pode ser timestamp.

  • 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.

  Observação
  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 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).

• SPARSE | NULL | NOT NULL
  Especifica se a coluna é uma coluna esparsa ou se pode aceitar valores nulos. Se a coluna a ser alterada for uma coluna esparsa, você deverá especificar essa propriedade explicitamente ou a coluna será revertida em uma coluna não esparsa. 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.

  Para conhecer outras restrições e obter mais informações sobre as colunas esparsas e a nulidade, consulte Usando colunas esparsas.

  As colunas que não permitem valores nulos podem ser adicionadas com ALTER TABLE apenas se você especificar um valor padrão para a coluna 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 como a propriedade null do tipo de dados definido pelo usuário e especificar um valor padrão para a coluna. Para obter mais informações, consulte CREATE TABLE (Transact-SQL).

  Observação
  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 ]
  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 Criando í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
  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. Para obter mais informações, consulte Controlando restrições, identidades e gatilhos com NOT FOR REPLICATION.

• SPARSE
  Especifica que a coluna a ser adicionada ou descartada é uma coluna esparsa. O armazenamento de colunas esparsas é otimizado para obter 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.

  Observação
  Você deve especificar a propriedade SPARSE toda vez que alterar a coluna ou a coluna será revertida em uma coluna não esparsa.

  Para conhecer outras restrições e obter mais informações sobre as colunas esparsas, consulte Usando 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 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.

  Observação
  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 estive próximo do, ou exceder, o limite. Recupere o espaço, criando um índice clusterizado da tabela ou recriando um índice clusterizado existente usando ALTER INDEX.

• 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
  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 Opção 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 Configurando operações de índice paralelo.

  Observação
  As operações de índice paralelas estão disponíveis somente nas edições Enterprise, Developer e Evaluation do SQL Server.

• ONLINE = { ON | OFF }
  Especifica se as tabelas subjacentes e í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 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 será possível definir ONLINE como ON quando um índice estiver sendo criado em uma tabela temporária local. Apenas a operação de reconstrução de HEAP de thread único é permitida.

  • 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. Ele impede a realização de 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. Para obter mais informações sobre bloqueios, consulte Modos de bloqueio.

  Observação
  As operações de índice online estão disponíveis somente nas edições Enterprise, Developer e Evaluation do SQL Server.

• MOVE TO { partition_scheme_name**(** column_name [ 1**,** ... n] ) | filegroup | "default" }
  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.

  Observação
  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 está 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
  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 } )
  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 ]
  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 índice 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. Para obter mais informações, consulte Transferindo dados de forma eficaz usando a alternância de partição. table e target_table não podem ser as mesmas. 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.  

  Observação
  Você não pode usar a instrução SWITCH em tabelas replicadas.

• SET ( FILESTREAM_ON = { partition_scheme_name | filestream_filegroup_name | "default" | "NULL" })
  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 } )
  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. Esse comportamento é igual ao do SQL Server 2005. 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
  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.

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

• DATA_COMPRESSION
  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.

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

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

  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 Criando tabelas e índices compactados.

• column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
  É 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 Usando conjuntos de colunas.

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

Bloqueios e ALTER TABLE

Execução de plano paralelo

Tabelas particionadas

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

Í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.

É 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.

Observação
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

Compactação de dados

Permissões

Requer 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.

Exemplos

A. Adicionando uma nova coluna

CREATE TABLE dbo.doc_exa (column_a INT) ;
GO
ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL ;
GO
EXEC sp_help doc_exa ;
GO
DROP TABLE dbo.doc_exa ;
GO

B. Descartando uma coluna

CREATE TABLE dbo.doc_exb (column_a INT, column_b VARCHAR(20) NULL) ;
GO
ALTER TABLE dbo.doc_exb DROP COLUMN column_b ;
GO
EXEC sp_help doc_exb ;
GO
DROP TABLE dbo.doc_exb ;
GO

C. Alterando o tipo de dados de uma coluna

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

D. Adicionando uma coluna com uma restrição

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

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

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

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

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

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

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

H. Adicionando uma coluna que permite valor nulo com valores padrão

USE AdventureWorks ; 
GO
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

I. Desabilitando e reabilitando uma 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) ;

J. Descartando uma restrição

CREATE TABLE dbo.doc_exc ( column_a INT
CONSTRAINT my_constraint UNIQUE) ;
GO
ALTER TABLE dbo.doc_exc DROP CONSTRAINT my_constraint ;
GO
DROP TABLE dbo.doc_exc ;
GO

K. Alternando partições entre tabelas

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

L. Desabilitando e reabilitando um 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

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

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

N. Descartando uma restrição PRIMARY KEY no modo ONLINE

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

O. Adicionando e descartando uma restrição FOREIGN KEY

USE AdventureWorks ;
GO
CREATE TABLE Person.ContactBackup
(ContactID int) ;
GO
ALTER TABLE Person.ContactBackup
ADD CONSTRAINT FK_ContactBacup_Contact FOREIGN KEY (ContactID)
    REFERENCES Person.Contact (ContactID) ;
ALTER TABLE Person.ContactBackup
DROP CONSTRAINT FK_ContactBacup_Contact ;
GO
DROP TABLE Person.ContactBackup ;

P. Alterando o tamanho de uma coluna

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');

Q. Permitindo escalonamento de bloqueios em tabelas particionadas

ALTER TABLE T1 SET (LOCK_ESCALATION = AUTO)
GO

R. Configurando controle de alterações em uma tabela

USE AdventureWorks;
ALTER TABLE Person.Contact
ENABLE CHANGE_TRACKING;

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

USE AdventureWorks;
ALTER TABLE Person.Contact
DISABLE CHANGE_TRACKING;

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

ALTER TABLE T1 
REBUILD WITH (DATA_COMPRESSION = PAGE);

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

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

T. Adicionando uma coluna esparsa

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

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

ALTER TABLE T1
ALTER COLUMN C4 ADD SPARSE ;
GO

ALTER TABLE T1
ALTER COLUMN C4 DROP SPARSE;
GO

U. Adicionando um conjunto de colunas

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

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

V. Alterando o agrupamento de colunas

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

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

Histórico de alterações do documento

Consulte também

Referência

Conceitos

Outros recursos